hi all
i have used workspace in msaccess 2000 project in order to get an atomic transaction
so it can rollback at any problem
the workspace allow me to open the record set for once but i need to open the record set with multiple conditions
so what i did i opened a generic record set
set rs = db.openRecordset("t1",2,dbseechanges)
then i used a filter to determine the conditions
dim x as recordset
rs.filter ='c = ' & c
set x = rs.openRecordset
but the problem is that it is toooooooooooooooo slow
and the applications has to be nearly on time and can not stand this delay
so what can i use instead of filter or other ideas
thnx in advance
bythe way the tables are SqlServer 2000
fast search
Jim Hart
Per our support engineer:
Base on my understanding, our buddy met the performance issue once he executes query against SQL Table by using Workplace object in VB. I have some suggestion regarding this issue.
=========================================
Option Compare Database
Sub QuerySQLServer()
Dim sqlCon As New ADODB.Connection
Dim sqlCmd As New ADODB.Recordset
On Error GoTo ErrHandler
Dim conStr As String, sqlQuery As String
conStr = "Provider=sqloledb;Data Source=SHA-LM-WK;Initial Catalog=Northwind;Integrated Security=SSPI;"
sqlCon.ConnectionString = conStr
sqlCon.Open
Set sqlCmd.ActiveConnection = sqlCon
sqlCon.BeginTrans
Set sqlCmd = sqlCon.Execute("SELECT count(*) FROM Employees")
MsgBox "Record Count:" & sqlCmd.Fields(0).Value
sqlCon.CommitTrans
sqlCon.Close
Set sqlCon = Nothing
Set sqlCmd = Nothing
Exit Sub
ErrHandler:
sqlCon.RollbackTrans
Set sqlCon = Nothing
Set sqlCmd = Nothing
End Sub
===========================================
1. Personally I prefer transaction in SQL store procedure (It will be better if you can combine parameters into store procedure and ‘Filter’ directly there inside SQL Server.). It will greatly improve the performance. It’s the recommended way for the applications with heavy data transporting/calculation involved.
2. If you insist on using ‘Filter’ property, I have following tips:
a) Set the Optimize property of field
Optimize Property-Dynamic (ADO)
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdprooptimizepropertyrds.asp
Optimize Property Example (VB)
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdprooptimizex.asp
b) Using client side cursor instead. (By default is server side cursor)
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM Authors", cn, adOpenStatic, adLockOptimistic
CursorLocation Property
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdprocursorlocation.asp
CursorLocationEnum
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdcstcursorlocationenum.asp
CursorType Property
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdprocursortype.asp
CursorTypeEnum
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdcstcursortypeenum.asp
I hope above information can help. Anything I can do regarding this issue, please feel free to let me know. Thanks. Regards, Ming
-brenda (ISV Buddy Team)
The_Jackal
First day I learned about searching, I knew the big performance difference between index search and sequential search.
Do you expect an opened recordset will have index built on the field "c" I won't expect. Even through you have an index on field "c" for table "t1", I don't believe the index will be used in the way you write the code. I believe it is sequential search.
So, make sure table "t1" has index on field "c" and try this:
set rs = db.openrecordset("select * from t1 c=" & c, dao.RecordsetTypeEnum.dbOpenDynaset, dao.RecordsetOptionEnum.dbSeeChanges).
I hope this would help.