December 5, 2017 at 4:31 am
Hi Experts,
Need some suggestions on below sp to improve performance of the sp. Currently taking 10 -15 secs to execute.Trying to see if we can reduce the overall execution time.
I have no much info about what does the stored proc does. Written by some consultant. When we run against more users, getting a performance hit. So wanted to reduce
the execution time of sp. Its completely a dynamic sp.
select @@version
--Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 17 2017 14:42:09 Copyright (C) 2017 Microsoft Corporation
Attached zip file contains all details.
1. sp call.sql // contains the step to execute the sp
2. sp source code.sql // source code of the sp
3. ssms_actual_plan.sqlplan // actual execution plan generated from SQL Server Management Studio
4. tmpA554.pesession // actual execution plan opened in plan explorer. i.e. in ssms -> right click the plan -> View with SQL Sentry Plan Explorer
Seeking for any help on speeding up the stored proc.
Observations in plan explorer
==========================
- stored proc retuns 2 results sets
result set1: returns 1 row
result set2: returns 50 rows
- no missing recommendations
- Friom the Query Columns tab, there is skew between Actual no of rows vs Est no of rows
-- from Plan Diagram tab
I see a lot of thick lines.
I see a high cost operators marked in RED
I see a DISTINCT operator .. which is forcing a SORT operation.
-- From Top operations tab
I see Sort as the top
( see the estimated and Actual no of rows are way off) not sure if we update the stats with Full scan would help.
I tried to update the stats with FULLSCAN but it didnt help!
Thanks,
Sam
December 5, 2017 at 8:54 pm
Any suggestions please?
December 15, 2017 at 9:29 am
Can you run the following two commands in SSMS and then run the proc from the same session. After you execute, copy and paste the output that displays in the Messages tab.
SET STATISTICS IO ON
SET STATISTICS TIME ON
December 17, 2017 at 11:13 pm
Whoever wrote this needs to give back whatever money they were paid... This contains some truly unforgivable sins.
Looking at the top 1/2 of the code, my first thought was switch from @table_variables to #temp_tables so that sql server will build statics on them and convert to parameterized dynamic sql to eliminate (or at least reduce) the convoluted OR logic.
When I got to the lower 1/2 of the code and saw that the moron IS using dynamic sql but not doing anything that you'd actually use dynamic sql for... That's when the eye twitch set in.
That said, my suggestions are still the same... 1) use #temp_tables rather than @table_variables... including dumping the table valued parameters into temp tables. 2) use dynamic sql to generate SARGable queries.
Just as an example...
this whole section
uca.ComponentID=samp.ComponentID AND uca.UserID=@UserId
WHERE uca.IsReportAssociation=1
AND samp.SampleStatusID=1
AND (@IsAdmin=1 OR samp.ComponentID IN (SELECT Id FROM @UserComponents))
AND ( (@DurationId IS NULL OR @DurationId = 0) OR
(
(@DurationId IS NOT NULL AND @DurationId <> 0 )
AND ( @DurationId = 1 AND CONVERT(date, samp.DateDrawn) BETWEEN @FromDate AND CONVERT(date,GETUTCDATE()))
OR (@DurationId = 2 AND CONVERT(date, samp.DateDrawn) BETWEEN CONVERT(date,DATEADD(d,-7,GETUTCDATE())) AND CONVERT(date,GETUTCDATE()))
OR (@DurationId = 3 AND CONVERT(date, samp.DateDrawn) BETWEEN CONVERT(date,DATEADD(d,-14,GETUTCDATE())) AND CONVERT(date,GETUTCDATE()))
OR (@DurationId = 4 AND CONVERT(date, samp.DateDrawn) BETWEEN CONVERT(date,DATEADD(m,-1,GETUTCDATE())) AND CONVERT(date,GETUTCDATE()))
OR (@DurationId = 6 AND CONVERT(date, samp.DateDrawn) BETWEEN @FromDate AND @ToDate)
)
)
AND (@KeyWord IS NULL OR
(
@KeyWord IS NOT NULL
AND
(
lub.LubricantCode LIKE '%'+@KeyWord+'%'
OR samp.SampleNumber LIKE '%'+@KeyWord+'%'
OR samp.LANumber LIKE '%'+@KeyWord+'%'
OR comp.ComponentName LIKE '%'+@KeyWord+'%'
OR equip.EquipmentName LIKE '%'+@KeyWord+'%'
OR comp.LANumber LIKE '%'+@KeyWord+'%'
--OR comp.RLACode LIKE '%'+@KeyWord+'%'
)
)
)
AND (@CustomerId IS NULL OR
(
@CustomerId IS NOT NULL
AND (
sites.CustomerID = @CustomerId
OR vessel.CustomerID = @CustomerId
)
)
)
AND (@DepartmentId IS NULL OR
(
@DepartmentId IS NOT NULL
AND
(
(@DepartmentId <> 0 AND dept.DepartmentID = @DepartmentId)
OR(@DepartmentId = 0 AND dept.DepartmentID IS NULL)
)
)
)
AND (@SiteOrVesselId IS NULL OR
(
@SiteOrVesselId IS NOT NULL
AND
(
(@SiteOrVessel = 2 AND sites.SiteID = @SiteOrVesselId)
OR (@SiteOrVessel = 3 AND vessel.VesselID = @SiteOrVesselId)
)
)
)
AND(samp.DateDrawn BETWEEN CONVERT(date,DATEADD(d,-@OverFlowPeriod,GETUTCDATE())) AND CONVERT(date,GETUTCDATE())) OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
Can be rewritten, using parameterized dynamic sql, like this...DECLARE @_sql NVARCHAR(MAX) = CONCAT(N'
WHERE
uca.IsReportAssociation = 1
AND samp.SampleStatusID = 1
AND samp.DateDrawn >= DATEADD(DAY, - @OverFlowPeriod, GETUTCDATE())
AND samp.DateDrawn < DATEADD(DAY, 1, CONVERT(DATE, GETUTCDATE())
AND (
@IsAdmin = 1
OR
EXISTS (SELECT 1 FROM #UserComponents uc WHERE samp.ComponentID = uc.Id ) -- dump the TVP into a #temp_table.
)',
CASE
WHEN @DurationId IS NULL OR @DurationId = 0 THEN N''
WHEN @DurationId = 1 THEN N'
AND samp.DateDrawn >= @FromDate
AND samp.DateDrawn < DATEADD(DAY, 1, CONVERT(DATE, GETUTCDATE()))'
WHEN @DurationId = 2 THEN N'
AND samp.DateDrawn >= CONVERT(DATE, DATEADD(d, -7, GETUTCDATE()))
AND samp.DateDrawn < DATEADD(DAY, 1, CONVERT(DATE, GETUTCDATE()))'
WHEN @DurationId = 3 THEN N'
AND samp.DateDrawn >= CONVERT(DATE, DATEADD(d, -14, GETUTCDATE()))
AND samp.DateDrawn < DATEADD(DAY, 1, CONVERT(DATE, GETUTCDATE()))'
WHEN @DurationId = 4 THEN N'
AND samp.DateDrawn >= CONVERT(DATE, DATEADD(m, -1, GETUTCDATE()))
AND samp.DateDrawn < DATEADD(DAY, 1, CONVERT(DATE, GETUTCDATE()))'
WHEN @DurationId = 6 THEN N'
AND samp.DateDrawn >= @FromDate
AND samp.DateDrawn < DATEADD(DAY, 1, @ToDate)'
ELSE N'
AND 1 = 2' -- short circuits the query when an illegal value is provided.
END,
CASE WHEN @KeyWord IS NULL THEN N'' ELSE N'
AND (
lub.LubricantCode LIKE ''%'' + @KeyWord + ''%'' -- if possible, lose the leading wildcards...
OR samp.SampleNumber LIKE ''%'' + @KeyWord + ''%''
OR samp.LANumber LIKE ''%'' + @KeyWord + ''%''
OR comp.ComponentName LIKE ''%'' + @KeyWord + ''%''
OR equip.EquipmentName LIKE ''%'' + @KeyWord + ''%''
OR comp.LANumber LIKE ''%'' + @KeyWord + ''%''
--OR comp.RLACode LIKE ''%''+@KeyWord+''%''
)',
CASE WHEN @CustomerId IS NULL THEN N'
AND @CustomerId IN (sites.CustomerID, vessel.CustomerID)'
END,
CASE WHEN @DepartmentId IS NULL THEN N'' ELSE N'
AND (
dept.DepartmentID = @DepartmentId
OR
(
@DepartmentId = 0
AND dept.DepartmentID IS NULL
)
)'
CASE
WHEN @SiteOrVesselId IS NULL THEN N''
WHEN @SiteOrVessel = 2 THEN N'
AND sites.SiteID = @SiteOrVesselId'
WHEN @SiteOrVessel = 3 THEN N'
AND vessel.VesselID = @SiteOrVesselId'
ELSE N'
AND 1 = 2' -- short circuits the query when an illegal value is provided.
END);
Now the entire search predicate, except for the @Keyword section, is SARGable.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply