Which is more efficient? (1 Viewer)

[RossWindows]

In the picture attached, there are two fields we're working with;
Address1 and DepartmentID

The picture shows two different queries that return the same results.

If you wanted to run this query where;
Address1 = "123 Fake St." and
DepartmentID = 28 or 29

Which method would be more efficicent, and why?

I think it would be the bottom method in the picture because it creates a simpler-looking SQL statement, but I have no theoretical basis to that hypothesis.

There was a real address that I had to black-out so just pretend it says "123 Fake St."

 

[Banana]

I'll bet that if you switch to SQL view in the query builder, you'll find that both statements are actually identical. The only thing that's different is how it's visually presented in the builder but the visual layout has no relation to how the SQL is actually constructed (and why should it? Moving the tables may be helpful in making a query more clearer but it won't change the underlying SQL statement at all).

 

[boblarson]

The bottom method is more efficient because you only have to put the criteria for the address line in once

 

[Banana]

Well, I would argue for top method being more efficient on the account of number of keystrokes:

Four keystrokes for the criteria and a down arrow for the top compared to four keystroke for the criteria plus four more for " OR ", so top method saves us 3 keystrokes.

 

[boblarson]

How about the number of keystrokes as you have to move from column to column if you have more than two criteria on the right side? The more criteria, it would get less efficient, especially with a large number of OR's (for which you could switch to IN).

So, a bunch of silliness anyway, eh? For as you stated, it makes no difference really what you do (although I've seen Access reformat it to the lower version from the top after a save).

 

[Banana]

I happened had a second to quickly test, and I wasn't quite right. It's very similar but not identical.

Top version

SELECT 1 AS Expr1
FROM dual
WHERE (((1)=27 Or (1)=28));

Bottom version

SELECT 1 AS Expr1
FROM dual
WHERE ((1)=27 OR (1)=28);

The difference being a extra (), which is probably just one extra cycle. Big whooopee considering that we're using several gigahertz chips nowadays. But Bob is right; it get reformatted anyway.

 

[gemma-the-husky]

what about using IN

SELECT 1 AS Expr1
FROM dual
WHERE 1 in (27,28);

is this processed in the same way as far as the compiler goes, or is this optimised more efficiently?

 

[Banana]

Good question.

I do know for a fact that IN() in the WHERE is actually a compile-time shortcut for OR in case of SQL Server & MySQL. This is also why you can't do this:

PARAMETERS foo TEXT;
SELECT 1 AS Expr1
FROM dual
WHERE 1 IN (foo);

(or in SQL Server/MySQL's parlance)

SELECT 1
FROM dual
WHERE 1 IN (@p);

There's no way to basically say "here, parse this string of delimited values for the IN at runtime" short of using dynamic SQL, though the Erland (a SQL Server MVP) gave a cool solution of transforming a space-delimited arrays of integers into a temporary table and doing a join to do just that; I've actually used that myself).

I know that Jet also does not accept the above query with a parameter so it must be passed as a dynamic SQL (e.g. use VBA to build the query just in time) so though I don't have an explicit text saying so, I'm inclined to say Jet is same as SQL Server & MySQL in that it treats X IN(A,B,C) as same thing as X = A OR X = B OR X = C, so it exists mostly for our convenience rather than for optimizing the query.