Query taking too long

Hi all.
I have a query that is selecting distinct product and state from a main
table with over 3 million rows. I have an index on state and product, and
used a hint to make it use the index, but it still takes 45 seconds or longer
to return. I need to get this down to less than a couple of seconds.
What is the best way to do this? Will a view help? Or create a lookup table?
Thanks!
SusanWhat kind of index do you have? Do you have 2 seperate indices on each field
or a one composite index on both fields? Is the index clustered? What does
the query and table structure look like?
--
MG
"Susan Cooper" wrote:
> Hi all.
> I have a query that is selecting distinct product and state from a main
> table with over 3 million rows. I have an index on state and product, and
> used a hint to make it use the index, but it still takes 45 seconds or longer
> to return. I need to get this down to less than a couple of seconds.
> What is the best way to do this? Will a view help? Or create a lookup table?
> Thanks!
> Susan|||The index is a composite index of both fields, with State first. It is not
clustered.
The table is:
[dbo].[policy] (
[policy_number] [char] (12) , --PK
[description] [char] (30) ,
[status] [char] (1) ,
[effective_datetime] [datetime] NULL ,
[state] [char] (2) ,
[source] [char] (3) ,
[source_details] [char] (10) ,
[num_of_rated_drivers] [int] NULL ,
[num_of_excluded_drivers] [int] NULL ,
[num_of_non_driver_residents] [int] NULL ,
[num_of_vehicles] [int] NULL ,
[program] [char] (1) ,
[term] [int] NULL ,
[bill_plan_id] [char] (10) ,
[percent_down_payment] [int] NULL ,
[number_of_installments] [int] NULL ,
[down_payment_amount] [int] NULL ,
[payment_method] [char] (1) ,
[premium_amount] [int] NULL ,
[market_code] [char] (12) ,
[agency_code] [char] (10) ,
[producer_code] [char] (10) ,
[local_fee_1] [int] NULL ,
[local_fee_2] [int] NULL ,
[municipal_tax_rate] [int] NULL ,
[datetime_quote_created] [datetime] NULL ,
[created_by] [char] (10) ,
[datetime_last_updated] [datetime] NULL ,
[updated_by] [char] (10) ,
[datetime_uploaded] [datetime] NULL ,
[uploaded_by] [char] (10) ,
[upload_status] [char] (1) ,
[fees] [int] NULL ,
[received_date] [datetime] NULL ,
[processed_date] [datetime] NULL ,
[Credit_Check_Result] [char] (1) ,
[Number_of_Credit_Requests] [int] NULL ,
[Credit_Score_Level] [char] (2) ,
[Score_Provider] [char] (3) ,
[Score_Reference_Number] [int] NULL ,
[Tier_Group] [char] (2) ,
[Tier] [int] NULL ,
[Adverse_Action_Notice] [char] (1) ,
[Product] [int] NULL ,
[Agent_License_Number] [char] (10) ,
[Underwriting_Group] [int] NULL ,
[Number_of_Nonchargeable_Incidents] [int] NULL ,
[Rate_Book_Indicator] [char] (10) ,
[Version_Number] [char] (50) ,
[Pos_status] [char] (1) ,
[POS_token_id] [char] (32) ,
[UDI_ID] [char] (38) ,
[Quote_ID] [char] (32)
The query is:
select distinct state, product
from dbo.policy
WITH (INDEX (state_program))
The purpose of this is that a new application needs all of the products
associated with each state.
Thanks!
"MGeles" wrote:
> What kind of index do you have? Do you have 2 seperate indices on each field
> or a one composite index on both fields? Is the index clustered? What does
> the query and table structure look like?
> --
> MG
>
> "Susan Cooper" wrote:
> > Hi all.
> >
> > I have a query that is selecting distinct product and state from a main
> > table with over 3 million rows. I have an index on state and product, and
> > used a hint to make it use the index, but it still takes 45 seconds or longer
> > to return. I need to get this down to less than a couple of seconds.
> >
> > What is the best way to do this? Will a view help? Or create a lookup table?
> >
> > Thanks!
> > Susan|||Is the query meant to build a list for a dropdown or something like that? If
so I'd probably just use a lookup table containing the distinct possibilities
like you had said originally.
I'm not sure that the query would benefit that much from an index because it
looks like it would need to go through the entire talbe to get teh results
that you want.
Did you recently add the index and get a peformance improvement?
--
MG
"Susan Cooper" wrote:
> The index is a composite index of both fields, with State first. It is not
> clustered.
> The table is:
> [dbo].[policy] (
> [policy_number] [char] (12) , --PK
> [description] [char] (30) ,
> [status] [char] (1) ,
> [effective_datetime] [datetime] NULL ,
> [state] [char] (2) ,
> [source] [char] (3) ,
> [source_details] [char] (10) ,
> [num_of_rated_drivers] [int] NULL ,
> [num_of_excluded_drivers] [int] NULL ,
> [num_of_non_driver_residents] [int] NULL ,
> [num_of_vehicles] [int] NULL ,
> [program] [char] (1) ,
> [term] [int] NULL ,
> [bill_plan_id] [char] (10) ,
> [percent_down_payment] [int] NULL ,
> [number_of_installments] [int] NULL ,
> [down_payment_amount] [int] NULL ,
> [payment_method] [char] (1) ,
> [premium_amount] [int] NULL ,
> [market_code] [char] (12) ,
> [agency_code] [char] (10) ,
> [producer_code] [char] (10) ,
> [local_fee_1] [int] NULL ,
> [local_fee_2] [int] NULL ,
> [municipal_tax_rate] [int] NULL ,
> [datetime_quote_created] [datetime] NULL ,
> [created_by] [char] (10) ,
> [datetime_last_updated] [datetime] NULL ,
> [updated_by] [char] (10) ,
> [datetime_uploaded] [datetime] NULL ,
> [uploaded_by] [char] (10) ,
> [upload_status] [char] (1) ,
> [fees] [int] NULL ,
> [received_date] [datetime] NULL ,
> [processed_date] [datetime] NULL ,
> [Credit_Check_Result] [char] (1) ,
> [Number_of_Credit_Requests] [int] NULL ,
> [Credit_Score_Level] [char] (2) ,
> [Score_Provider] [char] (3) ,
> [Score_Reference_Number] [int] NULL ,
> [Tier_Group] [char] (2) ,
> [Tier] [int] NULL ,
> [Adverse_Action_Notice] [char] (1) ,
> [Product] [int] NULL ,
> [Agent_License_Number] [char] (10) ,
> [Underwriting_Group] [int] NULL ,
> [Number_of_Nonchargeable_Incidents] [int] NULL ,
> [Rate_Book_Indicator] [char] (10) ,
> [Version_Number] [char] (50) ,
> [Pos_status] [char] (1) ,
> [POS_token_id] [char] (32) ,
> [UDI_ID] [char] (38) ,
> [Quote_ID] [char] (32)
> The query is:
> select distinct state, product
> from dbo.policy
> WITH (INDEX (state_program))
> The purpose of this is that a new application needs all of the products
> associated with each state.
> Thanks!
> "MGeles" wrote:
> > What kind of index do you have? Do you have 2 seperate indices on each field
> > or a one composite index on both fields? Is the index clustered? What does
> > the query and table structure look like?
> > --
> > MG
> >
> >
> > "Susan Cooper" wrote:
> >
> > > Hi all.
> > >
> > > I have a query that is selecting distinct product and state from a main
> > > table with over 3 million rows. I have an index on state and product, and
> > > used a hint to make it use the index, but it still takes 45 seconds or longer
> > > to return. I need to get this down to less than a couple of seconds.
> > >
> > > What is the best way to do this? Will a view help? Or create a lookup table?
> > >
> > > Thanks!
> > > Susan|||I think this will be used for a drop down or something similar. I almost
created a lookup table, but then wondered if there was a better way to do
this.
I did recently add the index, and it didn't help things much at all.
Thanks so much for your help.
"MGeles" wrote:
> Is the query meant to build a list for a dropdown or something like that? If
> so I'd probably just use a lookup table containing the distinct possibilities
> like you had said originally.
> I'm not sure that the query would benefit that much from an index because it
> looks like it would need to go through the entire talbe to get teh results
> that you want.
> Did you recently add the index and get a peformance improvement?
> --
> MG
>
> "Susan Cooper" wrote:
> > The index is a composite index of both fields, with State first. It is not
> > clustered.
> >
> > The table is:
> > [dbo].[policy] (
> > [policy_number] [char] (12) , --PK
> > [description] [char] (30) ,
> > [status] [char] (1) ,
> > [effective_datetime] [datetime] NULL ,
> > [state] [char] (2) ,
> > [source] [char] (3) ,
> > [source_details] [char] (10) ,
> > [num_of_rated_drivers] [int] NULL ,
> > [num_of_excluded_drivers] [int] NULL ,
> > [num_of_non_driver_residents] [int] NULL ,
> > [num_of_vehicles] [int] NULL ,
> > [program] [char] (1) ,
> > [term] [int] NULL ,
> > [bill_plan_id] [char] (10) ,
> > [percent_down_payment] [int] NULL ,
> > [number_of_installments] [int] NULL ,
> > [down_payment_amount] [int] NULL ,
> > [payment_method] [char] (1) ,
> > [premium_amount] [int] NULL ,
> > [market_code] [char] (12) ,
> > [agency_code] [char] (10) ,
> > [producer_code] [char] (10) ,
> > [local_fee_1] [int] NULL ,
> > [local_fee_2] [int] NULL ,
> > [municipal_tax_rate] [int] NULL ,
> > [datetime_quote_created] [datetime] NULL ,
> > [created_by] [char] (10) ,
> > [datetime_last_updated] [datetime] NULL ,
> > [updated_by] [char] (10) ,
> > [datetime_uploaded] [datetime] NULL ,
> > [uploaded_by] [char] (10) ,
> > [upload_status] [char] (1) ,
> > [fees] [int] NULL ,
> > [received_date] [datetime] NULL ,
> > [processed_date] [datetime] NULL ,
> > [Credit_Check_Result] [char] (1) ,
> > [Number_of_Credit_Requests] [int] NULL ,
> > [Credit_Score_Level] [char] (2) ,
> > [Score_Provider] [char] (3) ,
> > [Score_Reference_Number] [int] NULL ,
> > [Tier_Group] [char] (2) ,
> > [Tier] [int] NULL ,
> > [Adverse_Action_Notice] [char] (1) ,
> > [Product] [int] NULL ,
> > [Agent_License_Number] [char] (10) ,
> > [Underwriting_Group] [int] NULL ,
> > [Number_of_Nonchargeable_Incidents] [int] NULL ,
> > [Rate_Book_Indicator] [char] (10) ,
> > [Version_Number] [char] (50) ,
> > [Pos_status] [char] (1) ,
> > [POS_token_id] [char] (32) ,
> > [UDI_ID] [char] (38) ,
> > [Quote_ID] [char] (32)
> >
> > The query is:
> > select distinct state, product
> > from dbo.policy
> > WITH (INDEX (state_program))
> >
> > The purpose of this is that a new application needs all of the products
> > associated with each state.
> >
> > Thanks!
> >
> > "MGeles" wrote:
> >
> > > What kind of index do you have? Do you have 2 seperate indices on each field
> > > or a one composite index on both fields? Is the index clustered? What does
> > > the query and table structure look like?
> > > --
> > > MG
> > >
> > >
> > > "Susan Cooper" wrote:
> > >
> > > > Hi all.
> > > >
> > > > I have a query that is selecting distinct product and state from a main
> > > > table with over 3 million rows. I have an index on state and product, and
> > > > used a hint to make it use the index, but it still takes 45 seconds or longer
> > > > to return. I need to get this down to less than a couple of seconds.
> > > >
> > > > What is the best way to do this? Will a view help? Or create a lookup table?
> > > >
> > > > Thanks!
> > > > Susan|||On Tue, 25 Apr 2006 08:34:02 -0700, Susan Cooper wrote:
>The index is a composite index of both fields, with State first. It is not
>clustered.
(snip DDL)
>The query is:
>select distinct state, product
>from dbo.policy
>WITH (INDEX (state_program))
Hi Susan,
Since there is no WHERE clause, this query has to scan the complete
index. With over 3 milion rows, this will take time.
You might consider using an indexed view. This basically precomputes the
information you need and changes the precomputed results every time the
data in the table changes. This will introduce some overhead for changes
to the data in the table, but judging by the name, this is not a table
that gets changed very frequently.
CREATE VIEW ProductsPerState WITH SCHEMABINDING
AS
SELECT state, product, COUNT_BIG(*) AS tally
FROM dbo.policy
GROUP BY state, product
go
CREATE UNIQUE CLUSTERED INDEX ix_ProductsPerState
ON ProductsPerState(state, product)
go
Once the view is created, you can build the dropdown by querying the
ProductsPerState view.
--
Hugo Kornelis, SQL Server MVP