Subsequent OpenRecordSet execution very slow

Hi Experts
I have a VB6 application that uses an MS Access local database.
In the app, I execute the following open recordset command - each time it runs it gets new parameters and returns ~2000 records.
The first time it runs is very fast (1-2 seconds), but when I change the passed in numbers and re-run, it takes almost 25 seconds.

1st run - fast!
SELECT DISTINCT G_Id FROM myTable   WHERE H_ID IN (18705,3211,17401,11007,31109,7667,17407,20029,18279,14999,22138,17494,6859,33480,15584)

subsequent runs - slow!!
SELECT DISTINCT G_Id FROM myTable WHERE H_ID IN (25006,17460,8761,29293,14302,7074,29289,12997,10005,32576,3226,31235,31518,7194)

I've tried:
* verifying recordset object is .closed, set = nothing
* closing and reopening the database object between runs
* setting recordset as a global level and not closing/setting to nothing & leaving database open
* Using recordset type options of dbOpenDynaset, dbOpenSnapShot; both with and without dbReadOnly.

Nothing changes the subsequent run delay.
Any advice?
Thanks
k heitzsoftware developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
How are you running that select statement?   Via querydef or are you executing it in code on the fly?

Jim.
0
k heitzsoftware developerAuthor Commented:
In code on the fly.
Here is the call:

Set grstDistinctGID = gdbsGD.OpenRecordset(sSQL, dbOpenDynaset)
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Hum, not what I expected.

 I thought it was a matter of a query plan that is very inefficient getting saved for subsequent runs, but now that I think it through anyway, that still doesn't explain why your first run is fast (should be the same for all runs) unless the data in the tables is changing substantially from one run to the next.

  I have seen in the past where JET will do a three table join one way if one of the tables is nearly empty and have a very efficient plan, then do it exactly the opposite if the table is full and do it very inefficiently as a result.

 Something like that may be at work; is data changing substantially?

 and have you reviewed indexing?

Jim.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

k heitzsoftware developerAuthor Commented:
Hi Jim;
The underlying table doesn't change at all, although the database is big (getting close to 2GB) and this is the largest table in the database. But as you mention, why does it run so fast at first execution, then drag?

I ran the same queries w/n the access database and both ran quickly.
I've also verified indexes. G_ID and H_ID are both indexed (duplicates OK).
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I've also verified indexes. G_ID and H_ID are both indexed (duplicates OK). >>

  Does the table have a PK defined?

  Outside of that, I think you'd need to activate JET SHOWPLAN to figure out what's going on and maybe get a clue why it's different.

  Turning show plan on is a registry change.   When enabled, it writes the queries costing plan to a text file.  It has some limitations however. ...it won't show stacked or sub queries.

 But it may give you some clue as to what the issue is and if it really is using the same plan from one run to the next.

 Two other questions
1. Does it ever go back to being fast?
2. Does it change if you run a compact and repair on the DB?
0
k heitzsoftware developerAuthor Commented:
Hi Jim;
Yes it has a PK field which isn't part of the query (I wonder if I should try including it (?))
I've never used JET SHOWPLAN so I'll do some research on it.

re: the other two questions -
1. Does it ever go back to being fast? - No. Not until I exit the program and re-launch
2. Does it change if you run a c/r on the DB: - No. No difference.

I'll let you know what I find w/ JET SHOWPLAN.
Thanks!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Oh no need for research.  For a JET 4.0 based DB, the key is:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\Debug

Create a string key of JETSHOWPLAN  (all caps).

Set it's value to "ON".

Execute your query, then look for SHOWPLAN.OUT either in my doc's or the current directory.

Have a look at it.   Now run the SQL again (you may want to clean it out/delete between runs).

Jim.
0
k heitzsoftware developerAuthor Commented:
Hi Jim - Sorry I got pulled away onto another project yesterday...
I don't have a Debug folder in my \\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines\.
Under Engines I have: 3.5 & 4.0; under 4.0 I have \Excel \ Exchange \Jet2.x \Jet 3.x \Jet4.0 \Lotus \ODBC \Paradox \Test \xBase.

No sub folders under any of these. (?)
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry for the delay.   You might have to create the keys.  I've attached two reg files which you can import.

Jim.
TurnJetShowPlanOFF.reg
TurnJetShowPlanON.reg
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Note that on the .reg files, you should be able to right click and do "Merge".   If not, open regedit and do an import.

Jim.
0
k heitzsoftware developerAuthor Commented:
Hi Jim -
Finally getting back to this problem. Thank you so much for the SHOWPLAN advice and keys.

Using SHOWPLAN=On, I found after the DISTINCT query, every returned G_ID is being put thru another query to determine if it falls w/n the predetermined date range (from a separate table). This can be 2000 add'l queries.

This set of add'l queries doesn't happen until after the first DISTINCT recordset call, so it appears they are the cause of the hang on the subsequenct DISTINCT calls.

I suspected the add'l queries were not closed/set = nothing, but they all are.

So I added a relationship between the tables on !G_Date and that seems to have increased speed some.

I open the DISTINCT queries with dbOpenSnapshot; and the Showplan.out file shows the following:

--- temp query ---

- Inputs to Query -
Table 'PAE'
- End inputs to Query -

01) Restrict rows of table PAE
      using rushmore
      for expression "H_ID In (18705,17401,11007,31109,7667,17407,6195,20029,18279,16583,14999,22138,17494,33480,15584)"
      store result in temporary table

*** The .out for subsequent queries look like: (there is one for every distinct G_ID) are also opened with dbOpenSnapshot

--- temp query ---

- Inputs to Query -
Table 'GH'
- End inputs to Query -

01) Restrict rows of table GH
      using rushmore
      for expression "(G_ID=2) AND (G_Date>=#4/24/2015# And G_Date<=#4/24/2015#)"

***
I noticed there is nothing about 'store result in temporary table' on the above. Not sure if that is significant.

As I tested with the .out files, renaming them as they were created to keep them straight, I found the DISTINCT openrecordset call was consistently fast (I put a stop point on the openrecordset code and timed how long to continue execution).

This seems to indicate the d/b needs some time to recover from the many add'l queries. Do you know if this could be and if so, is there something I can force to flush it out? Closing and reopening the d/b didn't work.

Maybe it's simply a limitation due to all the add'l queries being called. I may need change how I get that information.

Sorry for the small novel :)
Thansk
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
On:

<<I open the DISTINCT queries with dbOpenSnapshot; >>

 Don't do this....your forcing it to grab a complete copy of the record.   Take that out and see what the difference is.

 Second, delete all the .TMP files in TEMP and TMP locations (two environment variables that point to temp directories - get them by going to a command prompt and typing SET.  you'll see the directories they point to).

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Make sure G_Date is indexed to.

Jim.
0
k heitzsoftware developerAuthor Commented:
Hi Jim -
Removing the dbOpenSnap shot helped (i removed it from the DISTINCT as well as the add'l openrecordset calls).
The set rs command is definitely faster - I still see intermittent delays but they are not every time; only every 4-5 iterations, which is manageable.

G_Date is indexed (yes duplicates)

I've found/watched the size of the .tmp file. I can't manually delete it because it says it's in use by my program.
Running tests against the delay I haven't been able to find a correlation. I thought I saw the size increase impact the delay, but found that not to be consistent.

It's size goes to 0 when I exit the program.

At one point when the delay was very noticeable, a new file was written to temp (filename: etilqs_2yH2Px21lwfLJPu no extension; hidden). After the set rs command completes, this file disappears so it seems related.

I'm going to call this a done task. The changes you helped ID and recommended are working so much better than previously.

Thank you for all your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.