Sql-server – Simple SELECT taking too long to run

performancequery-performancesql serversql server 2014

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

Best Answer

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?

Related Question