SQL Server procedure optimisation

Question

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

                                                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

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

END

Answer 1

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:

• Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

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.