September 25, 2014 at 12:23 pm
Hi,
We have a view created,in that .now the results are comming in 2 seconds when we execute.We are trying to get it in 1 sec or less.
September 25, 2014 at 12:59 pm
It is hard to know for sure with just a query but WOW there is a lot of low hanging fruit in here.
Let's start by formatting this into some more legible.
SELECT --------(bla bla bla...)
FROM (
SELECT (c.FirstName + ' ' + c.LastName) AS 'Contact_Name'
,c.LoginName
,ISNULL(s.Case_Load, 0) AS 'CL'
FROM (
SELECT x.LoginName
,SUM(x.Case_Load) AS 'CL'
FROM (
SELECT a.LoginName
,count(c.caseno) AS 'CL'
FROM tblUser a
LEFT JOIN tblApp c ON (c.ManagerID = a.ID)
LEFT JOIN tblMoo m ON (m.SBCUID = a.LoginName)
WHERE CONVERT(NVARCHAR(30), dateadd(hh, - 4, m.CalDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND m.Avbl = 'IN'
AND a.manager = 1
AND a.inactivedate IS NULL
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
UNION ALL
SELECT c.LoginName
,count(DISTINCT a.caseno) AS 'Case_load'
FROM tblMoo m
LEFT JOIN tblUser c ON (m.SBCUID = c.LoginName)
LEFT JOIN tblApp a ON (a.ManagerID = c.ID)
LEFT JOIN AH b ON (b.AppealID = a.ID)
WHERE CONVERT(NVARCHAR(30), dateadd(hh, - 4, m.CalDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND m.Avbl = 'IN'
AND c.manager = 1
AND c.inactivedate IS NULL
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, a.ModifiedByDate), 103) IN (
SELECT TOP 1 CONVERT(NVARCHAR(30), dateadd(hh, - 4, b.DateAdded), 103)
FROM AH b
WHERE IsSystemGenerated = 1
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, b.DateAdded), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND b.HistoryText LIKE 'Assigned Manager was changed from%'
ORDER BY b.AppealHistoryID DESC
)
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, a.OpenedDate), 103) <> CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY c.LoginName
) x
GROUP BY x.LoginName
) s
RIGHT JOIN tblMoo m ON (m.SBCUID = s.LoginName)
LEFT JOIN tblUser c ON (c.LoginName = m.SBCUID)
WHERE m.Avbl = 'IN'
AND c.Manager = 1
AND c.InactiveDate IS NULL
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, m.CalDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND c.LoginName NOT IN (
'abc1234'
,'abc12345'
)
) cl
LEFT JOIN (
SELECT *
FROM (
SELECT *
FROM (
SELECT a.LoginName
,a.Region
,a.Region_Load
FROM (
/* Region Load for Today Starts */
SELECT a.LoginName
,b.Description AS 'Region'
,COUNT(b.Description) AS 'Region_Load'
,c.CaseNo
FROM tblUser a
LEFT JOIN tblAppeal c ON (c.ManagerID = a.ID)
LEFT JOIN Region b ON (c.RegionID = b.RegionID)
WHERE a.manager = 1
AND a.inactivedate IS NULL
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
,b.Description
,c.CaseNo
/* Region Load for Today Ends */
UNION ALL
SELECT a.LoginName
,b.Description AS 'Region'
,COUNT(DISTINCT b.Description) AS 'Region_Load'
,c.CaseNo
FROM tblUser a
LEFT JOIN tblApp c ON (c.ManagerID = a.ID)
LEFT JOIN AH d ON (d.AppealID = c.ID)
LEFT JOIN Region b ON (c.RegionID = b.RegionID)
WHERE CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.ModifiedByDate), 103) IN (
SELECT TOP 1 CONVERT(NVARCHAR(30), dateadd(hh, - 4, d.DateAdded), 103)
FROM AH d
WHERE d.IsSystemGenerated = 1
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, d.DateAdded), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND d.HistoryText LIKE 'Assigned Manager was changed from%'
ORDER BY d.AppealHistoryID DESC
)
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) <> CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
,b.Description
,c.CaseNo
) a
) AS RegionData
PIVOT(SUM([Region_Load]) FOR [Region] IN (
[SouthEast]
,[North]
,[Midwest]
,[Southwest]
,[West]
,[East]
)) AS Hello
) regn
) reg ON (reg.LoginName = cl.LoginName)
LEFT JOIN (
SELECT *
FROM (
SELECT a.LoginName
,count(c.SuggWrittenResp) AS 'OOS_Urgent'
FROM tblUser a
LEFT JOIN tblapp c ON (c.ManagerID = a.ID)
WHERE a.manager = 1
AND a.inactivedate IS NULL
AND c.SuggWrittenResp = 1
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
UNION ALL
SELECT a.LoginName
,count(DISTINCT c.SuggWrittenResp) AS 'OOS_Urgent'
FROM tblUser a
LEFT JOIN tblApp c ON (c.ManagerID = a.ID)
LEFT JOIN AH d ON (d.AppealID = c.ID)
WHERE c.SuggWrittenResp = 1
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.ModifiedByDate), 103) IN (
SELECT TOP 1 CONVERT(NVARCHAR(30), dateadd(hh, - 4, d.DateAdded), 103)
FROM AH d
WHERE d.IsSystemGenerated = 1
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, d.DateAdded), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND d.HistoryText LIKE 'Assigned Manager was changed from%'
ORDER BY d.AppealHistoryID DESC
)
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) <> CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
,c.SuggWrittenResp
) outofservice
) oos ON (oos.LoginName = cl.LoginName)
LEFT JOIN (
SELECT *
FROM (
SELECT *
FROM (
SELECT a.LoginName
,a.Complaint_type
,a.count_complaint
FROM (
SELECT a.LoginName
,d.Code AS 'Complaint_type'
,COUNT(c.typeid) AS 'count_complaint'
,c.CaseNo
FROM tblUser a
LEFT JOIN tblAppeal c ON (c.ManagerID = a.ID)
LEFT JOIN tblComplaintType d ON (d.id = c.typeid)
WHERE a.manager = 1
AND a.inactivedate IS NULL
AND d.inactivedate IS NULL
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
,d.code
,c.CaseNo
UNION ALL
SELECT a.LoginName
,e.Code AS 'Complaint_type'
,COUNT(DISTINCT e.code) AS 'count_complaint'
,c.CaseNo
FROM tblUser a
LEFT JOIN tblApp c ON (c.ManagerID = a.ID)
LEFT JOIN AH d ON (d.AppealID = c.ID)
LEFT JOIN tblCT e ON (e.ID = c.TypeID)
WHERE CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.ModifiedByDate), 103) IN (
SELECT TOP 1 CONVERT(NVARCHAR(30), dateadd(hh, - 4, d.DateAdded), 103)
FROM AH d
WHERE d.IsSystemGenerated = 1
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, d.DateAdded), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
AND d.HistoryText LIKE 'Assigned%Manager%changed%'
ORDER BY d.AppealHistoryID DESC
)
AND CONVERT(NVARCHAR(30), dateadd(hh, - 4, c.OpenedDate), 103) <> CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
GROUP BY a.LoginName
,e.Code
,c.CaseNo
) a
) AS complainttype
PIVOT(SUM([count_complaint]) FOR [Complaint_type] IN (
[1]
,[4]
,[3C]
,[9]
,[2]
,[7B]
,[5]
,[6]
,[7I]
,[2B]
,[2C]
,[3A]
,[3G]
,[7A]
,[Y]
,[Z]
,[7R]
,[V]
,[7V]
,[A]
,[5A]
,[YB]
,[YA]
,[2M]
,[SRC4]
,[CS]
,[7 AA]
,[2SM]
,[7VR]
,[CTec]
,[CTch]
,[1CEO]
)) AS complainttypespivot
) ctype
) ct ON (ct.LoginName = cl.LoginName)
You have a LOT of nonSARGable predicates in here. Pretty much any time you wrap a column in a function you negate any ability to use an index on that column and forces a scan.
Here is one of them.
WHERE CONVERT(NVARCHAR(30), dateadd(hh, - 4, m.CalDate), 103) = CONVERT(NVARCHAR(30), dateadd(hh, - 4, getutcdate()), 103)
In general this seems to be about a dozen times more complicated than it needs to be. Once we start seeing things like select * from (select * from ( select.....there is something funky going on.
SELECT *
FROM (
SELECT *
FROM (
Many of these queries seems to be nearly identical and could likely be combined with using UNION ALL all over the place.
I would start with the nonSARGable predicates and see where that gets you. Otherwise a complete rewrite may be in order.
_______________________________________________________________
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/
September 25, 2014 at 1:15 pm
Hi champion,
Thanks for replying,ya its popping a lot of times the where condiiton.
September 25, 2014 at 1:30 pm
Hi champion,
Do you prefer to use temporary tables
September 25, 2014 at 1:45 pm
mcfarlandparkway (9/25/2014)
Hi champion,Thanks for replying,ya its popping a lot of times the where condiiton.
Will you please give me the hint like what exactly i can do?
One time you can get away with a function around a column is casting a datetime to a date. SQL Server has some internal optimizations where this isn't a big deal. Your code was converting your column to a varchar which renders it hopeless.
Try something like this for all those predicates.
cast(m.CalDate as date) = cast(GETUTCDATE() as DATE)
_______________________________________________________________
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/
September 25, 2014 at 1:47 pm
mcfarlandparkway (9/25/2014)
Hi champion,Do you prefer to use temporary tables,like until union all will keep in one table..
Is there any issue with Like operator?and pivot?
You might be able to use a cte which could greatly reduce the number of times you need to hit the same base table. I think you could probably combine a number of those queries together as they are only slightly different in where clauses and a few other very minor differences.
PIVOT isn't too bad. You can squeeze a slight bit of performance increase by using cross tabs instead but for the most part the difference is minimal.
_______________________________________________________________
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/
September 25, 2014 at 2:07 pm
ffsdfdsf
September 25, 2014 at 2:10 pm
Thank you for helping me champion,I am very new to
?
September 25, 2014 at 2:16 pm
mcfarlandparkway (9/25/2014)
Thank you for helping me champion,I am very new to optimization techniques.Will you please format my view code into CTE please?
btw, my name is Sean. Just as your is not "Valued Member". 😀
CTE is not a format. It is a common table expression. It isn't going to help you much here unless you can combine all those queries into one. Rebuilding this query is way beyond the scope of an online forum. If a client asked me to estimate reworking that query it would be 8 hours minimum and likely be closer to 16 by the time testing and such is done. I am happy to offer some advice but am not interested in spending a day or two rewriting your query for you.
_______________________________________________________________
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/
September 26, 2014 at 6:36 am
Sean Lange (9/25/2014)
btw, my name is Sean. Just as your is not "Valued Member". 😀
Ha ha! Well said Champion. 🙂
Regards
Crazy
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply