Performance for nightly jobs

I have a job that takes 9 hours that I need to optimize. The job is an sp
that populates a flat table. Here are the rules of the job.
Members own contracts. Contracts are made up of diffrent products that are
grouped in product categories. A certain product may be in several diffrent
contracts. My job needs to display the member, the product category, and a
distinct count of products in that category for the member.
I have optimized the query so far by taking it out of a cursor but I have
one last query that is still hurting my performance.
The first pass (which has a slight flaw) looked liked so...
SELECT a.member_id,
b.product_category,
COUNT(b.product_id)
FROM member_contract a (NOLOCK)
JOIN product b (NOLOCK)
ON a.contract_id = b.contract_id
GROUP BY a.member_id,
b.product_category
The result time was beautiful in which the whole query took less than 2 min
and the correct number of rows in the result set. Problem is that the
products are dupplicated and I do not get an acurate count or products in a
product category for each member (I get a number larger than what is actually
designated for that particular member), so I put in the key word distinct.
This really droped performance down. the new query looks like so...
SELECT a.member_id,
b.product_category,
COUNT(DISTINCT b.product_id)
FROM member_contract a (NOLOCK)
JOIN product b (NOLOCK)
ON a.contract_id = b.contract_id
GROUP BY a.member_id,
b.product_category
I have introduced the following indexes on the product table trying to
optimize this query.
1st
contract_id,
product_category,
product_id
2nd
product_category,
contract_id,
product_id
There is a pk & clustered index on the prooduct table that is not the
product_id (it is simply an identity column that cannot be changed because I
have a full-text index on another unrelated column).
Table product has 3 million rows
Table member_contract has 33,000 rows
For those that have taken the time to read through my pain...Thank you I
really appreciate it.
Jared
Hi,
Please post DDL (CREATE TABLE statements) and sample data (INSERT
statements, just enough to duplicate your incorrect count issue); there may
be another way to re-write the query without the DISTINCT keyword that's
slowing you down.
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:C70994A5-DAB1-4501-BF48-8966FA33C73F@.microsoft.com...
> I have a job that takes 9 hours that I need to optimize. The job is an sp
> that populates a flat table. Here are the rules of the job.
> Members own contracts. Contracts are made up of diffrent products that
are
> grouped in product categories. A certain product may be in several
diffrent
> contracts. My job needs to display the member, the product category, and
a
> distinct count of products in that category for the member.
> I have optimized the query so far by taking it out of a cursor but I have
> one last query that is still hurting my performance.
> The first pass (which has a slight flaw) looked liked so...
> SELECT a.member_id,
> b.product_category,
> COUNT(b.product_id)
> FROM member_contract a (NOLOCK)
> JOIN product b (NOLOCK)
> ON a.contract_id = b.contract_id
> GROUP BY a.member_id,
> b.product_category
> The result time was beautiful in which the whole query took less than 2
min
> and the correct number of rows in the result set. Problem is that the
> products are dupplicated and I do not get an acurate count or products in
a
> product category for each member (I get a number larger than what is
actually
> designated for that particular member), so I put in the key word distinct.
> This really droped performance down. the new query looks like so...
>
> SELECT a.member_id,
> b.product_category,
> COUNT(DISTINCT b.product_id)
> FROM member_contract a (NOLOCK)
> JOIN product b (NOLOCK)
> ON a.contract_id = b.contract_id
> GROUP BY a.member_id,
> b.product_category
> I have introduced the following indexes on the product table trying to
> optimize this query.
> 1st
> contract_id,
> product_category,
> product_id
> 2nd
> product_category,
> contract_id,
> product_id
> There is a pk & clustered index on the prooduct table that is not the
> product_id (it is simply an identity column that cannot be changed because
I
> have a full-text index on another unrelated column).
>
> Table product has 3 million rows
> Table member_contract has 33,000 rows
> For those that have taken the time to read through my pain...Thank you I
> really appreciate it.
> Jared
|||CREATE TABLE member_contract
(
member_id int,
contract_id int
)
CREATE TABLE product
(
contract_id,
product_category_id,
product_id
)
INSERT member_contract
values
(1,1)
INSERT member_contract
values
(1,2)
INSERT member_contract
values
(2,1)
INSERT member_contract
values
(2,3)
insert product
values
(1, 1, 1)
insert product
values
(1, 1, 1)
insert product
values
(1, 1, 1)
insert product
values
(1, 1, 2)
insert product
values
(1, 2, 1)
insert product
values
(1, 2, 1)
insert product
values
(2, 1, 4)
insert product
values
(2, 2, 2)
"Adam Machanic" wrote:

> Hi,
> Please post DDL (CREATE TABLE statements) and sample data (INSERT
> statements, just enough to duplicate your incorrect count issue); there may
> be another way to re-write the query without the DISTINCT keyword that's
> slowing you down.
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:C70994A5-DAB1-4501-BF48-8966FA33C73F@.microsoft.com...
> are
> diffrent
> a
> min
> a
> actually
> I
>
>
|||Kirk,
How can a product change categories?
insert product
values
(1, 1, 2)
insert product
values
(1, 2, 1)
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:CE4E31B2-A028-42FB-8B3C-4E74DD983881@.microsoft.com...
> CREATE TABLE product
> (
> contract_id,
> product_category_id,
> product_id
> )
>
|||The short answer is bad database design, The long answer is a lose term (and
when I say lose I mean very lose), for the word category in which a product
can be part of a package like needles, or it can be cross referanced in the
subscription drug catagory.
Right now I am playing with grouping all items I need to remove the dups and
then aggregating on the result of the correlated query. So far it is not
helping much.
"Adam Machanic" wrote:

> Kirk,
> How can a product change categories?
> insert product
> values
> (1, 1, 2)
> insert product
> values
> (1, 2, 1)
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:CE4E31B2-A028-42FB-8B3C-4E74DD983881@.microsoft.com...
>
>
|||Kirk,
I also notice that you don't have a primary key on the product table, and
you have duplicate data. Is that the case in the real database? I don't
think there's going to be a way to speed up this process until you deal with
your data integrity issues.
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:3B37AB13-099F-4FAD-B874-3F192A2B7C13@.microsoft.com...
> The short answer is bad database design, The long answer is a lose term
(and
> when I say lose I mean very lose), for the word category in which a
product
> can be part of a package like needles, or it can be cross referanced in
the
> subscription drug catagory.
> Right now I am playing with grouping all items I need to remove the dups
and
> then aggregating on the result of the correlated query. So far it is not
> helping much.
sql

Performance for nightly jobs

I have a job that takes 9 hours that I need to optimize. The job is an sp
that populates a flat table. Here are the rules of the job.
Members own contracts. Contracts are made up of diffrent products that are
grouped in product categories. A certain product may be in several diffrent
contracts. My job needs to display the member, the product category, and a
distinct count of products in that category for the member.
I have optimized the query so far by taking it out of a cursor but I have
one last query that is still hurting my performance.
The first pass (which has a slight flaw) looked liked so...
SELECT a.member_id,
b.product_category,
COUNT(b.product_id)
FROM member_contract a (NOLOCK)
JOIN product b (NOLOCK)
ON a.contract_id = b.contract_id
GROUP BY a.member_id,
b.product_category
The result time was beautiful in which the whole query took less than 2 min
and the correct number of rows in the result set. Problem is that the
products are dupplicated and I do not get an acurate count or products in a
product category for each member (I get a number larger than what is actuall
y
designated for that particular member), so I put in the key word distinct.
This really droped performance down. the new query looks like so...
SELECT a.member_id,
b.product_category,
COUNT(DISTINCT b.product_id)
FROM member_contract a (NOLOCK)
JOIN product b (NOLOCK)
ON a.contract_id = b.contract_id
GROUP BY a.member_id,
b.product_category
I have introduced the following indexes on the product table trying to
optimize this query.
1st
contract_id,
product_category,
product_id
2nd
product_category,
contract_id,
product_id
There is a pk & clustered index on the prooduct table that is not the
product_id (it is simply an identity column that cannot be changed because I
have a full-text index on another unrelated column).
Table product has 3 million rows
Table member_contract has 33,000 rows
For those that have taken the time to read through my pain...Thank you I
really appreciate it.
JaredHi,
Please post DDL (CREATE TABLE statements) and sample data (INSERT
statements, just enough to duplicate your incorrect count issue); there may
be another way to re-write the query without the DISTINCT keyword that's
slowing you down.
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:C70994A5-DAB1-4501-BF48-8966FA33C73F@.microsoft.com...
> I have a job that takes 9 hours that I need to optimize. The job is an sp
> that populates a flat table. Here are the rules of the job.
> Members own contracts. Contracts are made up of diffrent products that
are
> grouped in product categories. A certain product may be in several
diffrent
> contracts. My job needs to display the member, the product category, and
a
> distinct count of products in that category for the member.
> I have optimized the query so far by taking it out of a cursor but I have
> one last query that is still hurting my performance.
> The first pass (which has a slight flaw) looked liked so...
> SELECT a.member_id,
> b.product_category,
> COUNT(b.product_id)
> FROM member_contract a (NOLOCK)
> JOIN product b (NOLOCK)
> ON a.contract_id = b.contract_id
> GROUP BY a.member_id,
> b.product_category
> The result time was beautiful in which the whole query took less than 2
min
> and the correct number of rows in the result set. Problem is that the
> products are dupplicated and I do not get an acurate count or products in
a
> product category for each member (I get a number larger than what is
actually
> designated for that particular member), so I put in the key word distinct.
> This really droped performance down. the new query looks like so...
>
> SELECT a.member_id,
> b.product_category,
> COUNT(DISTINCT b.product_id)
> FROM member_contract a (NOLOCK)
> JOIN product b (NOLOCK)
> ON a.contract_id = b.contract_id
> GROUP BY a.member_id,
> b.product_category
> I have introduced the following indexes on the product table trying to
> optimize this query.
> 1st
> contract_id,
> product_category,
> product_id
> 2nd
> product_category,
> contract_id,
> product_id
> There is a pk & clustered index on the prooduct table that is not the
> product_id (it is simply an identity column that cannot be changed because
I
> have a full-text index on another unrelated column).
>
> Table product has 3 million rows
> Table member_contract has 33,000 rows
> For those that have taken the time to read through my pain...Thank you I
> really appreciate it.
> Jared|||CREATE TABLE member_contract
(
member_id int,
contract_id int
)
CREATE TABLE product
(
contract_id,
product_category_id,
product_id
)
INSERT member_contract
values
(1,1)
INSERT member_contract
values
(1,2)
INSERT member_contract
values
(2,1)
INSERT member_contract
values
(2,3)
insert product
values
(1, 1, 1)
insert product
values
(1, 1, 1)
insert product
values
(1, 1, 1)
insert product
values
(1, 1, 2)
insert product
values
(1, 2, 1)
insert product
values
(1, 2, 1)
insert product
values
(2, 1, 4)
insert product
values
(2, 2, 2)
"Adam Machanic" wrote:

> Hi,
> Please post DDL (CREATE TABLE statements) and sample data (INSERT
> statements, just enough to duplicate your incorrect count issue); there ma
y
> be another way to re-write the query without the DISTINCT keyword that's
> slowing you down.
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:C70994A5-DAB1-4501-BF48-8966FA33C73F@.microsoft.com...
> are
> diffrent
> a
> min
> a
> actually
> I
>
>|||Kirk,
How can a product change categories?
insert product
values
(1, 1, 2)
insert product
values
(1, 2, 1)
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:CE4E31B2-A028-42FB-8B3C-4E74DD983881@.microsoft.com...
> CREATE TABLE product
> (
> contract_id,
> product_category_id,
> product_id
> )
>|||The short answer is bad database design, The long answer is a lose term (an
d
when I say lose I mean very lose), for the word category in which a product
can be part of a package like needles, or it can be cross referanced in the
subscription drug catagory.
Right now I am playing with grouping all items I need to remove the dups and
then aggregating on the result of the correlated query. So far it is not
helping much.
"Adam Machanic" wrote:

> Kirk,
> How can a product change categories?
> insert product
> values
> (1, 1, 2)
> insert product
> values
> (1, 2, 1)
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:CE4E31B2-A028-42FB-8B3C-4E74DD983881@.microsoft.com...
>
>|||Kirk,
I also notice that you don't have a primary key on the product table, and
you have duplicate data. Is that the case in the real database? I don't
think there's going to be a way to speed up this process until you deal with
your data integrity issues.
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:3B37AB13-099F-4FAD-B874-3F192A2B7C13@.microsoft.com...
> The short answer is bad database design, The long answer is a lose term
(and
> when I say lose I mean very lose), for the word category in which a
product
> can be part of a package like needles, or it can be cross referanced in
the
> subscription drug catagory.
> Right now I am playing with grouping all items I need to remove the dups
and
> then aggregating on the result of the correlated query. So far it is not
> helping much.

Performance - Left Outer Joins VS Updates

We currently are in a debate on what is quicker, doing a query in a SP
that has Nth left outer joins or doing a insert followed by update
queries. let me lay out a simple scenerio. Please advise, the DBA
group is set on saying example 2 is better (they keep telling me Im
doing too many table scans).
Table "Store" has 1 Record
StoreNum: 1
Table "StoreDate" has many records that are associated to the Store
table; we will say 3 records fo this example
StoreNum: 1
DateType:1
DateValue: 1/1/2001
StoreNum: 1
DateType:2
DateValue: 2/2/2002
StoreNum: 1
DateType:3
DateValue: 3/3/2003
EXAMPLE 1
Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)
Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, SD1.DateValue As OpenDate, SD2.DateValue As
CloseDate, SD3.DateValue As LastInspectionDate
>From Store S Left Outer Join StoreDate SD1 ON
S.StoreNum = SD1.StoreNum And SD1.DateType = 1 Left Outer Join
StoreDate SD2 ON
S.StoreNum = SD2.StoreNum And SD2.DateType = 2 Left Outer Join
StoreDate SD3 ON
S.StoreNum = SD3.StoreNum And SD3.DateType = 3
EXAMPLE 2
Create table X
StoreNum int not null,
OpenDate datetime null,
CloseDate datetime null,
LastInspectionDate datetime null)
Insert into X (StoreNum, OpenDate, CloseDate, LastInspectionDate)
Select S.StoreNum, null As OpenDate, null As CloseDate, null As
LastInspectionDate
>From Store S
Update X
set opendate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 1
Update X
set closedate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 2
Update X
set lastinspectiondate = datevalue
from storedates sd
where x.storenum = sd.storenum
and sd.datetype = 3Did you test it for yourself with SET STATISTICS IO ON?
You could easily improve Ex 2 by combining the three UPDATEs but I
would try something different from either solution:
INSERT INTO X (storenum, opendate, closedate, lastinspectiondate)
SELECT storenum,
MAX(CASE WHEN datetype = 1 THEN datevalue END),
MAX(CASE WHEN datetype = 2 THEN datevalue END),
MAX(CASE WHEN datetype = 3 THEN datevalue END)
FROM Store
WHERE datetype BETWEEN 1 AND 3
GROUP BY storenum ;
David Portas
SQL Server MVP
--|||The examples are not equivalent. The first example will insert the number
of rows in Store, leaving existing rows in the inserted table unaffected.
The second example will insert the same number of rows, but will update
EVERY row in the inserted table (that joins successfully). I can't imagine
that the insert/update logic is "superior" in any way. Is there an
assumption that the inserted table is empty?
I'll ignore the potential logic flaws based on the design assumptions (e.g.,
store has a 1:1 relationship with storedate for rows where type = 3). The
3rd datetime column named "LastInspectionDate" seems to contradict the
assumed 1:1 relationship.
As David indicated, you don't even need to join store to storedate to
generate the correct information (unless there is some "irregular" aspect to
your design/schema that was not mentioned).