need help in performance tuning a simple select

I am running this simple select 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 total 2066381 rows for year 2018.(total rows - 2549803)there is one index which has INVOICE_NUMBER, EMPLOYEE_ID, DAY_DATE, LOCATION_ID, ITEM_ID columns.

I am using sql server 14.

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock commented · Feb 20, 2019 at 08:14 AM

Can you share the execution plan and the schema of the ABC_TABLE

Do you need all of 64 columns to be returned in the select?

1 ·

shraddha commented · Feb 20, 2019 at 10:34 AM

hi @sp_lock I have updated my question and can't upload rest of the column details

0 ·

sp_lock commented · Feb 20, 2019 at 01:54 PM

Can you share just the datatypes? If you have BLOB/XML etc are you are returning 2 millions rows with large datatypes then that will not help.

You could look at a better indexing strategy

Option 1 - Create a MASSIVE Covering index with the DAY_DATE as the key column

Option 2 - "Assuming you have a clustered index. Create NC index with the DAY_DATE as the key. It will cause a key lookup, but would mitigate the large covering index.

0 ·

shraddha commented · Feb 20, 2019 at 02:32 PM

there are only float,int,varchar,char and date data types. and there is no clustered index on that table(it is a fact table)

0 ·

Show more comments