I have a big fat table having 10 million rows. This table has three major columns as CurrencyCode, DealType and ID. There are 9 distinct CurrencyCode, 5 distinct DealType and 1 million distinct Ids.I created a clustered index on CurrencyCode,DealType, ID.
I have a SQL query as "select xxx,xxx,xxx... from this_table where CurrencyCode='USD' and DealType='PE' and ID in (1,2,3,xxx...)". Usually, number of IDs will go up to 10000 as a cap.With 10000 IDs in the query, i start to get data back in 6 seconds and get all data back in 28 seconds.In the execution plan, it shows 98% time is spent on table scanning.
Is it possible to make the query run faster? like getting data from 2nd second? Is the order of index right.Is there other way to do the select? When i load data in this table, it is done by currency in 9 patches, but i don't really know this will help or not.
thank you for your inputs.
I have a SQL query as "select xxx,xxx,xxx... from this_table where CurrencyCode='USD' and DealType='PE' and ID in (1,2,3,xxx...)". Usually, number of IDs will go up to 10000 as a cap.With 10000 IDs in the query, i start to get data back in 6 seconds and get all data back in 28 seconds.In the execution plan, it shows 98% time is spent on table scanning.
Is it possible to make the query run faster? like getting data from 2nd second? Is the order of index right.Is there other way to do the select? When i load data in this table, it is done by currency in 9 patches, but i don't really know this will help or not.
thank you for your inputs.