March 8, 2010 at 7:20 am
Am not sure how to approach this so thought would post my thoughts. I have to retrieve this huge piece of information from the database. Basically i need to retrieve time series data for upto 126 ID numbers which will be passed to the database.
Initially i thought of passing the whole 126 numbers in an XML format to the stored proc and then retrieve the data for each of the ID. But each ID can have upto 100's of values. So now i am thinking of passing the IDs individually within the stored Proc and then format the results in my .Net code.
Which would be a neater approach or if anyone has any other ideas, welcome.
Database used is SQL 2005
March 8, 2010 at 1:19 pm
You could send the ID values as a comma separated list in a varchar variable to your sproc.
Run a high performing split string function to separate the values (an example can be found by reading the Tally table article I referenced in my signature) and use the results either as a joined table or in a CROSS APPLY query.
If you'd give us something to play with (table def, sample data, expected results etc.) I'm sure there will be more than just me willing to show how that would look like. If you're not sure how to post sample data, please see the first link in my signature. 😉
March 8, 2010 at 7:32 pm
Since you're on SQL 2008, pass the IDs in as a TVP (table-valued parameter) and receive the results back as a datatable. SQL CAT's test have generally shown this to be the fastest way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2010 at 10:07 am
RBarryYoung (3/8/2010)
Since you're on SQL 2008, pass the IDs in as a TVP (table-valued parameter) and receive the results back as a datatable. SQL CAT's test have generally shown this to be the fastest way.
Would have been my thought, too.
But then I realized, the OP is using SS2K5...
I'm sorry I didn't mention that on my reply.
Database used is SQL 2005
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply