September 23, 2014 at 3:15 am
Hi Experts,
I need to optimize the procedure below (SQL Server 2008) and badly need help:
I am thinking to reduce search space
o Splitting the joins and create intermediate (smaller) results
o Force the most selective joins(and where clauses) first
How to do that?
USE [Astalao]
GO
/****** Object: StoredProcedure [test_001] Script Date: 09/22/2014 15:44:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [test_001]
@Distributor nvarchar(255)=null,
@Seller nvarchar(255)=null,
@vitastor nvarchar(255)=null,
@Svice nvarchar(255)=null,
@Flow nvarchar(255)=null,
@dateFrom nvarchar(255)=null,
@dateTo nvarchar(255)=null,
@ARC nvarchar(255)=null,
@RequestCode nvarchar(255)=null,
@lowerBound nvarchar(255)=null,
@upperBound nvarchar(255)=null,
@sortExpression nvarchar(255)=N'Data Desc'
AS
BEGIN
SET NOCOUNT ON;
-- Declare @parameters and @command variables
DECLARE @command nvarchar(MAX)
DECLARE @parameters nvarchar(MAX)
-- Set @parameters variable
SET @parameters = N'@RequestCode nvarchar(255), @ARC nvarchar(255), @vitastor nvarchar(255), @dateFrom Datetime, @dateTo Datetime, @Svice nvarchar(255), @Distributor nvarchar(255), @Seller nvarchar(255), @Flow nvarchar(255)'
-- Set @command variable
SET @command = N'SELECT * FROM
(select *,
ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNum
from
(select r.FileID,r.FilePath,r.Vitadst,r.Seller,r.vitaserv,r.Flusso,r.FlagErr,r.DescErr,r.Data,r.vitastor,max(e.FlagErr) as ErroreOutcome,count(*)as NumeroEsiti, max(r.Type) as SviceType
from
(select distinct rf.FileID,rf.FilePath
,rf.Vitadst,av.Descrizione as Seller
,rf.vitaserv,rf.Flusso,rf.FlagErr,rf.DescErr,rf.Data,rf.vitastor, S.Type
fromdbo.colmerich rf WITH (nolock)
JOIN dbo.fildrich ef WITH (nolock)
on (rf.FileID = ef.vitafilID) AND (@ARC is null OR @ARC = ef.ARC) AND (@RequestCode is null OR @RequestCode = ef.caseNumber) AND (rf.richturc is null OR (rf.richturc=''GAS''))
left join dbo.hubgas_Svices S on (rf.vitaserv collate SQL_Latin1_General_CP1_CI_AS LIKE ''%'' + S.IDSvice + ''%'')
left join AnagraficaVenditori av with(nolock) on (rf.vitasell = av.vitatbl)
where (@Seller is null OR @Seller = av.vitatbl)
)
r join
dbo.fildrich e WITH (nolock) on (r.FileID = e.vitafilID)
group by r.FileID,r.FilePath,r.Vitadst,r.Seller,r.vitaserv,r.Flusso,r.FlagErr,r.DescErr,r.Data,r.vitastor
UNION
select rf.FileID, rf.FilePath,rf.Vitadst,av.Descrizione as Seller,rf.vitaserv,rf.Flusso,rf.FlagErr,rf.DescErr,rf.Data,rf.vitastor,NULL as ErroreOutcome,0 as NumeroEsiti, s.Type as SviceType
from dbo.colmerich rf WITH (nolock)
left join dbo.hubgas_Svices S WITH (nolock)
on (rf.vitaserv collate SQL_Latin1_General_CP1_CI_AS LIKE ''%'' + S.IDSvice + ''%'')
left join AnagraficaVenditori av with(nolock) on (rf.vitasell = av.vitatbl)
where vitastor != 1
AND Visionato = 0
AND (@ARC is null OR rf.FileID = ''-1'')
AND (@RequestCode is null OR rf.FileID = ''-1'')
AND (rf.richturc is null OR (rf.richturc=''GAS''))
AND (@Seller is null OR @Seller = av.vitatbl)
) res
where
(@Distributor is null OR @Distributor = res.Vitadst)
AND (@vitastor is null OR @vitastor = vitastor)
AND (@Svice is null OR @Svice = vitaserv)
AND (SviceType is null OR SviceType like ''CUU%'')
AND (@Flow is null OR @Flow = Flusso)
AND (@dateFrom is null OR (Data >= @dateFrom))
AND (@dateTo is null OR (Data <= @dateTo))
) rows
WHERE RowNum BETWEEN ' + @lowerBound + ' AND ' + @upperBound
DECLARE @tab AS TABLE (FileID NVARCHAR(255) null, FilePath NVARCHAR(255) null ,Vitadst NVARCHAR(255), Seller NVARCHAR(255),vitaserv NVARCHAR(255) null , Flusso NVARCHAR(255) null, FlagErr NVARCHAR(255),DescErr NVARCHAR(255) null, Data Datetime null , vitastor NVARCHAR(255) null, ErroreOutcome NVARCHAR(255) null , NumeroEsiti int, SviceType NVARCHAR(255) null, RowNum int )
INSERT into @tabEXECUTE sp_executesql @command, @parameters, @RequestCode, @ARC, @vitastor, @dateFrom, @dateTo, @Svice, @Distributor, @Seller, @Flow
select * from @tab
END
September 23, 2014 at 4:01 am
Neither.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
And why the nolock hint? Is accurate results not required from this query?
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
September 23, 2014 at 5:06 am
Hi GilaMonster,
Thanks a lot for the tips.
I do not know why nolock hint has been used. Accurate result? What alternative do you propose?
I have just been given this query to optimise 🙁
Can you think of anymore optimisation that can be done to this procedure?
September 23, 2014 at 6:00 am
ashvindevil (9/23/2014)
I do not know why nolock hint has been used. Accurate result? What alternative do you propose?
Not using nolock? It's not a go-faster switch. It's a trade off of not being blocked for possible inaccurate results.
Can you think of anymore optimisation that can be done to this procedure?
Did you read the blog post I referenced?
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
September 23, 2014 at 7:19 am
I am not able to implement the dynamic search on my code, can you please help.
September 23, 2014 at 7:24 am
You're already using dynamic SQL in your code. How can you say you're not allowed to do something you're already doing?
If you're using SQL 2008, the way you optimise this kind of query is by using dynamic SQL, as shown in that blog post. If you're using SQL 2008 R2 or above, you can just use the recompile hint. Since you're on SQL 2008 and already using dynamic SQL, the dynamic SQL option is probably the one you want to go for.
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
September 23, 2014 at 7:32 am
Here are a few links which discuss what that NOLOCK hint is really doing.
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]
_______________________________________________________________
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 23, 2014 at 8:03 am
Hi Sean,
Thanks for your reply.
Is there anything that can be changed apart from no lock?
Regards,
Ashvin
September 23, 2014 at 8:12 am
ashvindevil (9/23/2014)
Hi Sean,Thanks for your reply.
Is there anything that can be changed apart from no lock?
Regards,
Ashvin
Yes. Change your dynamic sql so it follow the pattern outlined in Gail's article.
_______________________________________________________________
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 24, 2014 at 2:55 am
Sean,
I am very new to SQL server and do not know how to proceed.
Can you please help me to accomplish the same?
Thanks and Regards,
Ashvin
September 24, 2014 at 6:36 am
Go look at the blog post again. Play with the example listed there if you need to. It's really not hard and it's explained in that blog post.
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
September 24, 2014 at 7:27 am
It would be a lot easier for you if you formatted your code. I really didn't look at your code but the main select statement is 4 nested selects. You should be able that down to a single select statement. That would make this a lot less complicated to see what is going on. First and foremost make it easier on yourself by cleaning this up. Then work on converting it to the type of query Gail has in her blog post.
Pretty sure the first query in your UNION could be reduced to something like this.
SELECT rf.FileID
, rf.FilePath
, rf.Vitadst
, av.Descrizione AS Seller
, rf.vitaserv
, rf.Flusso
, Max(rf.FlagErr) AS ErroreOutcome
, Count(*) AS NumeroEsiti
, rf.DescErr
, rf.Data
, rf.vitastor
, S.Type AS SviceType
, Row_number() OVER (ORDER BY sortExpression ) AS RowNum
FROM dbo.colmerich rf
JOIN dbo.fildrich ef ON rf.FileID = ef.vitafilID
AND (@ARC IS NULL OR @ARC = ef.ARC)
AND (@RequestCode IS NULL OR @RequestCode = ef.caseNumber)
AND (rf.richturc IS NULL OR rf.richturc = 'GAS')
LEFT JOIN dbo.hubgas_Svices S ON rf.vitaserv COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%' + S.IDSvice + '%'
LEFT JOIN AnagraficaVenditori av ON rf.vitasell = av.vitatbl
JOIN dbo.fildrich e ON rf.FileID = ef.vitafilID
WHERE ( @Seller IS NULL
OR @Seller = av.vitatbl )
GROUP BY rf.FileID
, rf.FilePath
, rf.Vitadst
, rf.Seller
, rf.vitaserv
, rf.Flusso
, rf.FlagErr
, rf.DescErr
, rf.Data
, rf.vitastor
_______________________________________________________________
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply