December 11, 2012 at 7:44 am
Hi
Can anyone see a loop in this query
SELECT AA.[Country Code],
SUM(extendedamount) AS Amount
FROM dbo.AllActiveAuthorised AS AA
WHERE dispatcheddate >= CAST(''20121101'' as DATETIME)
AND dispatcheddate < CAST(''20121201'' as DATETIME)
AND EXISTS
(
SELECT *
FROM dbo.AllActiveAuthorised AB
WHERE AB.address2_fax = AA.address2_fax
AND AB.dispatcheddate < CAST(''20121101'' as DATETIME)
AND AB.[Country Code] = AA.[Country Code]
AND AB.allowmail = ''Allow''
AND AB.statuscode = ''1''
AND AB.brandname = ''Product A''
)
AND allowmail = ''Allow''
AND statuscode = ''1''
AND brandname = ''Product A''
GROUP BY
[Country Code]
ORDER BY
[Country Code]
December 11, 2012 at 8:02 am
No, there are no LOOPs here.
December 11, 2012 at 8:04 am
But there is a correlated subquery which could be considered a bit like a loop.
Cursors never.
DTS - only when needed and never to control.
December 11, 2012 at 8:09 am
This does something similar but without the correlated subquery.
Depends on your data as to whether it is any better
SELECT AA.[Country Code],
SUM(extendedamount) AS Amount
FROM dbo.AllActiveAuthorised AS AA
WHERE dispatcheddate >= CAST('20121101' as DATETIME)
AND dispatcheddate < CAST('20121201' as DATETIME)
join
(
SELECT distinct address2_fax, [Country Code]
FROM dbo.AllActiveAuthorised AB
WHERE AB.dispatcheddate < CAST('20121101' as DATETIME)
AND AB.allowmail = 'Allow'
AND AB.statuscode = '1'
AND AB.brandname = 'Product A'
) AB
ON AB.address2_fax = AA.address2_fax
AND AB.[Country Code] = AA.[Country Code]
AND allowmail = 'Allow'
AND statuscode = '1'
AND brandname = 'Product A'
GROUP BY
[Country Code]
ORDER BY
[Country Code]
Cursors never.
DTS - only when needed and never to control.
December 11, 2012 at 8:38 am
I am getting an error on near join
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'join'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'AB'.
December 11, 2012 at 8:46 am
olivia.forde (12/11/2012)
I am getting an error on near joinMsg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'join'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'AB'.
That's because nigelrivett placed a join after the WHERE clause.
December 11, 2012 at 8:54 am
Oops yes
SELECT AA.[Country Code],
SUM(extendedamount) AS Amount
FROM dbo.AllActiveAuthorised AS AA
join
(
SELECT distinct address2_fax, [Country Code]
FROM dbo.AllActiveAuthorised AB
WHERE AB.dispatcheddate < CAST('20121101' as DATETIME)
AND AB.allowmail = 'Allow'
AND AB.statuscode = '1'
AND AB.brandname = 'Product A'
) AB
ON AB.address2_fax = AA.address2_fax
AND AB.[Country Code] = AA.[Country Code]
AND allowmail = 'Allow'
AND statuscode = '1'
AND brandname = 'Product A'
WHERE dispatcheddate >= CAST('20121101' as DATETIME)
AND dispatcheddate < CAST('20121201' as DATETIME)
GROUP BY
[Country Code]
ORDER BY
[Country Code]
Cursors never.
DTS - only when needed and never to control.
December 11, 2012 at 8:59 am
Correlated subqueries are not necessarily executed once per row of the outer query (which I assume is where the 'like loops' came from), the one in this query certainly isn't.
Joins are not more efficient than EXISTS, they're slightly less efficient in most cases, . That's ignoring the possibility of the join version returning different results (a join is a full join, an exists is a semi-join). Adding a distinct to the subquery to remove any chance of duplicates will just make the join version even less efficient as there now needs to be a distinct sort added.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2012 at 9:08 am
GilaMontser - so the way I have it is the most efficient - it takes hours to run so I was hoping it could be improved.
December 11, 2012 at 9:29 am
nigelrivett (12/11/2012)
This does something similar but without the correlated subquery.Depends on your data as to whether it is any better
SELECT AA.[Country Code],
SUM(extendedamount) AS Amount
FROM dbo.AllActiveAuthorised AS AA
WHERE dispatcheddate >= CAST('20121101' as DATETIME)
AND dispatcheddate < CAST('20121201' as DATETIME)
join
(
SELECT distinct address2_fax, [Country Code]
FROM dbo.AllActiveAuthorised AB
WHERE AB.dispatcheddate < CAST('20121101' as DATETIME)
AND AB.allowmail = 'Allow'
AND AB.statuscode = '1'
AND AB.brandname = 'Product A'
) AB
ON AB.address2_fax = AA.address2_fax
AND AB.[Country Code] = AA.[Country Code]
AND allowmail = 'Allow'
AND statuscode = '1'
AND brandname = 'Product A'
GROUP BY
[Country Code]
ORDER BY
[Country Code]
It does something "similar", but not the same,
Replacing EXISTS check with INNER JOIN,since sql2005, rarely will make your query better or faster (especially after using DISTINCT tom prevent duplications in results).
December 11, 2012 at 9:31 am
olivia.forde (12/11/2012)
GilaMontser - so the way I have it is the most efficient - it takes hours to run so I was hoping it could be improved.
Could you please post execution plan.
There are many possible reason why your query is slow...
And hopefully it will be possible to improve its performance
December 11, 2012 at 9:48 am
olivia.forde (12/11/2012)
GilaMontser - so the way I have it is the most efficient - it takes hours to run so I was hoping it could be improved.
I didn't say that. I just said that exists is not slower than joins in most cases.
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2012 at 8:19 pm
olivia.forde (12/11/2012)
GilaMontser - so the way I have it is the most efficient - it takes hours to run so I was hoping it could be improved.
I would say that you probably have something much worse than a simple loop in the code. Based on what I see and based on the amount of time it takes to run, I'd say you have all of the makings for a many-to-many query which is lovingly known as an "accidental CROSS JOIN'.
The only way to know for sure is if you were to read the article at the second link in my signature line below and post the information requested by that article. That would also give us the information we need to help you fix this problem. It may very well be that one of the posted solutions is the solution once a proper index is added. Then again, maybe not. We can't tell until we get the information in the form requested in the article.
As a side bar, Gail is absolutely correct. Correlated subqueries the represent equalities are not the bad thing the most think they are and will usually be incorporated into the execution plan in a totally set-based fashion that will frequently outstripe even a proper join with proper indexing. APPLYs (CROSS and OUTER) work in the same manner... they're really just a super convenient and easy to program correlated subqueries with the added benefit of being able to return multi-row results instead of being limited to single row results.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply