By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

slow query from vba

Expert 100+
P: 1,221
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
Jun 29 '09 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,679
@jimatqsi
Hello Jim, try the following to see what happens.
  1. Create an Index on the [ItemAlias] Field in SQL Server.
  2. Open the Recordset as a Snapshot and not Dynaset if it is OK for it to be Read Only.
  3. Eliminate the dbSeeChanges Option.
P.S. - How and where are dbs and rsItemWH Declared?
Jun 29 '09 #2

Expert 100+
P: 1,221
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
Jun 30 '09 #3

ADezii
Expert 5K+
P: 8,679
@jimatqsi
I have improved performance by moving the querying out of the Detail section
What exactly do you mean here, Jim?
Jun 30 '09 #4

Expert 100+
P: 1,221
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
Jun 30 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.