Opening & Closing Recordsets!

Suppose an ASP application includes, say, 3-4 SQL queries. Is it better
to open & close a recordset & then re-use it again & again (by again
opening & closing it), something like this:

----------------------------------------
Dim strSQL1
strSQL1="SELECT.................."

Dim objRS
Set objRS=Server.CreateObject("ADODB.RECORDSET")
objRS.Open strSQL1,objConn 'open objRS

'do something with strSQL1 & objRS
objRS.Close 'close objRS

Dim strSQL2
strSQL2="SELECT................"
objRS.Open strSQL2,objConn 'again open objRS

'do something with strSQL2 & objRS
objRS.Close 'close objRS again

Dim strSQL3
strSQL3="SELECT................"
objRS.Open strSQL3,objConn 'again open objRS

'do something with strSQL3 & objRS
objRS.Close 'close objRS again
----------------------------------------

or create different Recordset object variables, say 1 for 1 SQL query,
something like this:

----------------------------------------
Dim strSQL1
strSQL1="SELECT.................."

Dim objRS1 'create the first Recordset object variable
Set objRS1=Server.CreateObject("ADODB.RECORDSET")
objRS1.Open strSQL1,objConn

'do something with strSQL1 & objRS1
objRS1.Close

Dim strSQL2
strSQL2="SELECT................"

Dim objRS2 'create the second Recordset object variable
Set objRS2=Server.CreateObject("ADODB.RECORDSET")
objRS2.Open strSQL2,objConn

'do something with strSQL2 & objRS2
objRS2.Close

Dim strSQL3
strSQL3="SELECT................"

Dim objRS3 'create the third Recordset object variable
Set objRS3=Server.CreateObject("ADODB.RECORDSET")
objRS3.Open strSQL3,objConn

'do something with strSQL3 & objRS3
objRS3.Close
----------------------------------------

Thanks,

Arpan

Re: Opening & Closing Recordsets!

I personally don't believe that any difference in performance that may
exist, if any, is less important than the readability of your code. If it
makes your code easier to read by having four distinctly name variables
representing your recordsets, I'd do four separate ones. If it doesn't,
like you're using "rsTemp" or something, then I'd probably just reuse the
same variable. But, I'd code it like so:

Dim objRS
Set objRS = objConn.Execute(strSQL1)
''stuff
objRS.Close

Set objRS = objConn.Execute(strSQL2)
''stuff
objRS.Close

''etc.

Ray at work



"Arpan" wrote in message
news:1126123910.591183.100800@z14g2000cwz.googlegroups.com.. .
> Suppose an ASP application includes, say, 3-4 SQL queries. Is it better
> to open & close a recordset & then re-use it again & again (by again
> opening & closing it), something like this:
>
> ----------------------------------------
> Dim strSQL1
> strSQL1="SELECT.................."
>
> Dim objRS
> Set objRS=Server.CreateObject("ADODB.RECORDSET")
> objRS.Open strSQL1,objConn 'open objRS
>
> 'do something with strSQL1 & objRS
> objRS.Close 'close objRS
>
> Dim strSQL2
> strSQL2="SELECT................"
> objRS.Open strSQL2,objConn 'again open objRS
>
> 'do something with strSQL2 & objRS
> objRS.Close 'close objRS again
>
> Dim strSQL3
> strSQL3="SELECT................"
> objRS.Open strSQL3,objConn 'again open objRS
>
> 'do something with strSQL3 & objRS
> objRS.Close 'close objRS again
> ----------------------------------------
>
> or create different Recordset object variables, say 1 for 1 SQL query,
> something like this:
>
> ----------------------------------------
> Dim strSQL1
> strSQL1="SELECT.................."
>
> Dim objRS1 'create the first Recordset object variable
> Set objRS1=Server.CreateObject("ADODB.RECORDSET")
> objRS1.Open strSQL1,objConn
>
> 'do something with strSQL1 & objRS1
> objRS1.Close
>
> Dim strSQL2
> strSQL2="SELECT................"
>
> Dim objRS2 'create the second Recordset object variable
> Set objRS2=Server.CreateObject("ADODB.RECORDSET")
> objRS2.Open strSQL2,objConn
>
> 'do something with strSQL2 & objRS2
> objRS2.Close
>
> Dim strSQL3
> strSQL3="SELECT................"
>
> Dim objRS3 'create the third Recordset object variable
> Set objRS3=Server.CreateObject("ADODB.RECORDSET")
> objRS3.Open strSQL3,objConn
>
> 'do something with strSQL3 & objRS3
> objRS3.Close
> ----------------------------------------
>
> Thanks,
>
> Arpan
>

Re: Opening & Closing Recordsets!

I agree and usually just keep reusing a recordset called something like
objRS.

And one line of code that I usually add after objRS.Close is this:

Set objRS = Nothing

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

<<
I personally don't believe that any difference in performance that may
exist, if any, is less important than the readability of your code. If
it
makes your code easier to read by having four distinctly name variables
representing your recordsets, I'd do four separate ones. If it doesn't,
like you're using "rsTemp" or something, then I'd probably just reuse
the
same variable. But, I'd code it like so:

Dim objRS
Set objRS = objConn.Execute(strSQL1)
''stuff
objRS.Close

Set objRS = objConn.Execute(strSQL2)
''stuff
objRS.Close

''etc.

Ray at work
>>


*** Sent via Developersdex http://www.developersdex.com ***