Query Syntax: product

Showing posts with label product. Show all posts

Monday, March 26, 2012

Query with MAX Date

I have the following situation:
The name of a product can change with time. These changes are stored in a
table with 3 columns: Product_id, Date and ProductName with Product_id and
Date forming the Primary Key.
I want to run a query that returns the product_id and each product's latest
name. For the life of me, I can't get my head rould what such a query would
look like. I can get a query that gives me just the product_id and the Max
of Date with a group by on the Product_id but when I introduce the
ProductName, it returns ALL names.
I have a gut feeling that there may be a subquery involved, or am I barking
up the wrong tree?
Any help much appreciated.
PeteYou can either use a subquery:
SELECT Product_id, ProductName
FROM Your_table t1
WHERE Date = (SELECT MAX(Date) FROM Your_table t2 WHERE t1.Product_id =
t2.Product_id)
or a derived table:
SELECT t1.Product_id, t1.ProductName
FROM Your_table t1
INNER JOIN
(SELECT Product_id, MAX(Date) AS max_date
FROM Your_table
GROUP BY Product_id) t2
ON t1.Product_id = t2.Product_id
AND t1.Date = t2.max_date
Jacco Schalkwijk
SQL Server MVP
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
>I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
> latest
> name. For the life of me, I can't get my head rould what such a query
> would
> look like. I can get a query that gives me just the product_id and the
> Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
> barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||First, please change your column name 'Date' to something more meaningful
and something that doesn't use a reserved word. Also, you should be
consistent in your column naming. Why does Product_id have an underscore,
but ProductName not? Finally, in the future, please post DDL, sample data,
and desired results. See http://www.aspfaq.com/5006
In the meantime, you can try this:
SELECT o.Product_id, i.MaxDate, o.ProductName
FROM Products o
INNER JOIN
(
SELECT Product_id, MaxDate = MAX([Date])
FROM Products
GROUP BY Product_id
) i
ON o.Product_id = i.Product_id
AND o.[Date] = i.MaxDate
http://www.aspfaq.com/
(Reverse address to reply.)
"Italian Pete" <ItalianPete@.discussions.microsoft.com> wrote in message
news:4847B525-302C-4354-99EE-8165C56B60D9@.microsoft.com...
> I have the following situation:
> The name of a product can change with time. These changes are stored in a
> table with 3 columns: Product_id, Date and ProductName with Product_id and
> Date forming the Primary Key.
> I want to run a query that returns the product_id and each product's
latest
> name. For the life of me, I can't get my head rould what such a query
would
> look like. I can get a query that gives me just the product_id and the
Max
> of Date with a group by on the Product_id but when I introduce the
> ProductName, it returns ALL names.
> I have a gut feeling that there may be a subquery involved, or am I
barking
> up the wrong tree?
> Any help much appreciated.
> Pete|||Here is a solution based on guesswork:
SELECT t1.product_id, t1.product_name
FROM tbl t1
WHERE ( SELECT MAX( t2.dtcol )
FROM tbl t2
WHERE t2.product_id = t1.product_id ) = t1.dtcol ;
If this is not what you are looking for, refer to www.aspfaq.com/5006 and
provide required information.
Anith

Query where feild name is reserved

Using query analyzer, how can I return a column (desc in this case)
that is also a reserved word?
I'm working with a commercial product so I can't change the name of
the column.
I've tried things like:
Select desc from foo
Select 'desc' from foo
select id,desc from foo
etc...
each one gives me: Error near reserved word 'desc'
Thanks.ANSI SQL compliant:
SELECT "desc" FROM foo
SQL Server specific:
SELECT [desc] FROM foo
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Scamp@.nospam.com> wrote in message news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>|||When encountering any object name that is a reserved word, enclose it in
either double quotes, or square brackets.
"desc" or [desc]
And when you use multiple part names, such as:
MyDatabase.dbo.Table
enclose on the reserved word part of the name in delimiters:
MyDatabase.dbo.[Table]
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<Scamp@.nospam.com> wrote in message
news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>sql

Monday, March 12, 2012

Query to group sequential items

Let's say I have the following table:

entry product quality
1 A 80
2 A 70
3 A 80
4 B 60
5 B 90
6 C 80
7 D 80
8 A 50
9 C 70

I'm looking for a way to find the average "quality" value for a
SEQUENTIAL GROUPING of the same Product. For exmple, I need an
average of Entry 1+2+3 (because this is the first grouping of the same
product type), but NOT want that average to include row 8 (which is
also Product A, but in a different "group".)

I'm sure it can be done (because I can describe it!), but I'll be a
monkey's uncle if I can figure out how. I would imagine it would
involve some sort of running tally that references the next record as
it goes... to see if the product type has changed. Perhaps use of a
temporary table?

Muchas gracias!!
Cy.Easy way ... cursor or loop thru as you stated.

WARNING - THE FOLLOWING IS AN UNTESTED HALF BACKED IDEA -
CONSUME AT YOUR OWN RISK

The set oriented way would require the addition of a grouping column,
initially null and populated via update statements from a temp table

use something like this to generate a set of the grouping transition rows.

-- GENERATED GROUP IDS AND GET MAX ENTRY IN GROUP
select
identity(int,1,1) as groupid
a.product,
a.entry
into #groupings
from mytable a
join mytable b on a.product != b.product and a.entry = b.entry + 1

-- UPDATES BASE TABLE WITH FOR MAX ENTRY IN GROUP
update a
set groupid = g.groupid
from mytable a
join #grouping g on a.entry = g.entry

-- UPDATES PRIOR ENTRIES IN GROUP
update a
set a.groupid = g.groupid
from mytable a
join #grouping g on a.entry < g.entry
where a.groupid is null

-- QUERY TO RETURN RESULTS YOU ARE LOOKING FOR
select groupid , min( product ) , max( entry ) , min( entry) , sum (
quantity ) , count(*) , avg( quantity)
from mytable
group by groupid

----

I am not so sure about the 2nd update here, as I am tired and going to bed
soon. you may also need to join to the grouping temp table on the product
and also put a not exists() in the where clause, but you may be covered by
the simple is null to prevent muliple updates.

Let me know how you make out, and if this points you in a good direction or
throws you off track.

<cyrus.kapadia@.us.pm.com> wrote in message
news:1102562637.046747.292110@.c13g2000cwb.googlegr oups.com...
> Let's say I have the following table:
> entry product quality
> 1 A 80
> 2 A 70
> 3 A 80
> 4 B 60
> 5 B 90
> 6 C 80
> 7 D 80
> 8 A 50
> 9 C 70
> I'm looking for a way to find the average "quality" value for a
> SEQUENTIAL GROUPING of the same Product. For exmple, I need an
> average of Entry 1+2+3 (because this is the first grouping of the same
> product type), but NOT want that average to include row 8 (which is
> also Product A, but in a different "group".)
> I'm sure it can be done (because I can describe it!), but I'll be a
> monkey's uncle if I can figure out how. I would imagine it would
> involve some sort of running tally that references the next record as
> it goes... to see if the product type has changed. Perhaps use of a
> temporary table?
> Muchas gracias!!
> Cy.|||<cyrus.kapadia@.us.pm.com> wrote in message
news:1102562637.046747.292110@.c13g2000cwb.googlegr oups.com...
> Let's say I have the following table:
> entry product quality
> 1 A 80
> 2 A 70
> 3 A 80
> 4 B 60
> 5 B 90
> 6 C 80
> 7 D 80
> 8 A 50
> 9 C 70
> I'm looking for a way to find the average "quality" value for a
> SEQUENTIAL GROUPING of the same Product. For exmple, I need an
> average of Entry 1+2+3 (because this is the first grouping of the same
> product type), but NOT want that average to include row 8 (which is
> also Product A, but in a different "group".)
> I'm sure it can be done (because I can describe it!), but I'll be a
> monkey's uncle if I can figure out how. I would imagine it would
> involve some sort of running tally that references the next record as
> it goes... to see if the product type has changed. Perhaps use of a
> temporary table?
> Muchas gracias!!
> Cy.

CREATE TABLE ProductEntries
(
product_entry INT NOT NULL PRIMARY KEY,
product_code CHAR(1) NOT NULL,
product_quality INT NOT NULL
)

INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (1, 'A', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (2, 'A', 70)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (3, 'A', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (4, 'B', 60)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (5, 'B', 90)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (6, 'C', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (7, 'D', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (8, 'A', 50)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (9, 'C', 70)

SELECT PR.product_code AS product_code,
PR.start_product_entry AS start_product_entry,
MAX(P.product_entry) AS end_product_entry,
AVG(CAST(P.product_quality AS DECIMAL)) AS avg_product_quality
FROM (SELECT MIN(PE.product_entry) AS start_product_entry,
PE.next_product_entry AS end_product_entry,
PE.product_code
FROM (SELECT P1.product_entry, P1.product_code,
MIN(P2.product_entry) AS next_product_entry
FROM ProductEntries AS P1
LEFT OUTER JOIN
ProductEntries AS P2
ON P2.product_entry > P1.product_entry AND
P2.product_code <> P1.product_code
GROUP BY P1.product_entry, P1.product_code) AS PE
GROUP BY PE.product_code, PE.next_product_entry) AS PR
INNER JOIN
ProductEntries AS P
ON P.product_code = PR.product_code AND
P.product_entry >= PR.start_product_entry AND
(PR.end_product_entry IS NULL OR
P.product_entry < PR.end_product_entry)
GROUP BY PR.product_code, PR.start_product_entry
ORDER BY start_product_entry

product_code start_product_entry end_product_entry avg_product_quality
A 1 3 76.666666
B 4 5 75.000000
C 6 6 80.000000
D 7 7 80.000000
A 8 8 50.000000
C 9 9 70.000000

--
JAG|||Sure, that may work as well.

"John Gilson" <jag@.acm.org> wrote in message
news:5zQtd.72060$Vk6.20781@.twister.nyc.rr.com...
> <cyrus.kapadia@.us.pm.com> wrote in message
> news:1102562637.046747.292110@.c13g2000cwb.googlegr oups.com...
>> Let's say I have the following table:
>>
>> entry product quality
>> 1 A 80
>> 2 A 70
>> 3 A 80
>> 4 B 60
>> 5 B 90
>> 6 C 80
>> 7 D 80
>> 8 A 50
>> 9 C 70
>>
>> I'm looking for a way to find the average "quality" value for a
>> SEQUENTIAL GROUPING of the same Product. For exmple, I need an
>> average of Entry 1+2+3 (because this is the first grouping of the same
>> product type), but NOT want that average to include row 8 (which is
>> also Product A, but in a different "group".)
>>
>> I'm sure it can be done (because I can describe it!), but I'll be a
>> monkey's uncle if I can figure out how. I would imagine it would
>> involve some sort of running tally that references the next record as
>> it goes... to see if the product type has changed. Perhaps use of a
>> temporary table?
>>
>> Muchas gracias!!
>> Cy.
> CREATE TABLE ProductEntries
> (
> product_entry INT NOT NULL PRIMARY KEY,
> product_code CHAR(1) NOT NULL,
> product_quality INT NOT NULL
> )
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (1, 'A', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (2, 'A', 70)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (3, 'A', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (4, 'B', 60)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (5, 'B', 90)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (6, 'C', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (7, 'D', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (8, 'A', 50)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (9, 'C', 70)
> SELECT PR.product_code AS product_code,
> PR.start_product_entry AS start_product_entry,
> MAX(P.product_entry) AS end_product_entry,
> AVG(CAST(P.product_quality AS DECIMAL)) AS
> avg_product_quality
> FROM (SELECT MIN(PE.product_entry) AS start_product_entry,
> PE.next_product_entry AS end_product_entry,
> PE.product_code
> FROM (SELECT P1.product_entry, P1.product_code,
> MIN(P2.product_entry) AS
> next_product_entry
> FROM ProductEntries AS P1
> LEFT OUTER JOIN
> ProductEntries AS P2
> ON P2.product_entry >
> P1.product_entry AND
> P2.product_code <>
> P1.product_code
> GROUP BY P1.product_entry, P1.product_code) AS
> PE
> GROUP BY PE.product_code, PE.next_product_entry) AS PR
> INNER JOIN
> ProductEntries AS P
> ON P.product_code = PR.product_code AND
> P.product_entry >= PR.start_product_entry AND
> (PR.end_product_entry IS NULL OR
> P.product_entry < PR.end_product_entry)
> GROUP BY PR.product_code, PR.start_product_entry
> ORDER BY start_product_entry
> product_code start_product_entry end_product_entry avg_product_quality
> A 1 3 76.666666
> B 4 5 75.000000
> C 6 6 80.000000
> D 7 7 80.000000
> A 8 8 50.000000
> C 9 9 70.000000
> --
> JAG|||That is too much work! Let's move the average calculation into a
scalar subquery that will be done last, after all the clusters are
found. The little-used = ALL predicate can replace a lot of your
logic. And we pull up the usual Sequence auxiliary table.

SELECT prod_code, MIN(start) AS start, finish,
(SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
FROM ProductEntries AS P3
WHERE P3.prod_entry
BETWEEN MIN(start)
AND X.finish) AS avg_quality
FROM (SELECT P1.prod_code, S1.seq, MAX(S2.seq) AS finish
FROM ProductEntries AS P1, Sequence AS S1, Sequence AS S2
WHERE S1.seq <= S2.seq
AND S2.seq <= (SELECT MAX(prod_entry) FROM ProductEntries)
AND P1.prod_code
= ALL (SELECT P2.prod_code
FROM ProductEntries AS P2
WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq)
GROUP BY P1.prod_code, S1.seq)
AS X (prod_code, start, finish)
GROUP BY prod_code, finish;

Another version requires two sentinal values
--
INSERT INTO ProductEntries VALUES (0, '?', 0);
INSERT INTO ProductEntries VALUES (10, '?', 0);

SELECT DISTINCT P1.prod_code, S1.seq AS start, S2.seq AS finish,
(SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
FROM ProductEntries AS P3
WHERE P3.prod_entry
BETWEEN S1.seq AND S2.seq) AS avg_quality
FROM ProductEntries AS P1,
(SELECT seq FROM Sequence
UNION ALL SELECT 0) AS S1, Sequence AS S2
WHERE S1.seq <= S2.seq
AND S2.seq <= (SELECT MAX(prod_entry) + 1 FROM ProductEntries)
AND P1.prod_code
<> (SELECT P3.prod_code
FROM ProductEntries AS P3
WHERE P3.prod_entry = S1.seq - 1)
AND P1.prod_code
<> (SELECT P4.prod_code
FROM ProductEntries AS P4
WHERE P4.prod_entry = S2.seq + 1)
AND P1.prod_code
= ALL (SELECT P2.prod_code
FROM ProductEntries AS P2
WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq);|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1102618417.146169.127100@.c13g2000cwb.googlegr oups.com...
> That is too much work! Let's move the average calculation into a
> scalar subquery that will be done last, after all the clusters are
> found. The little-used = ALL predicate can replace a lot of your
> logic. And we pull up the usual Sequence auxiliary table.
> SELECT prod_code, MIN(start) AS start, finish,
> (SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
> FROM ProductEntries AS P3
> WHERE P3.prod_entry
> BETWEEN MIN(start)
> AND X.finish) AS avg_quality
> FROM (SELECT P1.prod_code, S1.seq, MAX(S2.seq) AS finish
> FROM ProductEntries AS P1, Sequence AS S1, Sequence AS S2
> WHERE S1.seq <= S2.seq
> AND S2.seq <= (SELECT MAX(prod_entry) FROM ProductEntries)
> AND P1.prod_code
> = ALL (SELECT P2.prod_code
> FROM ProductEntries AS P2
> WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq)
> GROUP BY P1.prod_code, S1.seq)
> AS X (prod_code, start, finish)
> GROUP BY prod_code, finish;
> Another version requires two sentinal values
> --
> INSERT INTO ProductEntries VALUES (0, '?', 0);
> INSERT INTO ProductEntries VALUES (10, '?', 0);
> SELECT DISTINCT P1.prod_code, S1.seq AS start, S2.seq AS finish,
> (SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
> FROM ProductEntries AS P3
> WHERE P3.prod_entry
> BETWEEN S1.seq AND S2.seq) AS avg_quality
> FROM ProductEntries AS P1,
> (SELECT seq FROM Sequence
> UNION ALL SELECT 0) AS S1, Sequence AS S2
> WHERE S1.seq <= S2.seq
> AND S2.seq <= (SELECT MAX(prod_entry) + 1 FROM ProductEntries)
> AND P1.prod_code
> <> (SELECT P3.prod_code
> FROM ProductEntries AS P3
> WHERE P3.prod_entry = S1.seq - 1)
> AND P1.prod_code
> <> (SELECT P4.prod_code
> FROM ProductEntries AS P4
> WHERE P4.prod_entry = S2.seq + 1)
> AND P1.prod_code
> = ALL (SELECT P2.prod_code
> FROM ProductEntries AS P2
> WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq);

Less work? Debatable. Also, this won't work if the product_entry values
aren't consecutive.

--
JAG|||>> Less work? Debatable. <<

Fewer nesting levels should be a bit faster. But trying to find the
start and finish points is going to get really bad as the number of row
increases.

>> Also, this won't work if the product_entry values
aren't consecutive. <<

It depends on the sequence of tests having no gaps.

This is one that might be better done with a cursor and a WHILE loop
that accumulates a count and total of each quality test to a working
table.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:1102630139.1f7348d9b8d0e1527f37d16587e3cecc@.t eranews...
> >> Less work? Debatable. <<
> Fewer nesting levels should be a bit faster. But trying to find the
> start and finish points is going to get really bad as the number of row
> increases.
> >> Also, this won't work if the product_entry values
> aren't consecutive. <<
> It depends on the sequence of tests having no gaps.
> This is one that might be better done with a cursor and a WHILE loop
> that accumulates a count and total of each quality test to a working
> table.

You could be right but bite your tongue!

--
JAG

> --CELKO--
> Please post DDL in a human-readable format and not a machne-generated
> one. This way people do not have to guess what the keys, constraints,
> Declarative Referential Integrity, datatypes, etc. in your schema are.
> Sample data is also a good idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Actually if this was an ongoing query: performance-wise it would be best to
have a trigger or "phase shift" grouping id set as part of the insert
operation.

"John Gilson" <jag@.acm.org> wrote in message
news:bo4ud.74477$Vk6.62153@.twister.nyc.rr.com...
> "--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
> news:1102630139.1f7348d9b8d0e1527f37d16587e3cecc@.t eranews...
>> >> Less work? Debatable. <<
>>
>> Fewer nesting levels should be a bit faster. But trying to find the
>> start and finish points is going to get really bad as the number of row
>> increases.
>>
>> >> Also, this won't work if the product_entry values
>> aren't consecutive. <<
>>
>> It depends on the sequence of tests having no gaps.
>>
>> This is one that might be better done with a cursor and a WHILE loop
>> that accumulates a count and total of each quality test to a working
>> table.
> You could be right but bite your tongue!
> --
> JAG
>> --CELKO--
>> Please post DDL in a human-readable format and not a machne-generated
>> one. This way people do not have to guess what the keys, constraints,
>> Declarative Referential Integrity, datatypes, etc. in your schema are.
>> Sample data is also a good idea, along with clear specifications.
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!|||>> Actually if this was an ongoing query: performance-wise it would be
best to have a trigger or "phase shift" grouping id set as part of the
insert operation. <<

My impulse is for a "cluster group number" column as each test is done.
Look to see if the current quality test is on the same product as the
most recent one, etc.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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 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

Monday, March 26, 2012

Query with MAX Date

Query where feild name is reserved

Query where feild name is reserved

Monday, March 12, 2012

Query to group sequential items

Saturday, February 25, 2012

Query taking too long

Query taking too long

Query Syntax

Blog Archive

About Me