1

I am running this simple query:

SELECT * 
FROM ABC_TABLE 
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'

There are 64 columns and we need all of them.

There are a total of 2,066,381 rows for year 2018. Total row count is 2,549,803.

There is one index which has INVOICE_NUMBER, EMPLOYEE_ID, DAY_DATE, LOCATION_ID, ITEM_ID columns.

I am using SQL Server 2014.

I am unable to understand why it is taking more than half an hour.

select count(*) from ABC_TABLE was executed in a fraction of a second.

Even if I use few columns in the select statement it takes really long to respond like the below one (>3 mins):

select COUNTRY_ID, 
EMPLOYEE_ID,
DAY_DATE,
LOCATION_ID,
CUST_ID,
ITEM_ID,
PROM_ID,
sum(s_cost)
from abc_table
WHERE DAY_DATE >='1-JAN-2018' AND DAY_DATE<='31-DEC-18'
group by COUNTRY_ID, 
EMPLOYEE_ID,
DAY_DATE,
LOCATION_ID,
CUST_ID,
ITEM_ID,
PROM_ID
  • 1
    In order for that query to use an index, the DAY_DATE column must be the first column in the index. An index on (INVOICE_NUMBER, EMPLOYEE_ID, DAY_DATE, LOCATION_ID, ITEM_ID) can't be used by that qurey – a_horse_with_no_name Feb 20 at 7:49
  • Additionally to @a_horse_with_no_name's comment - you are using SELECT *, so even the index with the first column DAY_DATE could be ignored by the query optimizer. – Denis Rubashkin Feb 20 at 8:04
  • When 80% of records is selected table scan is more effective than index scan. No index will help. – Akina Feb 20 at 10:45
2

Basically, you have 3 options, using regular indexes (b-trees):

Create a clustered index on DAY_DATE. But chances are that you already have a clustered index on something else, and you cannot, for some reason, change that.

Create a non-clustered index on DAY_DATE. Whether or not this will be used by the optimizer depends on how many rows you return for that condition (2,066,381) compared to number of rows in the table (unknown to us). For each row, SQL Server would need at least one page access to go fetch that page.

Create a non-clustered index that covers the query with DAY_DATE as the key column and the other columns that you query refers to as included columns. Since you have to do SELECT *, then you would need all columns in that index, making it as big as the data it self (a bit bigger, even). Quite likely not doable.

A columnstore index might help, depending on the compression level you achieve in the end. But, again, that index would need all columns!

Bottom line: Do you really need that many rows and that many columns?

  • do you mean I should create another index with all the columns ( COUNTRY_ID, EMPLOYEE_ID, DAY_DATE, LOCATION_ID, CUST_ID, ITEM_ID, PROM_ID) in it? – Shraddha Kadam Feb 20 at 11:45
  • You need to create the index the right way. DAY_DATE should be the key. The other columns should be included. Something like CREATE INDEX x ON tbl(DAY_DATE) INCLUDE(COUNTRY_ID, EMPLOYEE_ID, LOCATION_ID, CUST_ID, ITEM_ID, PROM_ID). Make sure you get all the columns that the query refers to. – Tibor Karaszi Feb 20 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.