slow query from vba | Member | | Join Date: Oct 2006 Location: Virginia
Posts: 65
| | |
I have the query "ICOnHandByAlias", which is saved in an Access 2003 frontend. I can open this query and get a result set of 4,900+ rows in about 1 second. The table being accessed is in a SQL backend.
I have a vba routine which puts together the code to pull one of these rows, as follows:
Select * from ICOnHandbyAlias WHERE ([ItemAlias]="MD-BC1/OB25")"
The actual vba code I am using is
strSQL = "Select * from ICOnHandby Alias "
strCriteria = " Where ([ItemAlias] = """ & me!strProductID & """) ;"
Set rsItemWH=dbs.OpenRecordset(strSQL & strCriteria, dbopendynaset,dbseechanges)
This last statement, the SET statement, takes 6 seconds to return the result.
What's causing this 1-second query for 5000 results to take 6 seconds to return one row?
Thanks,
Jim
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,429
| | | re: slow query from vba
Quote:
Originally Posted by jimatqsi  I have the query "ICOnHandByAlias", which is saved in an Access 2003 frontend. I can open this query and get a result set of 4,900+ rows in about 1 second. The table being accessed is in a SQL backend.
I have a vba routine which puts together the code to pull one of these rows, as follows:
Select * from ICOnHandbyAlias WHERE ([ItemAlias]="MD-BC1/OB25")"
The actual vba code I am using is
strSQL = "Select * from ICOnHandby Alias "
strCriteria = " Where ([ItemAlias] = """ & me!strProductID & """) ;"
Set rsItemWH=dbs.OpenRecordset(strSQL & strCriteria, dbopendynaset,dbseechanges)
This last statement, the SET statement, takes 6 seconds to return the result.
What's causing this 1-second query for 5000 results to take 6 seconds to return one row?
Thanks,
Jim Hello Jim, try the following to see what happens. - Create an Index on the [ItemAlias] Field in SQL Server.
- Open the Recordset as a Snapshot and not Dynaset if it is OK for it to be Read Only.
- Eliminate the dbSeeChanges Option.
P.S. - How and where are dbs and rsItemWH Declared?
| | Member | | Join Date: Oct 2006 Location: Virginia
Posts: 65
| | | re: slow query from vba
Hi and thanks for your reply.
I had already created the indices that seemed to make sense to make. The declarations were
dim dbs as dao.database
dim rsItemWH as dao.recordset
I have improved performance by moving the querying out of the Detail section and changing the query that the report was bound to. The result is that the report that was taking 7 minutes now takes 30 - 40 seconds.
As an aside, I think I was not actually getting that entire recordset in 1 second, I think I was only getting the first few, visible rows.
Thanks again,
Jim
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,429
| | | re: slow query from vba
Quote:
Originally Posted by jimatqsi Hi and thanks for your reply.
I had already created the indices that seemed to make sense to make. The declarations were
dim dbs as dao.database
dim rsItemWH as dao.recordset
I have improved performance by moving the querying out of the Detail section and changing the query that the report was bound to. The result is that the report that was taking 7 minutes now takes 30 - 40 seconds.
As an aside, I think I was not actually getting that entire recordset in 1 second, I think I was only getting the first few, visible rows.
Thanks again,
Jim Quote:
I have improved performance by moving the querying out of the Detail section
What exactly do you mean here, Jim?
| | Member | | Join Date: Oct 2006 Location: Virginia
Posts: 65
| | | re: slow query from vba
I chose to eliminate the vba code in the detail_print section. That code was fetching data from ICOnHandByAlias based on the product item that was in the detail.
In order to get the data, I also changed the query that the report was bound to so that the underlying query was joined to ICOnHandByAlias. It provided the same information much more quickly.
Jim
|  |
Similar Microsoft Access / VBA bytes | | |  Forums
Visit our community forums for general discussions and latest on Bytes
/bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 229,155 network members.
|
