Connecting Tech Pros Worldwide Help | Site Map

slow query from vba

  #1  
Old June 29th, 2009, 06:07 PM
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
  #2  
Old June 29th, 2009, 11:57 PM
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: slow query from vba


Quote:
Originally Posted by jimatqsi View Post
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.
  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?
  #3  
Old June 30th, 2009, 07:35 PM
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
  #4  
Old June 30th, 2009, 08:25 PM
ADezii's Avatar
Expert
 
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: slow query from vba


Quote:
Originally Posted by jimatqsi View Post
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?
  #5  
Old June 30th, 2009, 08:36 PM
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
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cumulative Sum in query is very slow... cefrancke@yahoo.com answers 4 November 13th, 2005 10:07 AM
Make-table query performance issues rednexgfx_k@hotmail.com answers 7 November 13th, 2005 07:26 AM
Getting data into Access Table from SQL SenseForAll answers 2 November 12th, 2005 08:34 PM
Large table/slow query/ can performance be improved? Robert answers 3 July 20th, 2005 02:33 AM