optimizing TSQL stored procedure

Question

I am trying to tweak the stored procedure below as its called 30k times a minute on our website.

CREATE PROCEDURE [dbo].[mltHttpCallStatus] 

    @SupplierId     AS INTEGER,
    @CallIsGood     AS BIT,
    @MaxWorkerThreads   AS INT,
    @MaxIOThreads       AS INT,
    @AvailWorkerThreads AS INT,
    @AvailIOThreads     AS INT,
    @ScriptTypeId       AS INT,
    @SiteTypeId         AS VARCHAR(50),
    @ConnectionTime     AS INT,
    @SiteName       AS VARCHAR(50),
    @HostName       AS VARCHAR(50)

AS

    --DEBUG BEN (Flight details keep failing) 07012008 19:30
    --Return
SET NOCOUNT ON

DECLARE @GoodCalls      AS INT,
    @BadCalls       AS INT

SET @BadCalls = 0
SET @GoodCalls = 0

IF @CallIsGood = 1
    SET @GoodCalls = 1

ELSE
    SET @BadCalls = 1

    UPDATE  HttpCallStatus_tbl SET
        GoodCalls       = GoodCalls + @GoodCalls,
        BadCalls        = BadCalls + @BadCalls,
        TotalConnectionTime = TotalConnectionTime + @ConnectionTime
     --WHERE dbo.datepart_fn(DayDate) = dbo.datepart_fn(getDate())
    WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, DayDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
        AND DATEPART(HOUR, DayDate) = DATEPART(HOUR, getDate()) 
        AND SupplierId   = @SupplierId
        AND ScriptTypeId = @ScriptTypeId
        AND SiteTypeId = @SiteTypeId
        AND SiteName = @SiteName
        AND HostName = @HostName

    IF @@ROWCOUNT = 0

    BEGIN
        INSERT INTO HttpCallStatus_tbl (DayDate,SupplierId,GoodCalls,BadCalls,ScriptTypeId,SiteTypeId,TotalConnectionTime, 
                        MaxWorkerThreads,MaxIOThreads,AvailWorkerThreads,AvailIOThreads,SiteName,HostName)
             VALUES (CONVERT(DATETIME, getDate(), 103),
                @SupplierId,
                @GoodCalls,
                @BadCalls,
                @ScriptTypeId,
                @SiteTypeId,
                @ConnectionTime,
                0,
                0,
                0,
                0,
                @SiteName,
                @HostName)
    END

table structure

    Column_name         Type        Length
    DayDate             datetime    8
    SupplierId          int     4
    GoodCalls           int     4
    BadCalls            int     4
    ScriptTypeId                int     4
    SiteTypeId          varchar     50
    TotalConnectionTime         int     4
    MaxWorkerThreads            int     4
    MaxIOThreads                int     4
    AvailWorkerThreads          int     4
    AvailIOThreads              int     4
    SiteName            varchar     50
    HostName            varchar     50
    SearchCount         int     4
    DomainId            int     4

Indexes

[PK_HttpCallStatus_tbl] clustered, unique, primary key [DayDate],[SupplierId]

[IX_HttpCallStatus_tbl] nonclustered [SupplierId]

[idx_HttpCallStatus_tbl_1]  nonclustered    
[SupplierId], [ScriptTypeId], [SiteTypeId], [SiteName], [HostName]
 , [DayDate], [GoodCalls], [BadCalls], [TotalConnectionTime]

I noticed these variables are unused @MaxWorkerThreads, @MaxIOThreads, @AvailWorkerThreads, @AvailIOThreads. also could make the variables @goodcalls & @Badcalls TINYINTS. I also noticed this conversion in the insert statement VALUES (CONVERT(DATETIME, getDate(), 103). but I think this is the default so could change to Getdate()

my issues is its hard to measure the improvements as they are both so quick, is it worth me making these changes, will I even see a small gain ?

Answer 1

Try this:

CREATE PROCEDURE [dbo].[mltHttpCallStatus] 

    @SupplierId     AS INTEGER,
    @CallIsGood     AS BIT,
    @MaxWorkerThreads   AS INT,
    @MaxIOThreads       AS INT,
    @AvailWorkerThreads AS INT,
    @AvailIOThreads     AS INT,
    @ScriptTypeId       AS INT,
    @SiteTypeId         AS VARCHAR(50),
    @ConnectionTime     AS INT,
    @SiteName       AS VARCHAR(50),
    @HostName       AS VARCHAR(50)

AS

    --DEBUG BEN (Flight details keep failing) 07012008 19:30
    --Return
SET NOCOUNT ON

DECLARE @GoodCalls      AS INT,
        @BadCalls       AS INT,
        @StartHour DateTime,
        @EndHour   DateTime

Select  @StartHour = DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()), 0),
        @EndHour   = DATEADD(Hour, 1 + DATEDIFF(Hour, 0, GETDATE()), 0),
        @BadCalls = 0,
        @GoodCalls = 0

IF @CallIsGood = 1
    SET @GoodCalls = 1

ELSE
    SET @BadCalls = 1

    UPDATE  HttpCallStatus_tbl SET
        GoodCalls       = GoodCalls + @GoodCalls,
        BadCalls        = BadCalls + @BadCalls,
        TotalConnectionTime = TotalConnectionTime + @ConnectionTime
     --WHERE dbo.datepart_fn(DayDate) = dbo.datepart_fn(getDate())
    WHERE DayDate >= @StartHour
        And DayDate < @EndHour
        AND SupplierId   = @SupplierId
        AND ScriptTypeId = @ScriptTypeId
        AND SiteTypeId = @SiteTypeId
        AND SiteName = @SiteName
        AND HostName = @HostName

    IF @@ROWCOUNT = 0

    BEGIN
        INSERT INTO HttpCallStatus_tbl (DayDate,SupplierId,GoodCalls,BadCalls,ScriptTypeId,SiteTypeId,TotalConnectionTime, 
                        MaxWorkerThreads,MaxIOThreads,AvailWorkerThreads,AvailIOThreads,SiteName,HostName)
             VALUES (CONVERT(DATETIME, getDate(), 103),
                @SupplierId,
                @GoodCalls,
                @BadCalls,
                @ScriptTypeId,
                @SiteTypeId,
                @ConnectionTime,
                0,
                0,
                0,
                0,
                @SiteName,
                @HostName)
    END

Notice that I calculate the start hour and end hour prior to the query, then I use that as the range of dates to search for. Since your primary key's first column is DayDate, this should make a big improvement in performance.

Answer 2

I think you're optimising in the wrong place. You're worrying about a conversion for one row on an insert, while ignoring the conversion/function in the where clause that precedes it. It looks like you've moved the logic from a scalar function back to the where clause, which will help, but I'd focus on changing this:

WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, DayDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
        AND DATEPART(HOUR, DayDate) = DATEPART(HOUR, getDate()) 

Into something that will work with indexes. Take a look at sargability http://en.wikipedia.org/wiki/Sargable and work out a way of avoiding this with your data. That'll be where the real gains are.

Also, still in the where clause, just check all your other conditions have the same data type, to prevent implicit conversions.

Answer 3

Couple of things:

The following converts GETDATE() to 'dd/mm/yyyy' (no time information) for all inserts

CONVERT(DATETIME, getDate(), 103)

Therefore, the datatype can be changed to DATE instead of DATETIME. With that change, the following items can be changes/removed:

CONVERT(DATETIME, getDate(), 103) 

Can change to:

CAST(GETDATE() AS DATE)

This can be removed all together, as the time portion is not being saved:

AND DATEPART(HOUR, DayDate) = DATEPART(HOUR, getDate()) 

This

DATEADD(dd, 0, DATEDIFF(dd, 0, DayDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Can be changed to:

DayDate = CAST(GETDATE() AS DATE)

And because you're using SQL server 2008+, you can use MERGE instead of UPDATE/INSERT logic

http://technet.microsoft.com/en-us/library/bb510625.aspx

Answer 4

1. Drop index [IX_HttpCallStatus_tbl] nonclustered [SupplierId]. It is covered by [idx_HttpCallStatus_tbl_1].
2. Change index [idx_HttpCallStatus_tbl_1] nonclustered [SupplierId], [ScriptTypeId], [SiteTypeId], [SiteName], [HostName], [DayDate], [GoodCalls], [BadCalls], [TotalConnectionTime] to an index on [SupplierId], [ScriptTypeId], [SiteTypeId], [SiteName], [HostName], [DayDate] and include columns [GoodCalls], [BadCalls], [TotalConnectionTime]

As far as the sproc goes, I would add this above the update:

DECLARE @MinDate DATETIME
DECLARE @MaxDate DATETIME
SET @MinDate=DATEADD(HOUR,DATEPART(HOUR, GETDATE()),CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101)))
SET @MaxDate=DATEADD(HOUR,1,@MinDate)

and change the first two lines of the where to be

WHERE  DayDate>=MinDate AND DayDate<MaxDate