fast - query - SQL Server Forums

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

Transact-SQL (2005)

fast - query

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-08-04 : 12:18:13

select sum(x.totalquantity) as totalquantity,
x.group_type as group_type
from (select * from statezip('cpim,','9/1/2007','5/7/2008','St','camp','')) x
group by x.group_type

then it tooks only 1second to execute

but
select sum(x.totalquantity) as totalquantity,
x.group_type as group_type
from (select * from statezip('hethehr,','9/1/2007','5/7/2008','St','camp','')) x
group by x.group_type

then it took 38 seconds..can anyonetell me to speed up the query..
the function is same for both..just inputing the values r different..

create function statezip
( @camp varchar(50), @startdate datetime, @enddate datetime, @type char(70),
@category varchar(50), @category_value varchar(50))

RETURNS TABLE
AS
RETURN

(Select distinct t.Camp,t.Item,sum(t.totalquantity) as Totalquantity,t.Group_type

from
(
select distinct

c.camp, vs.item, sum(vs.totalquantity) as totalquantity,

case @group_type
when 'state' then vs.state
when 'zipcode' then vs.zipcode
when 'country' then vs.country
end as group_type,

case @category
when 'camp' then 'camp'
when 'item' then 'item'
end as category,

case @category_value
when '' then ''
when vs.item then vs.item
end as category_value,
p.indx

from Pubs P

left outer join camp C

on C.indx =P.indx

left outer join stzip vs
on vs.item = p.sku

Where (vs.item = @category_value or @category_value='') and c.Camp = @Camp and (vs.date between @startdate and @enddate)

group by c.camp, vs.item, vs.state, vs.zipcode, p.indx,vs.country

) as t

group by t.group_type,t.itemr,t.indx,t.camp

)

can anyone tell me the solution..

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 12:59:34

Why are you grouping twice inside udf? can you explain your requirement?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-08-04 : 13:20:23

thanks for replying visakh.
if i m executing this ssimple select statement then also it took 40seconds..

select distinct s3.item,s2.date,
sum(s3.quantity) as totalquantity,s2.state
from S300 s3
left outer join s200 s2
on s2.snumber = s3.snumber
left outer join pubs p
on p.sku = s3.ITEM
left outer join camp c
on c.indx = p.indx
where C.cam = 'htruth' and s2.stype=3 and s2.date between '1/1/2008' and '8/5/2008'
group by s2.stype,s3.item,s2.state,s2.date

and if i m using C.cam = 'xyz' then it takes 0seconds to execute..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:22:50

and wats the difference in number of rows retrieved in both cases?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-08-04 : 13:33:41

yup..its huge difference...first one its more than 4500 rows and second one -'xyz' is 800 rows..

but any solution?
it just for this value only it occurs..

thanks for ur help visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:45:45

quote:
Originally posted by palak

yup..its huge difference...first one its more than 4500 rows and second one -'xyz' is 800 rows..

but any solution?
it just for this value only it occurs..

thanks for ur help visakh.

and do you have suffiecient indexes on your table? what does the query execution plan state?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-08-04 : 13:56:58

index seek - s200table - cost 27%, RID lookup s200table cost 15% and RID lookup s300table - cost 57%..

while in xyz is samething except RID lookup s300table cost 59%

also table scan is cost 0% in both values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 14:04:33

also what's the purpose of distinct after applying the group by?i cant understand its significance?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-08-04 : 14:11:19

it doesn't matter..gives same results with or without distinct..just to get faster i tried to use it. thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 14:22:15