[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

Query slow down after moving from MS Access 2003 to MS Access 2010

I have converted an application I built with MS Access 2003 to the new MS Access 2010 file format.

I've found that a particular query takes much longer now.

    CurrentDb.CreateQueryDef "qryNewlyCompleted", "SELECT x.ChequeNumber, x.ChequeDate, x.ChequeName FROM tblNSFs x, stblNSFs_original y WHERE x.ChequeNumber = y.ChequeNumber AND x.ChequeDate = y.ChequeDate AND x.ChequeName = y.ChequeName AND x.Completed = -1 AND y.Completed = 0"

    CurrentDb.Execute "UPDATE tblNSFs a SET [DateCompleted] = #" & DateValue(Format(Now(), "mm/dd/yyyy")) & "# WHERE EXISTS (SELECT 1 FROM qryNewlyCompleted b WHERE b.ChequeNumber = a.ChequeNumber AND b.ChequeDate = a.ChequeDate AND b.ChequeName = a.ChequeName)"

Open in new window

The Execute statement takes about 20 seconds in the new file format while it only took 1-2 seconds with the old format. Anyway to remedy this?

It might be worth mentioning that I've only converted the front-end to the new format, the back-end database which contains the tables is still an mdb. Could this be the reason the query is running slower? I haven't noticed problems with any other queries.
0
bejhan
Asked:
bejhan
  • 4
  • 3
  • 3
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Instead of CurrentDb.CreateQueryDef .... and CurrentDB.Execute ... try

DBEngine(0)(0).CreateQueryDef .. and DBEngine(0)(0).Execute ...

I'm curious to see if this makes any difference ...

"Could this be the reason the query is running slower? "
It doesn't seem likely .. but who knows.  Probably should test it ..

mx
0
 
Gustav BrockCIOCommented:
Just a note.
This could and should be reduced to:

.. SET [DateCompleted] = Date() WHERE

/gustav
0
 
RyanProject Engineer, ElectricalCommented:
You're doing a CROSS JOIN rather than an INNER JOIN on your sub query, that's going to hurt performance.  

SELECT x.ChequeNumber, x.ChequeDate, x.ChequeName
FROM tblNSFs x INNER JOIN stblNSFs_original y ON
x.ChequeNumber = y.ChequeNumber AND x.ChequeDate = y.ChequeDate AND x.ChequeName = y.ChequeName
WHERE x.Completed = -1 AND y.Completed = 0

I would also think that leaving the sub query in the DB would improve performance, since it could build indices for the query and not have to rebuild them every time you modify/recreate the sub query.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
bejhanAuthor Commented:
mx:
Replacing CurrentDb with DBEngine(0)(0) didn't improve the situation.
Converting the back-end database to the newest format also didn't have any effect.

gustav:
Thanks for the tip.
FYI, it didn't have any effect on performance, not that it should've.

MrBullwinkle:
I've always thought cross joins and inner joins had similar performance.
In this case, anyways, the inner join did not increase performance.
As well, leaving query qryNewlyCompleted in the database had no effect.

I'm quite puzzled that the query performance of this query decreased so substantially moving from MS Access 2003 to 2010.

Any other way I can complete the same action with better performance?
0
 
Gustav BrockCIOCommented:
> Any other way I can complete the same action with better performance?

Use a select query to find/extract the records/values to update from.
Write these to a temp table with fields that match the table to update.
Now run an update query using the temp table as source.

/gustav
0
 
RyanProject Engineer, ElectricalCommented:
I dont think Access allows it, but make sure your datatypes that are linked between the tables are the same.  Datatypes are 'correct', not all strings? Also index those fields which are referenced in the join/Where.  

Compact and repair the backend?

When I was taught Oracle, CROSS JOINS were a said to be a big no-no. It seems most db engines will treat them the same if the WHERE clause is used. I guess it's as much a style preference.

If I recall, NULL values also play havoc as they test to true no matter what? That's coming from way back in my mind; I could be wrong.

Also, we aren't talking about 5M+ records in Access, are we?
0
 
bejhanAuthor Commented:
Amazing what a temporary table can do.
0
 
Gustav BrockCIOCommented:
> Amazing what a temporary table can do.

Exactly. Many try to "avoid" them, but they do have their place.

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
But the question remains, why did this run (as is) fast in A2003 ?

mx
0
 
RyanProject Engineer, ElectricalCommented:
Yes, I'm curious too, as our company just switched from 2007 to 2010, and I've only seen 1 issue thus far.
0
 
bejhanAuthor Commented:
Yes, that is still quite puzzling.
0
 
RyanProject Engineer, ElectricalCommented:
The only thing relevant that I'm seeing is they added some datatypes to A2010 to match with SQLServer08.

I wouldn't doubt there were some engine changes, but odd that a "simple" query like yours got significantly worse.

I've seen some other questions on other sites that has issues with queries/reports displaying different info in A03 vs A10, but the solution/root cause wasn't posted.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Overall, in general ... I'm seeing if anything, A2010 does run slower for various operations.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now