November 8, 2007 at 9:56 am
Christine M (11/8/2007)
These are what worked. Thanks!SELECT IsNull(q1.ProdGroup, q2.ProdGroup) as NameofProdGroup,
ProdCodePROD,
ProdCodeANL
FROM (
select ProdGroup,
count(ProdCode) as ProdCodePROD
from smcscalc..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
) As q1
FULL OUTER JOIN (
select ProdGroup,
count(ProdCode) as ProdCodeANL
from smcscalcanalysis..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
) As q2
ON q1.ProdGroup = q2.ProdGroup
and
SELECT
c.ProdGroup,
c.CalcCountProdCode,
ca.CalcCountCACode
FROM
(
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountProdCode
FROM smcscalc..ERVProdGrp_ProdCode
WHERE ProcessDate < '12/20/2007'
GROUP BY ProdGroup
)c,
(
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountCACode
FROM smcscalcanalysis..ERVProdGrp_ProdCode
GROUP BY ProdGroup
)ca
WHERE c.ProdGroup = ca.ProdGroup
For whatever reason, the latter seems to be quicker.
Christine, Brendt's code (the first one) is using a full join, meaning that it is showing you all the records form both tables, even if a ProdCode only exists in one table. This may be what you want, and maybe not. My code (the latter) is only showing you ProdCodes that exist in both tables. I don't know the mechanics of it, but an INNER JOIN ( the ,) is faster than an OUTER JOIN. At least that is the way I understand it, and some of the other folks here could explain why.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 8, 2007 at 10:41 am
I can explain why an outer join is slower than an inner join on indexed fields.
On an Inner Join, you make the explicit assumption that you only want the items from table B if they match the items from table A on the Join Value(s). So, if your join is
[Code]
SELECT *
FROM A
INNER JOIN B
ON B.ID = A.ID
[/Code]
Then the process goes through the index of table A and looks for matches in table B, using whichever matching algorithm the analyzer decides is optimal for the data in your tables. Only if there are rows in both table A and table B with a given ID will the values be returned
On the other hand, an Outer Join will return all of the values from at least one table, and any matching values from the other table. So, if doing a LEFT JOIN, then all of the records from table A will be returned, and any matching records from table B will be returned. If all entries in A exist in B, the time taken will be at most negligibly different between this OUTER joint and the INNER JOIN. However, if B has fewer entries than A, then there will be more data returned, which at the minimum will take the extra time to retrieve and return the data. The query could also be slower because the requirement that all of A be returned could result in a less optimized query being generated internally.
A FULL OUTER JOIN will almost always be slower, since the process must check both tables to identify all records that will be returned, then match the two table extracts together. So, instead of looking for all records meeting the query criteria in table A and matching records in table B that match the JOIN requirements, the optimizer will usually get all the records from table A that match the requirements, then get all of the records for table B, then match the two recordsets. This is essentially guaranteed to be slower than the process used on an INNER JOIN.
November 8, 2007 at 10:23 pm
Greg,
I'm working on a couple of work arounds and a full "wish list" to Tony... I'll get back on these formatting issues... just not right now...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply