Optimal code - One large query but more complicated data retrieval or lots of small queries

Question

Hi,

I have a C# program that retrieves multiple rows from a DB but currently does them one at a time on the same connection.

Would it be better to write it so that instead of repeatedly running (where blah changes each time):

select data from table where name = 'blah'

To something like:

select name, data from table where name in ('blah','blah2','blah3')

I'm nervous of making this change as I am unsure of the additional overhead of retrieving this data from the resulting table.

Please note the example data is not representative of the actual data so I can't do anything like:

select name, data from table where name like 'blah%'

Additionally this is in a highly stressed environment so even the slightest improvement could be significantly beneficial.

Thanks in advance, Tom

Answer 1

I'd go for one chunky request. At least from a performance point of view this is the better approach.

Answer 2

One trip to the DB is mutch more efficient than multiple small hits.

Answer 3

I would expect the use of single statement to yield better performance and you will decrease the overhead associated with statement preparation, submission, and execution. However, as with any optimisation - you should try it and measure it before committing to it.

You might also want to look at preparing and submitting a batch of statements instead of making individual requests. Also, for the first case, check that you are preparing the statement once and caching the prepared statement - saving you overhead on the repeated compilation of the statement.

Answer 4

Theorically, one single query is better. Even if the processing time at the database is the same, you save resources and time by avoiding the multiple roundtrip communications between the database server and the client.

That said, this is one type of problem that calls for profiling to actually measure the improvements of any changes made.

Answer 5

I'd go for one operation calling

select data from table... etc.etc.

via a stored procedure: that will then let you tinker with the SQL logic (possible optimizations) without having to alter anything in your frontend code.

Answer 6

One statement is best, and optimise through the database..

Answer 7

A parameterized query is both fast and secure. It caches query plans and protects against SQL injection.

SELECT name, data
FROM table
WHERE name in (@name1, @name2, @name3)

In the calling code you can add parameters as needed. For example (C#):

var command = connection.CreateCommand();
command.CommandText = "SELECT name, data FROM table WHERE name in (";
int index = 0;
string separator = "";
foreach (string value in valueList)
{
   string paramName = "@p" + Convert.ToString(index);
   command.CommandText += separator + paramName;
   command.Parameters.Add(paramName, value);
   separator = ", ";
   index++;
}
command.CommandText += ")";