Sql Connection open slow down or make application performance better

Question

I am querying to a table for specific records having 5,00,000 records in application. When ever i query

eg:

...........  Some Code

for i = 0 ;  i < 60,000

.............  Some code

connection.Open();

select * from ERet where [Key] = Variable+i ;

connection.Close();
--------------some calculation

.............some code

next

Should this connection.open/close will affect the application performance? Should i need connection to open before the loop and close after loop for the better application performance.

score 3 · Answer 1 · edited May 23 '17 at 12:19

In this particular case, because you have a tight loop (well, it might not be so tight, but you definitely are performing a large amount of operations in that scope), you should keep the connection open outside of the loop before you enter the loop, and then make sure to close it when the loop is done. For example:

using (var connection = new SqlConnection("connection string"))
foreach (...)
{
   // Do your work here.
}

While connections might or might not be recycled/pooled (depending on your settings), there is still some overhead in pooling connections (they need to be reset when you pull them from a pool), and doing anything 60,000 times is going to have some overhead; you might as well take it out where you can and where you know it won't impact you negatively.

Also, as Mitch Wheat points out in his answer, and important question to ask is whether or not you have to perform 60,000 queries; it would appear from your code you are performing the same exact query over and over, when just once might suffice, or you might be able to collect the conditions you need to query on into one query and then process your data.

This is the sample Query, I am not calling same query select * from ERet where [Key] = 'CASHRET1-2' "CASHRET1-2" is variable which will be changing for each iteration and based on result of this query i am doing calculation — SHAJI P.D., Mar 30 '11 at 10:24
In this query, select * from ERet where [Key] = 'CASHRET1-2' — SHAJI P.D., Mar 30 '11 at 10:25
@SHAJI P.D.: You might want to consider creating a *single* query where you do something like `select * from ERet where [Key] in (...)`, creating a parameterized query and then getting the results up front and then cycling through. If you are seeing a lot of overhead in the creation and tearing down of connections, or of just making 60K calls to the database (instead of one), then this should help mitigate that. — casperOne, Mar 30 '11 at 12:28

Mitch Wheat · Answer 2 · 2011-03-30T12:05:23.590

1

Yes, open the connection before the loop (using()) and close after the loop completes.

On the other hand, why are you doing 60,000 SELECT's that way?

edited Mar 30 '11 at 12:05

answered Mar 29 '11 at 12:32

Mitch Wheat

295,962
43
465
541

3

@SHAJI P.D. I'd say that Mitch brings up a very good question, depending on what is being done with the data, you might be able to make just *one* query and then go from there. – casperOne Mar 29 '11 at 12:35
select * from ERet where [Key] = VariableKey basically I am searching table for particular value to do some calculation – SHAJI P.D. Mar 30 '11 at 10:18
have you considered: select * from ERet where [Key] IN (list of VariableKey) ? – Mitch Wheat Mar 30 '11 at 12:04

score 1 · Answer 3 · answered Mar 29 '11 at 12:32

1

You're creating 60,000 connections, and it doesn't appear you need to.

Do a

Using(open connection)
{
   for(i = 1; i < 60,000; ++i)
   {
      query
   }
}

Now you've got one connection open for the life of your loop, and it will dispose nicely when it's done.

answered Mar 29 '11 at 12:32

taylonr

10,732
5
37
66

1

not creating 60,000 connections as the connection string is the same (and therefore should be reused), but opening and closing 60,000 – Mitch Wheat Mar 29 '11 at 12:35

score 0 · Answer 4 · answered Mar 29 '11 at 12:43

0

My recommendation is usually to measure what's going on, and see if you have a problem. It's entirely possible that the overhead of creating and closing the connection is trivial compared to the other things going on, and by refactoring your code to optimize for connections, you're missing (or even making worse) bigger issues.

For instance - if you can do your select in a single statement, and iterate over the resultset, your application should go a lot faster. If you're worried about a recordset with 500.000 records in memory - test it.

Optimizing without testing "before" and "after" is pointless, and often counterproductive.

answered Mar 29 '11 at 12:43

Neville Kuyt

29,247
1
37
52

This is the sample Query, I am not calling same query select * from ERet where [Key] = 'CASHRET1-2' for each iteration. "CASHRET1-2" is variable which will be changing for each iteration and based on result of this query i am doing calculation – SHAJI P.D. Mar 30 '11 at 10:28
Right - so based on your pseudo code, you're incrementing the value of 'CASHRET1-2' everytime you loop, retrieving that record, and doing some calculation.Instead, you could just do 'select * from ERET where key between 'CASHRET1-1' and 'CASHRET1-60000'. That way, you run a single query, create a resultset and iterate over the resultset. – Neville Kuyt Mar 30 '11 at 10:39

Sql Connection open slow down or make application performance better

4 Answers4

Linked