opening mysql recordsets

[thompom]

Hi,

I am looking for clarification on opening my recordsets
I used to use the following for rs which were read only

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
....
rs.close

now i am changing all these to

Set rs = conn.execute(sql)
...
rs.close

is it worth me doing this? - will i see any performance gain?

 

[thompom]

i notice i cant use

rs.RecordCount

...i get -1 as a result!

 

[thompom]

...the recordcount property would not work without

rs.CursorLocation = 3

and i cant put that in using the new method

 

[Tarwn]

If your looking for a performance gain and an easy way to still have a recordcount, you should look into using the .GetString or .GetRows methods of the recordset on top of using Connection.Execute. GetString allows you to specify delimiters and quickly dumps the data down to a series of string data. GetRows dumps the data down to a two-dimensional array (col index, then row index).
With GetRows you can get the number of rows by looking at the upper bound of the second array index (UBound(arrayname,2) + 1 is the count). GetRows is generally 7-11 times faster than just looping through the recordset yourself.

http://www.w3schools.com/ado/met_rs_getrows.asp

http://www.w3schools.com/ado/met_rs_getstring.asp

To return a recordset that has it's recordcount property set, try adding in a conn.CursorLocation = 3 before you execute your query. This will do the same thing as setting it for an individual recordset, but will set it on the connection for all recordsets it generates.

-T

 

[thompom]

hi tarwn - thanks for your reply

i understand getrows will improve performance [you have told me many times!] - but wanted to know if it was better to use the second method to open recordsets, rather than the first - not just for performance gain, but for general standards compliance/less work by server etc...

if it doesnt matter then i will leave my recordsets as they are [first example]

thanks again - will use getrows in future, but am lazy and afraid of change!