Sql query optimisation needed for MS access 2010

Question

0.00/5 (No votes)

See more:

I have a table, MyTable, in which items are stored with their individual reference code, ReferenceCode but there are different versions of each ReferenceCode as the items have been updated over time so we can look back to historical records for each ReferenceCode.

I've written some SQL which pulls out the entries with the highest version number to as to extract the newest version of each ReferenceCode and give me a ‘live’ table. However, it seems to take forever (5.5 MINUTES!) to extract the data into a table - and that's in Access itself!

There are about 16,000 entries in the table, and just over half of those are the current live versions. The table has about 20 fields and yes, I do need to SELECT *.

Is there a quicker way of doing this in a single SQL statement?

What I have tried:

select * from [mytable] where ((([mytable].version)=(select max(x.version) from [mytable] as x where x.referencecode = [mytable].referencecode))) order by referencecode;

Posted 7-May-17 22:28pm

ICTIS-THL

Updated 8-May-17 0:47am

Add a Solution

Comments

CHill60 8-May-17 5:09am

Does this work any better?

select A.* from [myTable] A
INNER JOIN (select referencecode, max([version]) as [version] from [myTable] group by referencecode) B
	ON B.referencecode = A.referencecode AND A.[version]=B.[version]
order by A.referencecode

ICTIS-THL 8-May-17 5:27am

If you'd care to submit the edited code as solution, I'll gladly accept it. :-)

ICTIS-THL 8-May-17 5:12am

Nope... "Circular refence caused by alias 'version' in query definition's SELECT list"

ICTIS-THL 8-May-17 5:19am

But I editied it to:
select A.* from [myTable] A
INNER JOIN (select referencecode, max(myTable.[version]) as [version] from [myTable] group by referencecode) B
ON B.referencecode = A.referencecode AND A.[version]=B.[version]
order by A.referencecode

And that seems blisteringly fast...

CHill60 8-May-17 6:38am

Yeah - sorry about missing that table alias :facepalm: I tested it in SQL rather than Access and the former is less fussy about stuff like that.
Joins are always faster than straight forward in line sub-queries.
Glad to have helped.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-05-08T00:47:00

OP has confirmed that the following query is much faster (after they corrected my mistake :0)

SQL

select A.* from [myTable] A
INNER JOIN (select referencecode, max(myTable.[version]) as [version] from [myTable] group by referencecode) B
	ON B.referencecode = A.referencecode AND A.[version]=B.[version]
order by A.referencecode

I said in my comment that "Joins are always faster than ... sub-queries" - that is not strictly true. It's true in this case because the original query was using a correlated sub-query - i.e. it was referencing the outer query ...

select * from [mytable] where ((([mytable].version)=(select max(x.version) from [mytable] as x where x.referencecode = [mytable].referencecode))) order by referencecode;

See Correlated subquery - Wikipedia[^]