CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user304901
user304901

Reputation: 211

How to improve sql server query response time

I have a table that contains 60 million records with the following structure

CREATE TABLE dbo.cus_pivot_non_unique_indexes
  (
     SEQ_ID                              BIGINT,
     SRM_CLIENT_ENTITIES_SEQ_ID          BIGINT,
     CUS_ENTITY_DATA_SEQ_ID              BIGINT,
     SRM_CLIENT_ENTITY_ATTRIBUTES_SEQ_ID BIGINT,
     ATTRIBUTE_DATETIME                  DATETIME,
     ATTRIBUTE_DECIMAL                   DECIMAL(18, 2),
     ATTRIBUTE_STRING                    NVARCHAR(255),
     ATTRIBUTE_BOOLEAN                   CHAR(1),
     SRM_CLIENTS_SEQ_ID                  BIGINT
  )

The table has two indexes.

  1. Clustered index with key SEQ_ID

  2. Non unique non clustered index : I've following four composite indexes

    a. SRM_CLIENTS_SEQ_ID, SRM_CLIENT_ENTITIES_SEQ_ID, SRM_CLIENT_ENTITY_ATTRIBTUES_SEQ_ID, ATTRIBUTE_DATETIME

    b. SRM_CLIENTS_SEQ_ID, SRM_CLIENT_ENTITIES_SEQ_ID, SRM_CLIENT_ENTITY_ATTRIBTUES_SEQ_ID, ATTRIBUTE_DECIMAL

    c. SRM_CLIENTS_SEQ_ID, SRM_CLIENT_ENTITIES_SEQ_ID, SRM_CLIENT_ENTITY_ATTRIBTUES_SEQ_ID, ATTRIBUTE_STRING

    d. SRM_CLIENTS_SEQ_ID, SRM_CLIENT_ENTITIES_SEQ_ID, SRM_CLIENT_ENTITY_ATTRIBTUES_SEQ_ID, ATTRIBUTE_BOOLEAN

The problem is that when I execute a simple query over this table it does not return the results in an acceptable time.

Query:

SELECT CUS_ENTITY_DATA_SEQ_ID  
FROM   dbo.cus_pivot_non_unique_indexes 
WHERE  srm_client_entity_attributes_seq_id = 51986 
       AND attribute_decimal = 4150196

Execution Time : 2 seconds

Thanks

Upvotes: 1

Views: 876

Answers (1)

Martin Smith
Martin Smith

Reputation: 453047

The indexes you have aren't useful for this query.

You need one with leading columns SRM_CLIENT_ENTITY_ATTRIBUTES_SEQ_ID, ATTRIBUTE_DECIMAL (in either order) to get a seek.

Possibly you might want to INCLUDE the other table columns to make the index covering and avoid lookups. This depends on the selectivity of the predicate and your overall workload balance.

Upvotes: 7

Related Questions