Performance problem involving sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance problem involving sum

Author

Topic

cardgunner

326 Posts

Posted - 2007-12-28 : 16:12:53

First of all I'm not schooled in SQL taught out of book and thru this forum.

I have a select statement I use over and over however it takes 17secs to run. It returns aound 800 records.


select tmp.wo, tmp.t_seg, A.type, A.unit,
	sum(tmp.parts+tmp.labor+tmp.misc)t_amnt
from
(select	B.wo, B.seg, 
        sum(case when A.flat=1 then 0 else B.invc end) as 'parts',
        ''as 'labor',
        '' as 'misc'
	from B join
	A on A.wo=B.wo and A.seg=B.seg
	group by B.wi, B.seg
union all
select 	C.wo, C.seg,
        '' as 'parts',
        sum(case when A.flat=1 then 0 else C.invc end ) as 'labor', 
        '' as 'misc'
	from C join
	A on A.wo=C.wo and A.seg=C.seg
	group by C.wo, C.seg
union all
select 	D.wo, D.seg,
        '' as 'parts',
        '' as 'labor', 
        sum(case when A.flat=1 then A.other else D.invc end ) as 'misc'	
	from D join
	A on A.wo=D.wo and A.seg=D.seg
	group by D.wo, D.seg
)tmp join
A on A.wo=tmp.wo and A.seg=tmp.seg
where A.status NOT IN (23,35)
group by tmp.wo, tmp.seg, A.type,A.unit

if I take sum(tmp.parts+tmp.labor+tmp.misc)t_amnt out of the statement it runs in 2 seconds or less.

I need to run it from live data and can not do a snapshot.

Like I said before I'm running this statement in about 20 different reports.

Any help

Card Gunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-28 : 17:06:58

Please post the DDL including indexes for all tables involved.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cardgunner

326 Posts

Posted - 2007-12-28 : 17:14:30

Sorry Tara. I'm home now and don't have access to the server. In anycase I'm not sure what is meant by DDL. Indexes I know what that means and will be able to get that info Monday :(

Card Gunner

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-12-28 : 17:33:54

http://msdn2.microsoft.com/en-us/library/ms713672.aspx
data definition language (DDL) A language that defines all attributes and properties of a database, especially record layouts, field definitions, key fields, file locations, and storage strategy.

CODO ERGO SUM

cardgunner

326 Posts

Posted - 2007-12-31 : 14:05:05

This is what I can tell you of the indexes,

A=Indexed on A.WO and A.seg
B=Indexed on B.WO and B.line
C=Indexed on C.WO and C.line
D=Imdexed on D.WO and D.line

They are checked as Clustered Indexes, Uniquie Values, File group is Primary

the Edit SQl brings up this


CREATE UNIQUE CLUSTERED
  INDEX [IA_1a] ON A ([wo], [line])
WITH
    DROP_EXISTING
ON [PRIMARY]

wo is a char(9) field
seg is a smallint

line is a samll int but I'm not using line. Maybe I should?

Card Gunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 15:19:07

Where's the DDL?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cardgunner

326 Posts

Posted - 2007-12-31 : 15:59:33

ummm,

wo is a char(9) field
seg is a smallint
line is a samllint

Is there a place that i can go to get you this?

I'm not trying to be dumb I really don't know.

Card Gunner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 16:17:12

I'm certainly not going to write out the DDL for your problem. We need the DDL statements to test it out in our own environments. We're way too busy to do that for the posters.

Enterprise Manager scripts objects out nicely.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cardgunner

326 Posts

Posted - 2008-01-02 : 11:08:32

Tara,

Thank you for your time.

I thought there may have been something wrong with my SQL Statemet or a better way I could write it to perform better.

If I knew where I could go to get you this DDL I would. I don't know where so I can not.

Again thank you for your time and I will look into learing this DDL so I may better help myself next time I have a question similiar to this one.

Card Gunner

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-02 : 12:13:05

Tables B, C, D could do with indices on the "wo + seg"...i.e all fields involved in the where clause. The "line" column in the indices specified are not optimal for helping you (with this query).

re the DDL....you're nearly there...just extend the snippet below to include what is needed to create the full table....

the Edit SQl brings up this

CREATE UNIQUE CLUSTERED
INDEX [IA_1a] ON A ([wo], [line])
WITH
DROP_EXISTING
ON [PRIMARY]

you may also be better off going down a style of .....

select a.wo, a.tmp, A.type, A.unit, sum(case b.abc...), sum(case c.def...), sum(case d.xyz...)
from a
left join b on a=b
left join c on a=c
left join d on a=d
etc....

the original query seems to involve tablea in the effort too many times.

re performance....how much data is in each of tablea, b, c, d.....800 rows returned in 17 seconds may seem a long time for a small set of data....but given it COULD be a summmary of 20000000000 record???

CrisO
Starting Member

6 Posts

Posted - 2008-01-02 : 12:41:54

Try something like this...

select A.wo, A.seg, A.type, A.unit,
sum(case when A.Flat=1 then 0 else B.invc+C.invc+D.invc end) AS Amt
from A
Left Join B on B.wo=A.wo and B.seg=A.seg
Left Join C on C.wo=A.wo and C.seg=A.seg
Left Join D on D.wo=A.wo and D.seg=A.seg
where A.status NOT IN (23,35)
group by A.wo, A.seg, A.type, A.unit

Be sure you have indexes that include columns WO and SEG on all tables.

If you do not need to see a row for A.Flat = 1 at all,
you could simplify this as...

select A.wo, A.seg, A.type, A.unit,
sum(B.invc+C.invc+D.invc) AS Amt
from A
Left Join B on B.wo=A.wo and B.seg=A.seg
Left Join C on C.wo=A.wo and C.seg=A.seg
Left Join D on D.wo=A.wo and D.seg=A.seg
where A.status NOT IN (23,35) AND A.FLAT = 0
group by A.wo, A.seg, A.type, A.unit

And it might help to have indexes on A.Flat and A.Status as well.

Better yet, a covering index on table A
that includes columns WO,SEG,TYPE,UNIT,STATUS,FLAT

CrisO
Starting Member

6 Posts

Posted - 2008-01-02 : 12:51:27

Problem in my earlier post.

You will have a problem with this due to possible.likely null values.

Fix this by using the isnull function in the Amt field calculation...

sum(case when A.Flat = 1
then 0
else isnull(B.invc,0)
+ isnull(C.invc,0)
+ isnull(D.invc,0)
) as Amt

cardgunner

326 Posts

Posted - 2008-01-02 : 15:22:07

Andrew thanks for responding.

A has 28600records
B has 67323
C has 54992
D has 34205

I will need to get permission from my boss to alter the tables so I'm keeping the that till the last.
Creating new indexes would constitute that I feel.

I'm in the process of adding my table and names to your SQL to test.

Cris O

I did convert mine to yours. It ran and gave me the correct number of records however the amounts are off.
I'm looking into that. Part of the reason is I did not represent my fields right. There are three flat.
It should have been A.flat_b, A.flat_c, A.flat_d

the SQL should be like this



select tmp.wo, tmp.t_seg, A.type, A.unit,
	sum(tmp.parts+tmp.labor+tmp.misc)t_amnt
from
(select	B.wo, B.seg, 
        sum(case when A.flat_b=1 then 0 else B.invc end) as 'parts',
        ''as 'labor',
        '' as 'misc'
	from B join
	A on A.wo=B.wo and A.seg=B.seg
	group by B.wi, B.seg
union all
select 	C.wo, C.seg,
        '' as 'parts',
        sum(case when A.flat_c=1 then 0 else C.invc end ) as 'labor', 
        '' as 'misc'
	from C join
	A on A.wo=C.wo and A.seg=C.seg
	group by C.wo, C.seg
union all
select 	D.wo, D.seg,
        '' as 'parts',
        '' as 'labor', 
        sum(case when A.flat_d=1 then A.other else D.invc end ) as 'misc'	
	from D join
	A on A.wo=D.wo and A.seg=D.seg
	group by D.wo, D.seg
)tmp join
A on A.wo=tmp.wo and A.seg=tmp.seg
where A.status NOT IN (23,35)
group by tmp.wo, tmp.seg, A.type,A.unit

But it did run in 8 seconds.

Card Gunner

CrisO
Starting Member

6 Posts

Posted - 2008-01-02 : 15:51:14

Then change the SUM() to ...

sum(case when A.Flat_b = 1 then 0 else isnull(B.invc,0) end
+ case when A.Flat_c = 1 then 0 else isnull(C.invc,0) end
+ case when A.Flat_d = 1 then 0 else isnull(D.invc,0) end
) as Amt

cardgunner

326 Posts

Posted - 2008-01-02 : 16:08:01

Thanks for hanging in there Cris0

I tried that already and I'm getting duplicated numbers. I don't know what the technical term is.
The only way I have been able to rid the statement of the unwan ted duplicates is thru a union query. But I'm still trying.

Card Gunner

CrisO
Starting Member

6 Posts

Posted - 2008-01-02 : 16:33:24

Card Gunner,

If A.Flat_B <> 1, will there be any child records found in B?

Same question for C & D.

If child records do not exist
when the corresponding A.flat <> 1,
then you could try this as well...

select A.wo, A.seg, A.type, A.unit,
sum(COALESCE(B.invc,0)+COALESCE(C.invc,0)+COALESCE(D.invc,0)) Amt
from A
Left Join B on B.wo=A.wo and B.seg=A.seg
Left Join C on C.wo=A.wo and C.seg=A.seg
Left Join D on D.wo=A.wo and D.seg=A.seg
where A.status NOT IN (23,35)
group by A.wo, A.seg, A.type, A.unit

cardgunner

326 Posts

Posted - 2008-01-02 : 16:35:17

What this is is a work order with different segments.
Each segments is a different job. So seg 1 is brakes, seg 2 is transmission, and etc.
Each segment has it's own parts, labor, and others cost.
Sometimes that cost is flat rated. So we charge a flat $ rather then the actual.

so A is the seg info
B is the parts
C is the Labor
D is the other
(I did not design this system)

so for instance

wo 1881
seg 10
has parts
ZZ $58.8
XX $107.06
YY $59.90
WW $57.60
VV $-6120.25
labor
$592.00 ( 11 entries)
Other
uu $29.60

My result should be $-5215.29

Without the union a query that sums parts, that sums labor sums other I get a number like $59617.79.

Card Gunner

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-01-02 : 16:38:23

CardGunner,

For future reference, DDL is "Data Definition Language". When someone asks you to post the DDL, sample data, and desired output here, they're looking for something like this:


CREATE TABLE MyTable(ID Identity(1,1), LastName Varchar(20), FirstName VarChar(20))

INSERT INTO MyTable
  SELECT 'Smith, 'John' UNION ALL
  SELECT 'Doe', 'Jane'

My expected output:

LastOne    FirstOne    LastTwo    FirstTwo
=======    ========    =======    ========
Smith      John        Doe        Jane

Here's what I've tried so far:

SELECT LastName, FirstName, LastName, FirstName FROM MyTable

That doesn't work. Can someone help me with the SQL to get the results I'm looking for?

Thanks.

See? There's enough information (although the example I provided is pretty stupid) so that someone can help you without having to take the time to try and read your mind about what your table looks like, what kind of data to use, and guess at what results you want to achieve. They also know what you've already tried that didn't work. So they can copy your DDL and sample data (the CREATE TABLE and INSERT statements), paste it into EM or SSMS, and create the actual table and data you have. They can then help you solve your problem.

Hope this helps in the future.

cardgunner

326 Posts

Posted - 2008-01-02 : 16:43:18

Oh yeah if we decide we want to "Flat" rate parts or labor then there is a check box we need to look for.

If that box is checked find the value for that Flat rate in the others table (Don't ask, cause I can't tell you why )

so if we are to flat rate labor in this seg say $400 irt would like like this

wo 1881
seg 10
has parts
ZZ $58.8
XX $107.06
YY $59.90
WW $57.60
VV $-6120.25
labor A.flat_c=1
$592.00 ( 11 entries)
Other
uu $29.60
ff $400.00

Card Gunner

cardgunner

326 Posts

Posted - 2008-01-02 : 17:00:50

Hey thanks Ken. I think I can do that with some practice. Really thanks.

with this instance I would do 4 create table with 4 sample data?

it would be create table A (wo char(9), seg smallint,flat_b tinyint, flat_c tinyint, flat_c tinyint)
create table B (wo char(9)seg smallint, invc float)

Insert into table A select '1881', '10', 1,1,1
insert into table B select '1881','10',58.80 union all
'1881','10','107.06 union all
'1881','80',59.90

and so on and so forth ?

Card Gunner

CrisO
Starting Member

6 Posts

Posted - 2008-01-02 : 17:58:07

I see what you mean.

Try this...

select distinct A.wo, A.seg, SUM(T.Amt) as Amt
from A
Left Join (select WO, Seg, SUM(Invc) as Amt From B GROUP BY WO, SEG
UNION ALL select WO, Seg, SUM(Invc) as Amt From C GROUP BY WO, SEG
UNION ALL select WO, Seg, SUM(Invc) as Amt From D GROUP BY WO, SEG
) as T
on T.wo=A.wo and T.seg=A.seg
where A.status NOT IN (23,35)
group by A.wo, A.seg

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources