How to improve this Access SQL query

Question

I've got a query here which does what it is supposed to but I'm wondering if it is possible to make it more efficient without splitting it into 2 separate queries.

SELECT [Full UO Code] FROM HPP_MD
WHERE LEFT([Full UO Code],5) In 
(SELECT IIF([Building]="Site" AND 
([Function Name_Usage Type]<>"Property" AND 
[Function Name_Usage Type]<>"Undeveloped Land"),[Business Entity],"") As Test 
FROM HPP_MD);

This is just a simple Access query that I'm playing with. It is easy to get the results I require if I run the subquery as a 'create table' query and then run a second query against that new table to get the required data.

I created the query/subquery out of curiosity to see how it worked but it is pretty slow and I'm wondering if anything can be done to increase efficiency. Is creating a temporary column the correct thing to do here? I couldn't quite conceive of another way of doing this.

Any thoughts? I know the column headers are pretty atrocious but this was just a quick table I imported into Access and I couldn't be buggered to change the names in this case.

Andrew Lazarus · Accepted Answer · 2011-11-07 04:48:10Z

2

It looks as if the IN always fails if the big AND comes out false. That's assuming [Full UO Code] is never the empty string, which may be wrong. But if I'm correct, you can get rid of the IIF, which basically can't be optimized, into a WHERE

/* ... IN */
(SELECT [Business Entity] FROM HPP_MD 
 WHERE 
 [Building]="Site" AND 
   ([Function Name_Usage Type]<>"Property" AND 
   [Function Name_Usage Type]<>"Undeveloped Land")

If this is correct so far, you may be able to rewrite the subquery into a self-join. That may or may not be faster. In any case, [Building] and [Function Name_Usage Type] should be indexed.

answered Nov 7, 2011 at 4:48

Andrew Lazarus

19.5k3 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Splatgore Over a year ago

Thanks for this! I'm so used to writing IF statements that it didn't occur to me to use this approach. I'll have another play!

Collectives™ on Stack Overflow

How to improve this Access SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related