Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Temp tables vs "permanent" temp table vs User Table Type Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 4:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:25 PM
Points: 10, Visits: 46
Why don't you use XML Indexes?

References:
http://technet.microsoft.com/en-us/library/ms191497.aspx
http://technet.microsoft.com/en-us/library/bb934097.aspx
Post #1496644
Posted Thursday, September 19, 2013 8:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
PiMané (9/4/2013)
What's the best way to store the XML?


The best way, IMHO, is to shred it on receipt and store it as properly normalized data. XML is formatted data and one of the worst data sins there is is to store formatted data.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1496682
Posted Thursday, September 19, 2013 10:43 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129


XML with indexes is very slow and takes lots of memory.
I've made some tests with XML without index, with indexes and data stored "vertically" (to store data in a database where the user can define whatever they want) and XML was always very very slow no matter what.
The best way to use XML, in this case, a very large XML, is to store it in temp table.




If you need to work better, try working less...
Post #1496700
Posted Thursday, September 19, 2013 10:47 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Jeff Moden (9/19/2013)
PiMané (9/4/2013)
What's the best way to store the XML?


The best way, IMHO, is to shred it on receipt and store it as properly normalized data. XML is formatted data and one of the worst data sins there is is to store formatted data.


The problem is that we have clients that have receipts with over 1000 lines, so to send the XML has proper data to the SP would mean to call and SP 1000 times or to have user defined data table (only exists in SQL 2008 and VB6 doesn't support it... this is our big problem).
That why we decided to use the XML to send the data to the SP and then create the temp table with its data to be used in all the process (we are code reviewing the process and so far found over 50 SP, FN and TRG that are called "along" the way...).

Pedro




If you need to work better, try working less...
Post #1496701
Posted Friday, September 20, 2013 7:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
PiMané (9/19/2013)
Jeff Moden (9/19/2013)
PiMané (9/4/2013)
What's the best way to store the XML?


The best way, IMHO, is to shred it on receipt and store it as properly normalized data. XML is formatted data and one of the worst data sins there is is to store formatted data.


The problem is that we have clients that have receipts with over 1000 lines, so to send the XML has proper data to the SP would mean to call and SP 1000 times or to have user defined data table (only exists in SQL 2008 and VB6 doesn't support it... this is our big problem).
That why we decided to use the XML to send the data to the SP and then create the temp table with its data to be used in all the process (we are code reviewing the process and so far found over 50 SP, FN and TRG that are called "along" the way...).

Pedro


Heh... you asked what the best way to store XML is. I suggested shredding it and storing it in normalized tables. Are you storing the XML in a table or not?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1496871
Posted Friday, September 20, 2013 8:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Jeff Moden (9/20/2013)
Heh... you asked what the best way to store XML is. I suggested shredding it and storing it in normalized tables. Are you storing the XML in a table or not?


Right
I don't store the XML in the database... I send the XML to SQL to process it. My question in how to store XML is cause it's used by several SPs and have to "pass" it between them...

Pedro




If you need to work better, try working less...
Post #1496920
Posted Friday, September 20, 2013 12:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
PiMané (9/20/2013)
Jeff Moden (9/20/2013)
Heh... you asked what the best way to store XML is. I suggested shredding it and storing it in normalized tables. Are you storing the XML in a table or not?


Right
I don't store the XML in the database... I send the XML to SQL to process it. My question in how to store XML is cause it's used by several SPs and have to "pass" it between them...

Pedro


I wouldn't store it. Build a main "control" proc that parses it once and stored it in a Temp Table and have the other stored procedures use the data from the Temp Table.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1496987
Posted Saturday, September 21, 2013 1:09 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
When I say process I mean use nodes() or sp_xmlpreparedocument to parse the XML an use it if only once or store it in a temp table if use more than once...

Pedro




If you need to work better, try working less...
Post #1497117
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse