2

I am at the moment trying to improve the performance in my ASP Classic application, and I am at the point of improving SQL transactions.

I have been reading a little from http://www.somacon.com/p236.php

So, my question is: How does the line set rs = conn.execute(sql) actually work?

I believe that the line defines a variable named rs and binding ALL the data collected from the database through the SQL sentence (fx select * from users). Then afterwards I can actually throw the database-connection to hell and redefine my sql variable if I please, is this true?

If that is true, will I then get the best performance by executing my code like this:

set conn = server.createobject("adodb.connection")
dsn = "Provider = sqloledb; Data Source = XXX; Initial Catalog = XXX; User Id = XXX; Password = XXX"

conn.open dsn
sql = "select id, name from users"
set rs = conn.execute(sql)
conn.close

-- Do whatever i want with the variable rs

conn.open dsn
sql = "select id from logins"
set rs = conn.execute(sql)
conn.close

-- Do whatever i want with the variable rs

conn.open dsn
sql = "select id, headline from articles"
set rs = conn.execute(sql)
conn.close

-- Do whatever i want with the variable rs

set conn = nothing

In this example i open and close the connection each time i do a sql transaction.

Is this a good idea?

MicBehrens
  • 1,780
  • 8
  • 34
  • 57

2 Answers2

3

Is this a good idea?

No but not for the reasons indicated by Luke. The reality is that ADODB will cache connections anyway so opening and closing connections isn't all that expensive after all. However the question proceeds from the mis-information you appear to have over the behaviour of a recordset...

From you comment on Lukes answer:-

But it is correct, that it stores all the data in the variable when executed?

Not unless you have carefully configured the recordset return to be a static client-side cursor and even then you would have to ensure that the cursor is completely filled. Only then could you disconnect the recordset from the connection and yet continue to use the data in the recordset.

By default a SQL server connection will deliver a simple "fire-hose" rowset (this isn't even really a cursor) the data is delivered raw from the query, only a small amount of buffering occurs of incoming records and you can't navigate backwards.

The most efficient way to minimise the amount of time you need the connection is to use the ADODB Recordset's GetRows method. This will suck all the rows into a 2-dimensional array of variants. Having got this array you can dispense with the recordset and connection.

Much is still made of minimising the number of connections maintained on a server but in reality on modern hardware that is not a real issue of the majority of apps. The real problem is the amount of time an on going query is maintaining locks in the DB. By consuming and closing a recordset quickly you minimise the time locks are held.

A word of caution though. The tradeoff is an increased demand for memory on the web server. You need to be careful you aren't just shifting one bottleneck to another. That said there are plenty of things you can do about that. Use a 64Bit O/S and stuff plenty of memory in it or scale out the web servers into a farm.

AnthonyWJones
  • 187,081
  • 35
  • 232
  • 306
  • "The most efficient way to minimise the amount of time you need the connection is to use the ADODB Recordset's GetRows" -- can you please explain why this is 'faster' than simply disconnecting the recordset. – onedaywhen Nov 28 '11 at 09:16
  • @onedaywhen: Actually I used the word "efficient" not "faster". You can't just disconnect a recordset, you first need to make sure it has been fully populated. In addition you need ensure the cursor being used is on the client side. Accessing individual rows still requires the "movenext" operation and accessing field values requires that a Field object is retrieved from a property getter and then the field objects value property is accesses (VB syntax makes that less obvious). Still the margins are quite small. – AnthonyWJones Nov 28 '11 at 21:49
  • You actually said, "The most efficient way to minimise the amount of time" -- i.e. efficiency with a temporal element must mean faster, no? ;) But I think I now have a better understanding of what you mean, thanks. Obviously, accessing elements in an array will have overhead too and for some operations a Recordset may be more efficient e.g. by using its properties/methods such as Sort, FIlter, GetString, etc. – onedaywhen Nov 29 '11 at 08:31
0

Nope, opening and closing connections is costly. Open it, reuse the recordset like you are, then close it.

Luke D
  • 138
  • 10