Optimise procedure sql server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Optimise procedure sql server 2008

Author

Topic

Ashvin
Starting Member

3 Posts

Posted - 2014-09-24 : 05:00:50

Hi Experts,

I need to optimize the procedure below (SQL Server 2008 R2) 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

Thanks and Regards,
Ashvin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-24 : 07:25:57

Read http://c2.com/cgi/wiki?PrematureOptimization and then benchmark your query. If it is not executed frequently or is not impacting operations, leave it as is.

Ashvin
Starting Member

3 Posts

Posted - 2014-09-24 : 08:09:04

Hi,

The link you sent is not helping...
This query is ran frequently.
Any idea how to optimize it?

Thanks and Regards,
Ashvin

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-24 : 11:02:27

First, use dynamic SQL to remove unused parameter values, like below, since they only "confuse" the optimizer.

Second, create a covering, nonclustered index on "dbo.hubgas_Svices" ( IDSvice ) INCLUDE ( Type )

If the query still executes poorly, we'll need to look further at indexes/indexing.



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 ' + 
    CASE WHEN @ARC IS NULL THEN '1 = 1 ' ELSE '(@ARC = ef.ARC) ' END + 'AND ' +
    CASE WHEN @RequestCode IS NULL THEN '1 = 1 ' ELSE '(@RequestCode = ef.caseNumber) ' END +
    '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 ' + CASE WHEN @Seller IS NULL THEN '1 = 1 ' ELSE '(@Seller = av.vitatbl) ' END + ' 
)
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 ' + CASE WHEN @ARC IS NULL THEN '1 = 1 ' ELSE '(rf.FileID = ''-1'') ' END + '
AND ' + CASE WHEN @RequestCode IS NULL THEN '1 = 1 ' ELSE '(rf.FileID = ''-1'') ' END + '
AND (rf.richturc is null OR (rf.richturc=''GAS'')) 
AND ' + CASE WHEN @Seller IS NULL THEN '1 = 1 ' ELSE '(@Seller = av.vitatbl) ' END + '
) res
where ' +
CASE WHEN @Distributor IS NULL THEN '1 = 1 ' ELSE '(@Distributor = res.Vitadst) ' END + '
AND ' + CASE WHEN @vitastor IS NULL THEN '1 = 1 ' ELSE '(@vitastor = vitastor) ' END + '
AND ' + CASE WHEN @Svice is null THEN '1 = 1 ' ELSE '(@Svice = vitaserv) ' END + '
AND (SviceType is null OR SviceType like ''CUU%'')
AND ' + CASE WHEN @Flow IS NULL THEN '1 = 1 ' ELSE '(@Flow = Flusso) ' END + '
AND ' + CASE WHEN @dateFrom IS NULL THEN '1 = 1 ' ELSE '(Data >= @dateFrom) ' END + '
AND ' + CASE WHEN @dateTo IS NULL THEN '1 = 1 ' ELSE '(Data <= @dateTo) ' END + '
) rows
WHERE RowNum BETWEEN ' + @lowerBound + ' AND ' + @upperBound

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-24 : 15:47:33

quote:
Originally posted by Ashvin

Hi,

The link you sent is not helping...
This query is ran frequently.
Any idea how to optimize it?

Thanks and Regards,
Ashvin

Why do you want to optimize it? Have you timed it? If so, how far away from the desired time is it? e.g. if only 10% away, you might not want to bother trying. If 50% away, is the target time reasonable?

What does the (actual) execution plan show? Where is most of the activity? Concentrate on that part. Add missing indices.

Oh, and don't use NOLOCK. Try READPAST if you must.

Ashvin
Starting Member

3 Posts

Posted - 2014-09-25 : 06:26:02

Hi Scott,

Thank you for your answer, can you please explain in more details:
Create a covering, nonclustered index on "dbo.hubgas_Svices" ( IDSvice ) INCLUDE ( Type )

Thanks and Regards,
Ashvin

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-25 : 10:24:04

CREATE NONCLUSTERED INDEX hubgas_Svices__IX_IDSvice ON dbo.hubgas_Svices ( IDSvice ) INCLUDE ( Type ) WITH ( FILLFACTOR = 98 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources