Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp tables vs "permanent" temp table vs User Table Type


Temp tables vs "permanent" temp table vs User Table Type

Author
Message
Srinivasan Raju
Srinivasan Raju
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 47
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45020 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334


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...
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45020 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45020 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search