colindow
asked on
Basic ADO performance/methods
There seems to be a plethora of ways to create ADo recordsets. I'm trying to find out the most efficient method for a basic read to load info into a combo box etc.
My understanding is that this is to create a firehose cursor forwardonly,serverside and cachesize=1.
Is this correct?. Also I open one connection gConn at the beginning of my project in which the cursor is set client side.
Can I use this same connection to create a firehose cursor by setting the recordset cursor to serverside?
e.g With ss
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CacheSize = 1
.Open strSQL, gConn
End With
Get the impression I'm not sure what I'm doing (no points for this part)?
Any additional info on this issue welcome!
My understanding is that this is to create a firehose cursor forwardonly,serverside and cachesize=1.
Is this correct?. Also I open one connection gConn at the beginning of my project in which the cursor is set client side.
Can I use this same connection to create a firehose cursor by setting the recordset cursor to serverside?
e.g With ss
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CacheSize = 1
.Open strSQL, gConn
End With
Get the impression I'm not sure what I'm doing (no points for this part)?
Any additional info on this issue welcome!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
colindow,
I would recommend the book "Programming ADO" by David Sceppa, Published by MS Press.
Here's a link to more info:
http://www.microsoft.com/mspress/books/3445.asp
Ture Magnusson
Karlstad, Sweden
I would recommend the book "Programming ADO" by David Sceppa, Published by MS Press.
Here's a link to more info:
http://www.microsoft.com/mspress/books/3445.asp
Ture Magnusson
Karlstad, Sweden
Hi VK
>>2. DAO is much faster as ADO (My experience: 10x. But if many users (>10) are accessing the database
Access) DAO crashes or gets very slow !<<
I suppose you are talking about DAO against an access db
well in my experience (and i have a lot) if you use DAO against an access database with the ISAM method then it is VERY fast up to 100x times faster then ADO and it is EXTREMELY stable even with dozens of simultanious users hundreds of thousands of records and slow networks (10 Base 2)
just use table-type recordsets,set indexes, use seek and refrain the use of query's
but to be complete ADO can be fast to if you carefully design the database and the program
much of the slowness of ADO comes from people not knowing what access method to choice amongst the bewildering array of ADO's possibility's,and you can blame MS for not clearly explaining what methods are best against what DB
and the best methods are VERY much DB dependent
well dont only blame MS for it,for i certenly am under the impression that most authors that write books about ADO programming do not have the slightest idea what they are talking about (well neither do I when it comes to ADO i suppose)
>>2. DAO is much faster as ADO (My experience: 10x. But if many users (>10) are accessing the database
Access) DAO crashes or gets very slow !<<
I suppose you are talking about DAO against an access db
well in my experience (and i have a lot) if you use DAO against an access database with the ISAM method then it is VERY fast up to 100x times faster then ADO and it is EXTREMELY stable even with dozens of simultanious users hundreds of thousands of records and slow networks (10 Base 2)
just use table-type recordsets,set indexes, use seek and refrain the use of query's
but to be complete ADO can be fast to if you carefully design the database and the program
much of the slowness of ADO comes from people not knowing what access method to choice amongst the bewildering array of ADO's possibility's,and you can blame MS for not clearly explaining what methods are best against what DB
and the best methods are VERY much DB dependent
well dont only blame MS for it,for i certenly am under the impression that most authors that write books about ADO programming do not have the slightest idea what they are talking about (well neither do I when it comes to ADO i suppose)
ASKER
I guess I should have clarified. I'm hitting a SQL Server 6.5 database from VB6 using ADO and an ODBC connection.
Thanks for the site ryancys this did solve one of the things puzzling me which was if I had created a firehose cursor then I didnt think i should be able to use movefirst.
Aparently in this case movefirst actually calls a complete refresh of the recordset to get back to the first record. You live and learn. It also answered an extra point which was whether to use DIM as New or to Dim then Set new separatly (the latter is best) so a bonus there.
The only remianing question is whether the firehose technique is the best (presumably because it actually creates a cursorless recordset) ?
Thanks for the site ryancys this did solve one of the things puzzling me which was if I had created a firehose cursor then I didnt think i should be able to use movefirst.
Aparently in this case movefirst actually calls a complete refresh of the recordset to get back to the first record. You live and learn. It also answered an extra point which was whether to use DIM as New or to Dim then Set new separatly (the latter is best) so a bonus there.
The only remianing question is whether the firehose technique is the best (presumably because it actually creates a cursorless recordset) ?
Can you also show your strSQL?
oh, sorry, u had mentioned that u use ODBC. Why not you use OLEDB, which should be faster than ODBC, if I am right.
Try this connection string and connection (similar to which we are using):
gConnStr = "Provider=SQLOLEDB.1;Passw
Set gConn = New ADODB.Connection
gCurrConn.CursorLocation = adUseClient
gCurrConn.Open gConnStr
'And regarding your cursor, I hope it is ok. You can also open a fwd only read only recordset like this:
Set rs = New Recordset
Set rs = gConn.Execute "SELECT ..."
HTH.
Try this connection string and connection (similar to which we are using):
gConnStr = "Provider=SQLOLEDB.1;Passw
Set gConn = New ADODB.Connection
gCurrConn.CursorLocation = adUseClient
gCurrConn.Open gConnStr
'And regarding your cursor, I hope it is ok. You can also open a fwd only read only recordset like this:
Set rs = New Recordset
Set rs = gConn.Execute "SELECT ..."
HTH.
ASKER
Thanks Valli_an. I'm using ODBC as I can connect to SQL server and set up my connection (gConn) without but a lot of the legacy code relies on getting the recordcount back
e.g
gConn.execute "SP_ResetInUse 'Colin',1" ,lRtn, adCmdText
if I "set nocount on" then lrtn seems to come back as -1 but the sp executes okay. If I don't the code fails which I believe is because of the way the OLEDB library works with its new extra info features(its a feature not a bug and all that).
One day I'll figure out how to write this to get a recordcount but at the mo there seems to be a lot to change to achieve this.
e.g
gConn.execute "SP_ResetInUse 'Colin',1" ,lRtn, adCmdText
if I "set nocount on" then lrtn seems to come back as -1 but the sp executes okay. If I don't the code fails which I believe is because of the way the OLEDB library works with its new extra info features(its a feature not a bug and all that).
One day I'll figure out how to write this to get a recordcount but at the mo there seems to be a lot to change to achieve this.
Hi, pierrecampe.
Ty for submitting your comment.
I'm working with ADO too (since it was available) and since 5 years with DAO.
You are right that you can't compare ADO with DAO.
If you have Accesss as database DAO is unbeatable in performance.
However we will migrate to ADO fully and the performance keeps acceptable.
Hope MS will make more efforts better in explaining performance issues about ADO.
V.K.
:-)
Ty for submitting your comment.
I'm working with ADO too (since it was available) and since 5 years with DAO.
You are right that you can't compare ADO with DAO.
If you have Accesss as database DAO is unbeatable in performance.
However we will migrate to ADO fully and the performance keeps acceptable.
Hope MS will make more efforts better in explaining performance issues about ADO.
V.K.
:-)
The recordcount does not always return the count, ya, I have come across this. But why not you assign to a recordset, like this:
gConn.execute "SP_ResetInUse 'Colin',1" ,lRtn, adCmdText
Set rs = New Recordst
rs.Open "SP_ResetInUse 'Colin'",gConn,adOpenKeySe
I suppose the recordcount is also dependent upon the locktype and cursortype, and also the type of backend you use.
The default one, for Execute statemnt is forward only and Read only.
Please correct me if I am wrong, since I dont have MSDN in this m/c, I could not check with what I am saying.
Also, FYI, I normally check recordcount like this:
If rs.RecordCount <> 0 Then
'found records
Else
'no records
End If
'HTH.
gConn.execute "SP_ResetInUse 'Colin',1" ,lRtn, adCmdText
Set rs = New Recordst
rs.Open "SP_ResetInUse 'Colin'",gConn,adOpenKeySe
I suppose the recordcount is also dependent upon the locktype and cursortype, and also the type of backend you use.
The default one, for Execute statemnt is forward only and Read only.
Please correct me if I am wrong, since I dont have MSDN in this m/c, I could not check with what I am saying.
Also, FYI, I normally check recordcount like this:
If rs.RecordCount <> 0 Then
'found records
Else
'no records
End If
'HTH.
ASKER
NOt a definitive answer but a useful resource. Which gave additional info over and above the specific question.
ASKER
NOt a definitive answer but a useful resource. Which gave additional info over and above the specific question.
What i knew is:
1. .CursorLocation = adUseServer doesn't work sometimes
I think that adUseClient is faster because the data is loaded in the clients cache an then is is faster accessible.
2. DAO is much faster as ADO (My experience: 10x. But if many users (>10) are accessing the database Access) DAO crashes or gets very slow !
V.K.