Odd Execution Plan

Question

I have a table in SQL Server that is performing very slowly and I have been trying different indexing options to try to speed it up to no avail.

I suspect all of the varchar(max) fields may be a source of the pain.

Here is the structure:

CREATE TABLE [SurveyData](
    [sdid] [bigint] IDENTITY(1,1) NOT NULL,
    [sid] [bigint] NOT NULL,
    [job_code] [varchar](50) NULL,
    [job_title] [varchar](255) NULL,
    [job_level] [varchar](50) NULL,
    [job_description] [varchar](max) NULL,
    [paytype] [char](1) NULL,
    [scope_fields] [varchar](max) NULL,
    [scope_values] [varchar](max) NULL,
    [data_fields] [varchar](max) NULL,
    [data_values] [varchar](max) NULL,
    [base_pay_fields] [varchar](max) NULL,
    [base_pay_values] [varchar](max) NULL,
    [var_pay_fields] [varchar](max) NULL,
    [var_pay_values] [varchar](max) NULL,
    [total_comp_fields] [varchar](max) NULL,
    [total_comp_values] [varchar](max) NULL,
    CONSTRAINT [PK_SurveyData] PRIMARY KEY CLUSTERED 
    (
        [sdid] ASC,
        [sid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,         
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Doing any queries on this table causes the execution plan to use "Nested Loops (Inner Join) and a Key Lookup. Queries take at least 25 seconds to return data.

I filter data by sid, job_code, job_level, and scope_values. I do use a query with an inner join to retrieve data in a stored procedure, but I want to get the data flowing faster before I work on improving that.

Here is the query I'm using for testing:

SELECT [scope_values], [job_title], [job_code], [job_level], [paytype],              
    [job_description], [base_pay_fields], [base_pay_values],
    [var_pay_fields], [var_pay_values], [total_comp_fields], 
    [total_comp_values], [data_values], [sid]         
FROM [surveydata] WHERE [sid] = 176

Here is the link to the execution plan: Execution Plan

This query returns 55,455 rows, and takes approximately 27 seconds.

The varchars that are max probably don't need to be max, but there's no standard of how long the data in those fields will be (pipe delimited strings) which is why I went with max just to be safe. The slowdown is in management studio as well as in the application. I'm trying to get the queries more efficient so the delays will be reduced in the application. I reduced all of the varchar(max) fields to varchar(255) and not much improvement and still the "nested loops" despite the fact that there is no inner join in the query.

The application will narrow the result down to 1 row, but that 1 row is taking forever (just like the 55k). I created a new table using the create script for the original table, then populated it with the original data. It no longer has the nested loops, but it now takes 2 minutes instead of 20ish seconds to complete in management studio.

Odd Execution Plan

0 Answers0