query timing issues - 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 2000 Forums

SQL Server Development (2000)

query timing issues

Author

Topic

pizzojm
Starting Member

20 Posts

Posted - 2003-07-18 : 14:49:19

Hello. I have a query running through a sproc, however it is still taking, what some consider, a huge amount of time at around 30-40 seconds. I was wondering if anyone has any indexing/other advice that could help me boost this query into and around the 10 second range. Here is the main table schema and query. The problem as I see it is where the cost, weight and qty are being summed. The table stores apprx 5 million records at anytime. Thanks for any advice.

shrink_ser serial
shrink_dte date
str_nbr char(3)
shrink_type char(1)
pl_dept char(2)
qty integer
weight decimal(7,2)
cost decimal(9,4)
day_nbr char(2)
week_nbr char(2)
reason_cde char(2)
item_ser integer
lchg_dte date
user_id varchar(50,0)

select first 50
i.item_nbr, i.item_desc, i.prod_cde, i.cat_cde, i.class_cde, pl.pl_desc, sum(s.cost), sum(s.weight), sum(s.qty)
from shrink_tbl s, item_tbl i, ns_pl pl
where s.item_ser = i.item_ser
and s.pl_dept = pl.pl_nbr
and s.lchg_dte = thedate
group by 1,2,3,4,5,6
order by 7 desc

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-18 : 15:30:23

I take it you have indexes on

i.item_ser
pl.pl_nbr
thedate

What is thedate? If it is a variable then put an index on s.lchg_dte.

Is there a record in the other tabvles for each shrink_tbl rec?
If not and you are losing a lot of recs due to the join then it may be worth putting an index on that field in shrink_tbl.
You could also try putting a compaund index on all the join fields in shrink_tbl and check the query plan but I doubt if that would help.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pizzojm
Starting Member

20 Posts

Posted - 2003-07-18 : 15:49:15

Every shrink record has a corresponding record in the item_tbl (apprx 200,000 rows) and every shrink record also has a corresponding pl_nbr from the pl table (apprx 30 rows). thedate is as you thought a variable passed into the sproc for the day the user requests.

Am I asking to much to even think that this query could ever finish in an approriate amount of time... or is there something else perhaps I am missing somewhere along the way someone can point out?
Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 16:52:52

What do you think about:


SELECT i.item_nbr, i.item_desc, i.prod_cde, i.cat_cde, i.class_cde, pl.pl_desc, sum(s.cost), sum(s.weight), sum(s.qty) 
FROM shrink_tbl s, 
INNER JOIN ns_pl pl
ON  s.pl_dept = pl.pl_nbr 
INNER JOIN (
SELECT DISTINCT TOP 50 item_nbr, item_desc, prod_cde, cat_cde, class_cde
	       FROM item_tbl
           ORDER BY item_nbr, item_desc, prod_cde, cat_cde, class_cde
) AS i
ON s.item_ser = i.item_ser
WHERE s.lchg_dte = '2003/07/04'
GROUP BY 1,2,3,4,5,6
ORDER BY 7 DESC

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-18 : 18:23:20

Brett,

plz, format your sql codes for the 800x600 screen.
This terrible horizontal scroll bar...

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-18 : 18:34:29

I meant your code is good but not quite good...
check for your typos...

- Vit

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-18 : 18:36:51

quote:

800x600

Really? That's ancient at this point.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-18 : 19:33:49

quote:
Really? That's ancient at this point.

But at least it's legible on 12" LCD screens and 15" monitors.

Before you say "buy a bigger monitor", try working for Fly-by-Night Mom & Pop Industries first, so you can really appreciate how LITTLE some people will spend on computer equipment.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-18 : 19:37:37

I was really just commenting on somebody in the IT business. I have had to work with (but not for) plenty of mom and pop shops.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-19 : 02:59:37

uh-oh! Is this query in Access??

>>
select first 50
i.item_nbr, i.item_desc, i.prod_cde, i.cat_cde, i.class_cde, pl.pl_desc, sum(s.cost), sum(s.weight), sum(s.qty)
from shrink_tbl s, item_tbl i, ns_pl pl
where s.item_ser = i.item_ser
and s.pl_dept = pl.pl_nbr
and s.lchg_dte = thedate
group by 1,2,3,4,5,6
order by 7 desc
>>

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-19 : 14:56:13

Tara,

I'm just a blue-collared researcher and have nothing in common
with any sort of business. So, the 800x600 rulez forever.

Owais,

no. This is the newest hybrid of RDBMSDBFFEB and NRDBDDSERTB.

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-21 : 09:06:01

OK...This is how I usuallywrite my code (would fit in 80 byte wide...

(and as for typos...it's hard to test when you don't get the ddl)

(It's even harder to perf tune when it doesnt make any sense)

SELECT TOP 50...I mean please, I gotta go find out what happened it
the other thread discussing TOP and FIRST...

Both should be thrown out...it's so mis-used..

And why is it used? Display purposes?

Would you ever use it with an UPDATE, DELETE, INSERT?



    SELECT i.item_nbr
 	 , i.item_desc
	 , i.prod_cde
	 , i.cat_cde
	 , i.class_cde
	 , pl.pl_desc
	 , sum(s.cost)
	 , sum(s.weight)
	 , sum(s.qty) 
      FROM shrink_tbl s 
INNER JOIN ns_pl pl
        ON  s.pl_dept = pl.pl_nbr 
INNER JOIN (
		SELECT 
			DISTINCT TOP 50 
				  item_nbr
				, item_desc
				, prod_cde
				, cat_cde
				, class_cde
	          FROM item_tbl
              ORDER BY item_nbr, item_desc, prod_cde, cat_cde, class_cde
	) AS i
	ON s.item_ser = i.item_ser
     WHERE s.lchg_dte = '2003/07/04'
  GROUP BY 1,2,3,4,5,6
  ORDER BY 7 DESC

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-21 : 17:13:36

Now it's quite good... so pleasant for eyes...

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources