June 9, 2014 at 8:05 am
Good Day
This is my SQL Query. Everything after the 3rd line is required due to dependancies.
SELECT Customer.EMail, Amount=SUM(ABS((SELECT Top 1 Amount+VATAmount FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ')))
FROM FreeWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.ResID = FreeWinners.ResID)
INNER JOIN Customer WITH(NOLOCK) ON (Reservations.EMail = Customer.EMail) WHERE FreeWinners.Comments_Approved!='No' AND FreeWinners.ResID
IN (SELECT ResID FROM Kitty WITH(NOLOCK) Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ'
AND Year(TransDate)>1900 AND Approved='Yes') AND FreeWinners.ResID NOT IN (SELECT ResID FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PC')
GROUP BY Customer.EMail, FreeWinners.ResID
ORDER BY Amount DESC
Some sample data
EMailAmount
bct@beyond.com46269.75
matomel@ag.co.za29700.00
yolan@jdo.co.za26400.00
sgre@nics.co.za25200.00
robgreish@hmail.com22500.00
bct@beyond.com20400.00
ghskhan@mail.com17940.00
carstens@mail.com16220.00
bishopk@sa.net16100.00
bishopk@sa.net16100.00
What I require is that the amounts for bct@beyond.com be added together
and then bishopk@sa.net values added together giving me the first and second records I require for
display. This will filter all through the data so if there are 5 amounts for bct@beyond.com I will
get a SUM returned for that email address based on the 5 amounts and then be sorted from highest to lowest.
So my output would then be
EMailAmount
bct@beyond.com66669.75
bishopk@sa.net32200.00
matomel@ag.co.za29700.00
I realise I can't have Subquery for the SUM and I'm not sure if I'm using the Grouping correctly.
Any help will be greatly appreciated.
June 9, 2014 at 8:31 am
BlackIceAngel (6/9/2014)
Good DayThis is my SQL Query. Everything after the 3rd line is required due to dependancies.
SELECT Customer.EMail, Amount=SUM(ABS((SELECT Top 1 Amount+VATAmount FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ')))
FROM FreeWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.ResID = FreeWinners.ResID)
INNER JOIN Customer WITH(NOLOCK) ON (Reservations.EMail = Customer.EMail) WHERE FreeWinners.Comments_Approved!='No' AND FreeWinners.ResID
IN (SELECT ResID FROM Kitty WITH(NOLOCK) Where Kitty.ResID=FreeWinners.ResID AND TransType='PZ'
AND Year(TransDate)>1900 AND Approved='Yes') AND FreeWinners.ResID NOT IN (SELECT ResID FROM Kitty WITH(NOLOCK)
Where Kitty.ResID=FreeWinners.ResID AND TransType='PC')
GROUP BY Customer.EMail, FreeWinners.ResID
ORDER BY Amount DESC
Some sample data
EMailAmount
bct@beyond.com46269.75
matomel@ag.co.za29700.00
yolan@jdo.co.za26400.00
sgre@nics.co.za25200.00
robgreish@hmail.com22500.00
bct@beyond.com20400.00
ghskhan@mail.com17940.00
carstens@mail.com16220.00
bishopk@sa.net16100.00
bishopk@sa.net16100.00
What I require is that the amounts for bct@beyond.com be added together
and then bishopk@sa.net values added together giving me the first and second records I require for
display. This will filter all through the data so if there are 5 amounts for bct@beyond.com I will
get a SUM returned for that email address based on the 5 amounts and then be sorted from highest to lowest.
So my output would then be
EMailAmount
bct@beyond.com66669.75
bishopk@sa.net32200.00
matomel@ag.co.za29700.00
I realise I can't have Subquery for the SUM and I'm not sure if I'm using the Grouping correctly.
Any help will be greatly appreciated.
One of the problems is that you have a derived column named the same as an actual column and then you order by that column. The sql engine will order by the persistent column, not the derived column alias.
Next you at least one predicate is nonSARGable.
Instead of Year(TransDate) > 1900 you should use TransDate > '1900-01-01'. The way you have it coded will force a scan on that column no matter what indexing you throw at it.
Then you have NOLOCK littering your select statement. Are you aware of what that hint brings to the table? Is duplicate and/or missing data acceptable?
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 12:08 am
Hi
I've found a solution but would still like to learn how to optimize it.
This is what's worked for me. I've changed the date as you suggested and for my current task the nolock is fine.
SELECT [Customer Name]=(SELECT Customer.FirstName + ' ' + Customer.Surname FROM Customer WITH(NOLOCK) WHERE EMail=Reservations.EMail)
,Amount=SUM(ABS(Kitty.Amount+Kitty.VATAmount))
FROM FreeWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.ResID = FreeWinners.ResID)
INNER JOIN Kitty WITH(NOLOCK) ON (Kitty.ResID=FreeWinners.ResID)
WHERE FreeWinners.Comments_Approved!='No' AND Kitty.TransType='PZ'
AND Kitty.TransDate>'1900-01-01' AND Kitty.Approved='Yes'
AND FreeWinners.ResID NOT IN (SELECT ResID FROM Kitty WITH(NOLOCK) Where Kitty.ResID=FreeWinners.ResID AND TransType='PC')
Group By Reservations.EMail
ORDER BY Amount DESC
June 10, 2014 at 7:23 am
BlackIceAngel (6/10/2014)
HiI've found a solution but would still like to learn how to optimize it.
This is what's worked for me. I've changed the date as you suggested and for my current task the nolock is fine.
SELECT [Customer Name]=(SELECT Customer.FirstName + ' ' + Customer.Surname FROM Customer WITH(NOLOCK) WHERE EMail=Reservations.EMail)
,Amount=SUM(ABS(Kitty.Amount+Kitty.VATAmount))
FROM FreeWinners
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.ResID = FreeWinners.ResID)
INNER JOIN Kitty WITH(NOLOCK) ON (Kitty.ResID=FreeWinners.ResID)
WHERE FreeWinners.Comments_Approved!='No' AND Kitty.TransType='PZ'
AND Kitty.TransDate>'1900-01-01' AND Kitty.Approved='Yes'
AND FreeWinners.ResID NOT IN (SELECT ResID FROM Kitty WITH(NOLOCK) Where Kitty.ResID=FreeWinners.ResID AND TransType='PC')
Group By Reservations.EMail
ORDER BY Amount DESC
Optimizing queries is almost as much art as it is science. There are many factors that go into making queries faster. We can help you if performance is an issue but we would need to see an actual execution plan at the very least. Also, tables definitions and indexes would be a big plus. I hope you didn't throw that NOLOCK hint in there to "make it faster".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply