question

shraddha avatar image
shraddha asked

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.

performance-tuning
untitled.png (8.8 KiB)
screen-1.png (40.8 KiB)
5 comments
10 |1200

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

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 Like 1 ·

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

0 Likes 0 ·

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 Likes 0 ·

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 Likes 0 ·

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 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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