Retrieving huge amount of data : XML or anything else?

[kav123]

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.

The database used is SQL 2005

 

[Crowley16]

use xml, sql server is actually faster at processing xml than csv, as long as you use the xml querying functionality correctly.

--------------------
Procrastinate Now!

 

[kav123]

Hmm, thanks for that. Any examples or urls, have used XML to insert data into the table how, to loop through the XML and select the records.

Also every id in XML will return rows of data, not sure how to return the data.

Sorry, but not a SQL2005 expert!! ANy help great

 

[Crowley16]

once you've got the xml, you can buffer the records you want into a table and use set based approach to process the data, will be a LOT faster than loops...

for examples, check out OpenXML in books online or google, it's really quite straight forwards...

--------------------
Procrastinate Now!

 

[gmmastros]

So.... Crowley, your suggestion is to use XML to pass the list of 126 Id's to the database, but return a regular ole result set. Right? This is probably what I would suggest also.

You can create XML in the database and return that, but I generally discourage it for performance reasons.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

as long as you return the data as xml using the native xml functions e.g. select... from ... for xml... or something along those lines, then the performance is fine.

If you're trying to build your own xml with a mess of concatenation of varchars, casts and so on then yeah, that's definitely something you want to avoid...

--------------------
Procrastinate Now!

 

[kav123]

I was thinking of populating the data retrieved from each of the ids into a table and then pass the table. But the amount of data looks huge.

Any examples of working with xml would be great!! Searched the web sites, did not bump into any good ones so far.

 

[Crowley16]

126 ids, up to 100 records per id, that's a max of 12,600 records. Absolutely tiny in terms of sql server.

I wouldn't bat an eye for anything under 1 million...

--------------------
Procrastinate Now!

 

[kav123]

Ok,worked it out, used OpenXML for it. Basically i am populating the XML into a temp table created and then created an inner join from there. However, for some reason the first two rows are null values, even though there are values in there. any ideas. I am using the following

INSERT @xtableselect Number, ID from OpenXml(@hDoc, '//ROOT/xDetails/x/',2) WITH(Number varchar(50), ID int)

The XML is

<xDetails>
<x Number="1" ID="">
<x Number="2" ID="">
<x Number="3" ID="">
<x Number="4" ID="">
<x Number="5" ID="">
</xDetails

 

[gmmastros]

Remove the last slash.

[tt][red]'//ROOT/xDetails/x'[/red][/tt]

Full example:

Declare @XML VarChar(8000)
Set @xml = '<ROOT><xDetails>
<x Number="1" ID="" />
<x Number="2" ID="" />
<x Number="3" ID="" />
<x Number="4" ID="" />
<x Number="5" ID="" />
</xDetails></ROOT>'

Declare @xtable Table(Number VarChar(50), ID Int)

Declare @hDoc Int

exec sp_xml_preparedocument @hDoc OUTPUT, @XML

INSERT @xtable
select Number, ID 
from   OpenXml(@hDoc, '//ROOT/xDetails/x',3) 
WITH   (Number varchar(50), ID int)

Select * From @xtable

Note that I also changed the 2 to a 3 in your OpenXML function. I also fixed the XML so it would validate by adding / to the closing tags of each x element.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom