CODEWITHSUNDEEP
sqlsql-serverindexing
gbro3n
gbro3n

Reputation: 6967

SQL Server Query Optimisation - What is the optimal index here?

I have an email dispatch table that needs to hold in excess of 4 million rows. A particular report also needs to reference this table to produce some stats, and it's taking longer than I would like to run (Currently around 30 seconds).

I have checked the estimated execution plan, which shows that 94% of the cost is incurred by a single predicate (If I am interpreting this correctly).

Note the following example shows small snippets to keep it brief. Other indexes are in place targeted at other queries.

enter image description here

Predicate:

[EmsDb].[dbo].[MailDispatchPending].[MailCampaignId]=[@MailCampaignId] OR [@MailCampaignId] IS NULL

This I believe is pointing to the following SQL:

WHERE @MailCampaignId IS NULL OR MailCampaignId = @MailCampaignId -- Restrict to mail campaign where parameter passed

I have attempted to improve performance by testing the following indexes. Neither influence the execution plan output, or improve query speed.

/****** Object:  Index [IX_MailCampaignId]    Script Date: 11/27/2013 11:21:00 ******/
CREATE NONCLUSTERED INDEX [IX_MailCampaignId] ON [dbo].[MailDispatchPending] 
(
    [MailCampaignId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_MailCampaignId]    Script Date: 11/27/2013 11:21:00 ******/
CREATE NONCLUSTERED INDEX [IX_MailCampaignId] ON [dbo].[MailDispatchPending] 
(
    [Id] ASC,
    [MailCampaignId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Can anyone suggest a better type of index or different strategy to help me improve performance here?

Upvotes: 1

Views: 59

Answers (3)

bjnr
bjnr

Reputation: 3437

Could be this an alternative solution to that proposed by @GarethD?

WHERE MailCampaignId = COALESCE(@MailCampaignId,MailCampaignId)

Upvotes: 0

GarethD
GarethD

Reputation: 69759

Don't use this approach:

WHERE @MailCampaignId IS NULL OR MailCampaignId = @MailCampaignId

Instead use:

IF @MailCampaignId IS NULL
    BEGIN
        SELECT  ..
        FROM    ...
    END
ELSE
    BEGIN
        SELECT  ..
        FROM    ...
        WHERE   MailCampaignId = @MailCampaignId
    END

It might feel like more work, but SQL-Server uses cached execution plans, and unless you force the recompile it will use the same execution plan whether the parameter is null or not. If you use the above approach you guarantee the correct plan is used depending on whether or not the parameter is null

Upvotes: 3

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

The IS NULL OR part is what's messing with your performance, because if the input is NULL you're asking for every row which is the scan.

I'd remove that part if possible and see if that doesn't help, and if not possible, then I'd rethink on when I'd need all of them and consider an alternate approach for example multiple queries instead of one or even storage changes.

Upvotes: 2

Related Questions