SELECT TOP 10 *
FROM (
SELECT TOP 60 *
FROM Patient
ORDER BY PA_VPatientID DESC
)
ORDER BY PA_VPatientID DESC
Error :-
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.
I did it through the following query
SELECT TOP 10 *
FROM (SELECT TOP 60 *
FROM Patient
ORDER BY PA_VPatientID DESC) a
ORDER BY PA_VPatientID
"Noor" <noor@.ngsol.com> wrote in message
news:eOrEnezXEHA.3044@.TK2MSFTNGP09.phx.gbl...
>
> SELECT TOP 10 *
> FROM (
> SELECT TOP 60 *
> FROM Patient
> ORDER BY PA_VPatientID DESC
> )
> ORDER BY PA_VPatientID DESC
>
> Error :-
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'ORDER'.
>
|||Why are you doing that? Simply using:
SELECT TOP 10 * FROM Patient ORDER BY PA_VPatientID DESC
would be more efficient.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:eOrEnezXEHA.3044@.TK2MSFTNGP09.phx.gbl...
>
> SELECT TOP 10 *
> FROM (
> SELECT TOP 60 *
> FROM Patient
> ORDER BY PA_VPatientID DESC
> )
> ORDER BY PA_VPatientID DESC
>
> Error :-
> Server: Msg 156, Level 15, State 1, Line 5
> Incorrect syntax near the keyword 'ORDER'.
>
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Friday, March 30, 2012
Query Works in SQL 2000 but not in SQL 2005
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
=======================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.
PLEASE HELP....
=====================================================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
EXCEPT
((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))
This is not a bug. This is due to the order in which certain expressions are evaluated. The query optimizer is free to move expressions around to determine the optimum plan. You could get this error in SQL Server 2000 also but it is more easy to hit in SQL Server 2005 due to newer optimizations to match computed column indexes/indexed views. This is documented in the behavior changes topic in BOL. See the link below and search for "Expressions in queries":
http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx
Basically, you need to use CASE expression in all the places where you are doing the CONVERT(float) with ISNUMERIC check in the WHEN clause. You will of course get slightly bad performance due to the complexity of the expressions. The correct approach however is to normalize the table and not mix different data domains in the same column.
|||One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.
Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.
sqlQuery Works in SQL 2000 but not in SQL 2005
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
=======================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.
PLEASE HELP....
=====================================================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
EXCEPT
((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))
This is not a bug. This is due to the order in which certain expressions are evaluated. The query optimizer is free to move expressions around to determine the optimum plan. You could get this error in SQL Server 2000 also but it is more easy to hit in SQL Server 2005 due to newer optimizations to match computed column indexes/indexed views. This is documented in the behavior changes topic in BOL. See the link below and search for "Expressions in queries":
http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx
Basically, you need to use CASE expression in all the places where you are doing the CONVERT(float) with ISNUMERIC check in the WHEN clause. You will of course get slightly bad performance due to the complexity of the expressions. The correct approach however is to normalize the table and not mix different data domains in the same column.
|||One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.
Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.
Friday, March 9, 2012
Query to find dbs that are log shipped
I need to find out all the dbs that are currently in readonly/standby state
on my standby servers. These databases are log shipped with standby option
ThanksCheck out the DATABASEPROPERTY() and DATABASEPROPERTYEX() functions.
--
Tibor Karaszi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23jgPwn8oDHA.424@.TK2MSFTNGP10.phx.gbl...
> I need to find out all the dbs that are currently in readonly/standby
state
> on my standby servers. These databases are log shipped with standby option
> Thanks
>
on my standby servers. These databases are log shipped with standby option
ThanksCheck out the DATABASEPROPERTY() and DATABASEPROPERTYEX() functions.
--
Tibor Karaszi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23jgPwn8oDHA.424@.TK2MSFTNGP10.phx.gbl...
> I need to find out all the dbs that are currently in readonly/standby
state
> on my standby servers. These databases are log shipped with standby option
> Thanks
>
Saturday, February 25, 2012
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 longe
r
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 tabl
e?
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 lon
ger
> 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 ta
ble?
> 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:
[vbcol=seagreen]
> What kind of index do you have? Do you have 2 seperate indices on each fie
ld
> or a one composite index on both fields? Is the index clustered? What do
es
> the query and table structure look like?
> --
> MG
>
> "Susan Cooper" wrote:
>|||Is the query meant to build a list for a dropdown or something like that? I
f
so I'd probably just use a lookup table containing the distinct possibilitie
s
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:
[vbcol=seagreen]
> The index is a composite index of both fields, with State first. It is no
t
> 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:
>|||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:
[vbcol=seagreen]
> 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 possibilit
ies
> 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:
>|||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
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 longe
r
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 tabl
e?
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 lon
ger
> 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 ta
ble?
> 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:
[vbcol=seagreen]
> What kind of index do you have? Do you have 2 seperate indices on each fie
ld
> or a one composite index on both fields? Is the index clustered? What do
es
> the query and table structure look like?
> --
> MG
>
> "Susan Cooper" wrote:
>|||Is the query meant to build a list for a dropdown or something like that? I
f
so I'd probably just use a lookup table containing the distinct possibilitie
s
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:
[vbcol=seagreen]
> The index is a composite index of both fields, with State first. It is no
t
> 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:
>|||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:
[vbcol=seagreen]
> 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 possibilit
ies
> 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:
>|||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
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
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
Query taking longer to run after adding only 2 fields and additional JOIN
why does my query take 2x as long to run after adding the Fees in?
SELECT m.customer,c.name,
c.customer,
c.state,
m.Branch,
CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then
(ph.totalpaid - ph.ForwardeeFee)
ELSE
0.00
END AS [Posted Amount],
ph.systemmonth,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
'' AS [New Old CC],
'In-House' AS Type,
'' AS Active,
ph.UID,
m.number,
dc.amount CC,
p.amount AS PDC,
m.original,
ph.OverPaidAmt,
fg.FeeGoal_AZ,
fg.FeeGoal_IL
FROM dbo.Master m LEFT JOIN dbo.payhistory ph ON m.number = ph.number
INNER JOIN dbo.DeC dc ON dc.number = m.number
INNER JOIN dbo.pdc p ON p.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
INNER JOIN ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer
GROUP BY m.customer,
c.name,
c.customer,
c.state,
m.Branch,
ph.OverPaidAmt,
ph.systemmonth,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
ph.UID,
m.number,
dc.amount,
p.amount,
m.original ,
ph.systemmonth,
ph.systemyear,
ph.ForwardeeFee,
fg.FeeGoal_AZ,
fg.FeeGoal_IL
HAVING ph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND
ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate())
ORDER BY m.customer
I assume that you mean that you added the two columns from the FeeGoal table to the select list and this caused the query to run 2x as long.
My guess would be that these columns are not included in an index and you forced the optimizer to do a table scan on the FeeGoal table.
Have you looked the the execution plan for one versus the other? What indexes do you have on the FeeGoal table? How big is the FeeGoal table?
I would start by looking at the plan, but if you could provide a bit more about these tables and what indexes they have on them, I might be able to give some tips.
HTH
Subscribe to:
Posts (Atom)