Mysterious delay in 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)

Mysterious delay in query

Author

Topic

jh_sql
Starting Member

24 Posts

Posted - 2011-07-08 : 03:25:02

Hello, can anyone help me understand where delay executing this simple query comes from, or say any possibilities i might have missed?

SELECT count(op.id) AS amount, avg(op.adults) AS adults, avg(op.child) AS child, op.offer_id, group_id AS id,op.product_id

FROM offer

LEFT OUTER JOIN op ON op.offer_id=offer.id
LEFT OUTER JOIN op_rows ON op_rows.op_id=op.id

WHERE offer.type=1 AND op.group_id<>0
AND (op.offer_day between '7.1.2011' and '7.8.2011')

GROUP BY op.group_id,op.offer_id,op.product_id

Without join to op_rows, query executes instantly, otherwise there is 15 seconds executing time in server. in op_rows there is 300 000 rows, wich shouldnt be a problem i think?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-08 : 04:00:59

What are the datatypes of the columns involved in WHERE and ON clauses?

No, you're never too old to Yak'n'Roll if you're too young to die.

jh_sql
Starting Member

24 Posts

Posted - 2011-07-08 : 04:26:09

op.offer_id=bigint, no nulls
offer.id=bigint, no nulls, autoincrement,primarykey
op_rows.op_id=bigint, no nulls
op.id=bigint, no nulls, autoincrement,primarykey

offer.type=int, no nulls
group_id=bigint, no nulls
op.offer_day=datetime, no nulls

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-08 : 04:51:57

change
AND (op.offer_day between '7.1.2011' and '7.8.2011')
to
AND (op.offer_day >= '20110701' and op.offer_day <'20110709')

No, you're never too old to Yak'n'Roll if you're too young to die.

jh_sql
Starting Member

24 Posts

Posted - 2011-07-08 : 05:05:39

Thank for reply, but didnt have any affect on excecution time

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-08 : 05:41:54

sometimes this is worth a try...
add a line to the end of your query:
OPTION (maxdop=1)

No, you're never too old to Yak'n'Roll if you're too young to die.

jh_sql
Starting Member

24 Posts

Posted - 2011-07-11 : 02:06:29

Thanks again, tried OPTION (maxdop 1), but it seemed to add few seconds more to excecution time :S

Server is in clustered environment, tried now eaven moving the SQL service to different node, but didnt help either.

Any more ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-11 : 02:17:15

Your WHERE clause is working against your LEFT JOIN intention.

SELECT		COUNT(w.ID) AS Amount,
		AVG(w.Adults) AS Adults,
		AVG(w.Child) AS Child,
		w.Offer_ID,
		o.Group_ID AS ID,
		w.Product_ID 
FROM		dbo.Offer AS o
LEFT JOIN	dbo.op AS w ON w.Offer_ID = o.Offer.ID
			AND w.Group_ID <> 0 
			AND w.Offer_Day BETWEEN '20110701' AND '20110708'
LEFT JOIN	dbo.op_rows AS r ON r.op_id = w.op.id 
WHERE		o.Type = 1
GROUP BY	w.Group_ID,
		w.Offer_ID,
		w.Product_ID

N 56°04'39.26"
E 12°55'05.63"

jh_sql
Starting Member

24 Posts

Posted - 2011-07-11 : 02:47:43

That opened my eyes, Thank you! I finally got it now :D

As i get average & count from OP, there is possibly hundreds needles joins to op.rows, i just added new inner query to get few fields from op_rows that i need to OP - table result, and only after that ill do aggregate functions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources