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]
/****** Object:  StoredProcedure [test_001]    Script Date: 09/22/2014 15:44:54 ******/

@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'


-- 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
(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
                                            (   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

                                                from    dbo.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


                                    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
                                          (@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 @tab              EXECUTE  sp_executesql @command, @parameters, @RequestCode, @ARC,  @vitastor, @dateFrom, @dateTo, @Svice, @Distributor, @Seller, @Flow
  select * from @tab

  • 1
    What is it supposed to do? – Mark Sinkinson Sep 23 '14 at 9:44
  • Just retrieving data – Ashvin Sungkur Sep 23 '14 at 10:37
  • 3
    Your question is very, very broad. In essence, you're asking "How does one optimize a stored procedure?". You've written nothing about the tables involved, indexes, how much data is in them, what the stored procedure is supposed to achieve, or any other factors that will affect performance tuning decisions. Start out by trying the different approaches you've written in your question and try to narrow down your question to a specific subject. – Daniel Hutmacher Sep 23 '14 at 12:15
  • The other comments are good responses. I would add to examine the indexes. Also, make sure statistics are created "sp_createstats 'indexonly'" and "sp_updatestats" (in my experience these aren't run on a trustworthy internal schedule or algorithm). Check the index fragmentation and FillFactor and adjust accordingly. I would go into more detail, but it's a tough query, like Aaron mentions. Detrimental things are: dynamic sql, OR, left join, union, LIKE '%'. Breaking down query into smaller procs with better definition are some ideas. – SnapJag Oct 8 '15 at 19:49

This looks like a "kitchen sink" stored procedure - meaning let the user search for any or all parameters. Impossible for SQL Server to generate a single, effective plan that will work well for any combination of parameters supplied (never mind their values). My first two approaches at optimization are always:

  1. Add OPTION (RECOMPILE) to the query - you pay compilation cost every time, but you get the right plan for the parameters supplied.
  2. Use dynamic SQL (different plan for each combination of parameters; turn on optimize for ad hoc workloads).

More information in a recent answer here:

Some of the other tips there apply (for example, not using NOLOCK as a magic turbo button), and also, do you really need multiple levels of duplicate elimination (e.g. DISTINCT and UNION)? Do any of these individual queries really have the potential to produce duplicates? These introduce very expensive operations that are just wasteful if duplicates aren't possible anyway... so use UNION ALL instead of UNION and drop the DISTINCT. If you have duplicates in the output then deal with those, there is probably a better fix than making SQL Server find and remove duplicates at runtime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.