CODEWITHSUNDEEP
c#.netsqlsql-server
tgandrews
tgandrews

Reputation: 12660

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

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

Upvotes: 0

Views: 415

Answers (7)

Anthony Faull
Anthony Faull

Reputation: 17957

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 += ")";

Upvotes: 1

Lee Conroy
Lee Conroy

Reputation: 31

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

Upvotes: 0

davek
davek

Reputation: 22905

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.

Upvotes: 0

Konamiman
Konamiman

Reputation: 50273

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.

Upvotes: 2

teabot
teabot

Reputation: 15444

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.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166376

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

Upvotes: 2

KB22
KB22

Reputation: 6969

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

Upvotes: 2

Related Questions