slow query from vba

[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

[ADezii]

@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?

[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

[ADezii]

@jimatqsi

I have improved performance by moving the querying out of the Detail section

What exactly do you mean here, Jim?

[jimatqsi]

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