Re: Slow Query Issue In SP
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/07/05
- Next message: Hugo Kornelis: "Re: IN(@variable) clause and Table Data Type variable"
- Previous message: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- In reply to: Paul Moore: "Slow Query Issue In SP"
- Next in thread: Chris2: "Re: Slow Query Issue In SP"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 08 Feb 2005 00:47:37 +0100
On 7 Feb 2005 14:00:41 -0800, Paul Moore wrote:
>I have an issue that I keep banging my head on. Basic business problem
>is that I need to return all records in a given time period and any
>records which have not been completed. There are several different
>queries inside of the SP which all have the same basic problem with the
>or statement.
>
>
>Select t1.col1, t3.col2, count(*), sum(col3)
>>>From table1 t1 inner join table2 t2 on (t1.pkey = t2.pkey)
> inner join table3 t3 on (t2.col1 = t3.col1 and t2.col2 = t3.col2)
>where (t1.date <= @datevar or completed = 0)
>group by t1.col1, t3.col2
>
>That check for the completed across the table takes about 3 seconds,
>compared to a couple ms without it. I have tried breaking this out
>into different queries and indexed the heck out of the table all to no
>avail.
>
>Because of the redundant way this has to be called the SP is running
>almost a minute and growing as the table grows. Any ideas?
Hi Paul,
In what table live the completed column?
Assuming it's in table1, see if this performs better:
SELECT t1.col1, t3.col2, COUNT(*), SUM(col3)
FROM (SELECT pkey, col1
FROM table1
WHERE date <= @datevar
UNION
SELECT pkey, col1
FROM table1
WHERE completed = 0) AS t1
INNER JOIN table2 AS t2
ON t2.pkey = t1.pkey
INNER JOIN table3 AS t3
ON t3.col1 = t2.col1
AND t3.col2 = t2.col2
GROUP BY t1.col1, t3.col2
You could also replace the derived table in the above query with one of
the following alternatives:
(SELECT pkey, col1
FROM table1
WHERE date <= @datevar
AND completed <> 0 -- Assumes completed can't be NULL
UNION ALL
SELECT pkey, col1
FROM table1
WHERE completed = 0) AS t1
or
(SELECT pkey, col1
FROM table1
WHERE date <= @datevar
UNION ALL
SELECT pkey, col1
FROM table1
WHERE date > @datevar -- Assumes date can't be NULL
AND completed = 0) AS t1
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Hugo Kornelis: "Re: IN(@variable) clause and Table Data Type variable"
- Previous message: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- In reply to: Paul Moore: "Slow Query Issue In SP"
- Next in thread: Chris2: "Re: Slow Query Issue In SP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
