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 ·

enterprisetoday shraddha commented · Feb 22, 2019 at 02:58 AM

Hi Shraddha. The table scan is the slowest method for searching. So that's why it takes so long. Your index is not being used at all, and that's no surprise because your filter statement is on the DAY_DATE column, and none of the columns in the index.

sp_lock's suggestion is correct. Create an index on this table, DAY_DATE is your key.

0 ·