asked on

Join Tables on Calculated Field

In MS Access sql, is one of the following query methods more efficient the other? I'm joining two tables on a cost center field.
-Table names are tGFRS and tIQN.
-In the tIQN table only, the Cost Center field has multiple values for some employees separated by semicolon. For example, 5545Q7654; 4675Q2332; 9087Q2090

My business rule is to only use the right-most Cost Center

Is there any difference in query efficiency if I write the query to filter a Cartesian product? Version A:

SELECT qIQN.*, tGFRS.Rate
FROM qIQN, tGFRS
WHERE (((tGFRS.Bu)=Right([qIQN]![CostCenter],10)));

Open in new window

or version B, where use two queries in succession: first to parse out the right most cost center and then do an inner join

SELECT qIQN2.*, tGFRS.Rate, 
FROM tGFRS INNER JOIN qIQN2 ON tGFRS.Bu = qIQN2.CostCenter;

Open in new window

Thanks

lcohan

I would definitely opt for the version B and if you don't have lots of rows to return you could easily do that by using a CTE to populate it with a list of the right most cost centers and then do an inner join on that CTE table.

Ron Malmstead

In SQL Server Management Studio, run both queries...whichever one has the fastest execution time (shown at the bottom), is the more efficient query.

Davisro

ASKER

well, I suppose I neglected to mention that I'm running this in MS Access. Ive added that to the front of my post

Ron Malmstead

Version B is my vote as well.

David Todd

Hi,

Do try to avoid Cartesian products - they rapidly consume large amounts of resources.

Regards
David

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Davisro

ASKER

Scott, from my hunt and peck research on this issue, it seems the current consensus is, "It depends". I do agree with the single operation idea which ive read about.

Scott Pletcher

My reasoning is: If SQL took you literally on Version B, it would:
(1) scan the entire table, find the last 10 bytes and the other needed columns, and write that out to a new table
(2) scan that entire table (again) in order to do the match

In Version A, SQL presumably could do the match in a single pass of the table.

Mark Wills

I wouldnt do either.... I would combine the methods, with a couple of tweaks....

Just to be 100% sure, this is MS Access - Right ?

And not SQL Server - or - are you sunning SQL Server backend ?

Assuming MS Access.... Then....

SELECT qIQN2.*, tGFRS.Rate, 
FROM qIQN2
INNER JOIN tGFRS  ON tGFRS.Bu =trim(right( qIQN2.costcenter,len(qIQN2.costcenter) - InstrRev(';'+qIQN2.CostCenter,';')+1))

Open in new window

Not a fan of Cartesian products, and considering costcenter in the example is only 9 characters, not 10 then not overly comfortable with Right([qIQN].[CostCenter],10)))

So, considering we are already using functions on a Cartesian join, may as well extract the right most costcenter no matter how long, or how many are in that multi-concatenated-costcenter-string and use in the inner join.

And because you are selecting qIQN2.* and only Rate from tGFRS then make qIQN2 the driving table (and most likely doing the table scan anyway)...

Bit more fiddly, but will suit a wider variety of condition so long as the separator is always a semi-colon in costcenter.

Ideally, you should consider creating a new table to formalise that employee / costcentre relationship to avoid having to use varuous functions to extract - that is not going to be scalable and will impact performance (big time).

If it is SQL Server, then it does change syntax, but would still try to resolve in a single pass, but, would need to see the entire query (where clauses et al).

Mark Wills

Apologies.... Forgot to hit enter before I closed the laptop, then on the road for a few hours, only to discover this "open" post some time later.....

Anyway, hope you find the post above of some benefit.