Speed Improvement Needed for Returning Multiple Records

Question

Speed Improvement Needed for Returning Multiple Records

gvdamn

SSC-Addicted

Points: 460
More actions
November 1, 2010 at 10:21 am

#231068

I've got a (SQL Server 2008) database with (to keep it simple) daily stock prices of 7500 stocks. I need the stock prices (for all stocks) for 100 specific (non adjacent) dates. I'm trying to retrieve those with ADO in Visual Studio 2008. Doing this one date, one stock at a time is much too slow. How can I dramatically improve the speed? Any suggestions?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Maxim Picard SSCrazy Points: 2052 More actions · Answer 1

Hi gvdam

You could pass all 100 dates to your proc as a multi value parameters, join with your stock price table and return all the rows in one call.

There are many ways to use multi value parameters, are you familiar with them?

Maxim

gvdamn SSC-Addicted Points: 460 More actions · Answer 2

Hi Maxim,

Thanks for your reply.

To be honest: I'm not familiar with multi value parameters. Do you have any good (web) references?

How is it going to work out with ADO? (I'm currently using a SQL command string, SqlDataAdapter, DataSet, and DataTable. I hope this is clear enough.)

Could "Table-Valued Parameters" be an option?

Maxim Picard SSCrazy Points: 2052 More actions · Answer 3

gvdamn (11/2/2010)
Hi Maxim,
Thanks for your reply.
To be honest: I'm not familiar with multi value parameters. Do you have any good (web) references?
How is it going to work out with ADO? (I'm currently using a SQL command string, SqlDataAdapter, DataSet, and DataTable. I hope this is clear enough.)
Could "Table-Valued Parameters" be an option?

Hi gvdamn

Table-valued parameter is a very good "multi value parameter" option for SQL Server 2008. Keep in mind its a SQL 2008 "only" option so you have to consider a different solution if you need to deploy your software on other versions.

For 2008

http://www.sommarskog.se/arrays-in-sql-2008.html

http://msdn.microsoft.com/en-us/library/bb675163.aspx

Worth the read even if you are SQL Server 2008 only

http://www.sommarskog.se/arrays-in-sql-2005.html

http://www.sommarskog.se/arrays-in-sql-2000.html

Erland Sommarskog's articles are excellent so you should be able to easily integrate the solution you choose but don't hesitate to ask if you have some problem i am comfortable with .NET too.

Maxim

gvdamn SSC-Addicted Points: 460 More actions · Answer 4

Hi Maxim,

Thanks again. I've implemented it and it takes about the same amount of time to get one (stock price) series as it took to get one individual stock price (for one date) out of my SQL Server 2008 database. (This is a very rough estimate.) Quite a significant speed improvement!

gvdamn