Optimize lots of queries

Question

I have a list of upwards 3,000 decimal values and for each one I need to lookup another decimal value from a SQL Server database. Currently I'm using the .Net SqlClient classes.
Making 3,000 queries seems inefficient, but I'm not sure if there's a neat efficient way of combining the queries into few calls.

The lookup table currently contains about 1,500,000 values and I don't want to retrieve any values from the database except those that actually needs to be looked up (so I can't cache the whole table in memory etc).

What's the best way of doing this?

Answer 1

SQL Server 2008 introduced table value parameters. You can define them as user type parameters and pass them through to stored procedures and UDFs (with UDFs they need to be read only).

You can write a stored procedure that takes a table of decimals, does the lookup and returns the data you need, all in one query.

Answer 2

If you're using SQL Server 2008, you could pass in a Table Valued Parameter to your query - pass in a table of values, to then join on to in your query. Example with performance comparisons against alternative approaches (CSV & XML): http://www.adathedev.co.uk/2010/02/sql-server-2008-table-valued-parameters.html

I'd see how that performs and go from there.

If you're not using SQL Server 2008, you could try the alternative approaches compared in that article (XML then CSV) - there's plenty of discussions too on "how to pass multiple values to a sproc" here on StackOverflow. Or, bulk load the decimal values to a temp table and then join on that.

Answer 3

LEARN SQL ;) Seriously.

IN clause. I rate that beginner knowledge.

SELECT otherfield, field FROM table WHERE otherfieled IN (value, value, value, value).

Put in 100-200 values each and you reduce the number of round tripy by a factor of 100 to 200. That cuts you down to 15-30 queries. No need to have any stored procedure for this.

You need to take both values out (as return) so you can correlate results and query parameter.

Answer 4

If the values do not change that often, make a AppFabric distributed cache bevore it. that would be the best way to speed it up. in my opinion, moving the logic to stored procedures is normally not the best way for scalability.