CODEWITHSUNDEEP
sql-server-2012
Ajay
Ajay

Reputation: 6590

SQL Server select query taking long time to execute with WHERE clause

I have one table Table1 which contains 1 - 1.5 million of rows with a primary key.

This is my table structure 

CREATE TABLE [dbo].[Table1]
(
    [Id] [INT] NOT NULL,    
    [allow_public] [BIT] NULL,
    [average_frequency] [DECIMAL](18, 4) NULL,
    [category_id] [INT] NULL,
    -- a few more columns
    --
    --
    --
    [member_id] [INT] NULL

    CONSTRAINT [PK_Table1_Id] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
)

Added a nonclustered index on member_id like this:

CREATE NONCLUSTERED INDEX non_cluster_index_member_table1
    ON dbo.Table1 (member_id) 
    WHERE member_id IS NOT NULL;
    GO

When I execute query like 

SELECT
    [id], 
    [accurate_grams],
    [allow_public],
    [average_frequency],
    [category_id]
FROM
    dbo.Table1 
WHERE 
    member_id = @memberid;

it returns some rows within milliseconds. But when I execute a query like

SET @OrgMemberId = ISNULL(@OrgMemberId, -1);

SELECT
    [id], 
    [accurate_grams],
    [allow_public],
    [average_frequency],
    [category_id]
FROM 
    dbo.Table1 f
WHERE 
    ISNULL(f.member_id, -1) = CASE 
                                 WHEN @MemberId = -1 
                                    THEN ISNULL(f.member_id, -1) 
                                    ELSE @MemberId 
                              END

it takes lot of time to complete execution.

Note: member_id column contains null values. And it is not mapped as a foreign key

    ISNULL(f.member_id, -1) = CASE 
                                 WHEN @MemberId = -1 
                                    THEN ISNULL(f.member_id, -1) 
                                    ELSE @MemberId 
                              END

Reason to write above line is - sometimes users send null value to @MemberId. To handle this scenario I have used this line in where clause. In single query I can fetch two different results. When member_id is null then query select all records and when member_id is not null then query select only those records matches with Member_id. So there is no need to use if else.

Is there any other solution to handle this?

Upvotes: 0

Views: 4504

Answers (1)

marc_s
marc_s

Reputation: 754458

One possible approach would be to use a covering index - an index that contains all the columns your query needs to return:

CREATE NONCLUSTERED INDEX ncix_table1_member_id
ON dbo.Table1 (member_id) 
INCLUDE (id, accurate_grams, allow_public, average_frequency, category_id)
WHERE member_id IS NOT NULL;
GO

With this approach, now your query can be "satisfied" by just looking at the index - and it doesn't have to do a lot of (fairly expensive) "key lookups" into the actual table's data pages to fetch the "missing" column values for your query

Upvotes: 3

Related Questions