Prev: Copy Data (attempt 2)
Next: Problem prefixing object with dbo. when using system stored procedures
From: PVG on 15 Jul 2008 10:06
Hi,
The following piece of SQL takes more than 2 minutes in 2005 whereas it
takes less than 10 seconds in SQL 7 -
--ORIGINAL
IF (
SELECT COUNT(*)
FROM OrderRecords o JOIN Batches b
ON o.BatchID = b.ID JOIN ProcessGateways pg
ON o.GatewayID = pg.GatewayID AND pg.ProcessID = @lngProcessID
WHERE o.RecordType = @strRecordType AND
(o.Status = 0 OR o.Status = 10) AND b.Status = 1 ) = 0

BEGIN
-- There are no orders; return an empty recordset
SELECT *
FROM OrderRecords o JOIN Batches b
ON o.BatchID = b.ID
WHERE o.ID = 0
RETURN
END

The above IF condition had to be re-written in SQL 2005 as follows to return
to the original performance -

--REVISED
declare @ret int
select @ret = COUNT(*) FROM OrderRecords o JOIN Batches b
ON o.BatchID = b.ID JOIN ProcessGateways pg
ON o.GatewayID = pg.GatewayID AND pg.ProcessID = 32
WHERE o.RecordType = '001' AND
(o.Status = 0 OR o.Status = 10) AND b.Status = 1

IF ( @ret = 0 )
BEGIN
-- There are no orders; return an empty recordset
SELECT *
FROM OrderRecords o JOIN Batches b
ON o.BatchID = b.ID
WHERE o.ID = 0
RETURN
END

Can someone please explain why the vast difference in timings between the 2
selects in 2005.

Any help greatly appreciated.
Thanks
Venu

From: Roy Harvey (SQL Server MVP) on 15 Jul 2008 10:40
Radical changes in performance generally come down to differences in
the execution plans. I'm guessing that the optimizer in 2005
converted the first test into an EXISTS test rather than calculating
the full count process, and it happened that the execution plan was
far from optimal. If there are indexes on o.Status or b.Status, for
example, it might have chosen to try to use such an index when in fact
it was not selective enough.

The optimizer will never be perfect, and there will always be cases we
have to deal with. Such issues come up when upgrading on a fairly
regular basis. We would never think twice about them when they come
up while writing an application because we would simply write the
query a bit differently until performance was acceptable, and not even
remember there was an issue.

Roy Harvey
Beacon Falls, CT

On Tue, 15 Jul 2008 07:06:05 -0700, PVG
<PVG(a)discussions.microsoft.com> wrote:

>Hi,
>The following piece of SQL takes more than 2 minutes in 2005 whereas it
>takes less than 10 seconds in SQL 7 -
>--ORIGINAL
>IF (
> SELECT COUNT(*)
> FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID JOIN ProcessGateways pg
> ON o.GatewayID = pg.GatewayID AND pg.ProcessID = @lngProcessID
> WHERE o.RecordType = @strRecordType AND
> (o.Status = 0 OR o.Status = 10) AND b.Status = 1 ) = 0
>
>BEGIN
> -- There are no orders; return an empty recordset
> SELECT *
> FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID
> WHERE o.ID = 0
> RETURN
>END
>
>The above IF condition had to be re-written in SQL 2005 as follows to return
>to the original performance -
>
>--REVISED
>declare @ret int
>select @ret = COUNT(*) FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID JOIN ProcessGateways pg
> ON o.GatewayID = pg.GatewayID AND pg.ProcessID = 32
> WHERE o.RecordType = '001' AND
> (o.Status = 0 OR o.Status = 10) AND b.Status = 1
>
>IF ( @ret = 0 )
>BEGIN
> -- There are no orders; return an empty recordset
> SELECT *
> FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID
> WHERE o.ID = 0
> RETURN
>END
>
>Can someone please explain why the vast difference in timings between the 2
>selects in 2005.
>
>Any help greatly appreciated.
>Thanks
>Venu
From: TheSQLGuru on 15 Jul 2008 11:09
1) Check to see if IF NOT EXISTS (Select * ...). isn't more efficient on
both platforms.

2) When you upgraded the database to 2005, did you run update statistics on
ALL objects using FULL SCAN option? that is a MANDATORY step post-upgrade.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"PVG" <PVG(a)discussions.microsoft.com> wrote in message
news:53C6CC61-3331-48D4-A9D1-9C31E41DCA46(a)microsoft.com...
> Hi,
> The following piece of SQL takes more than 2 minutes in 2005 whereas it
> takes less than 10 seconds in SQL 7 -
> --ORIGINAL
> IF (
> SELECT COUNT(*)
> FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID JOIN ProcessGateways pg
> ON o.GatewayID = pg.GatewayID AND pg.ProcessID = @lngProcessID
> WHERE o.RecordType = @strRecordType AND
> (o.Status = 0 OR o.Status = 10) AND b.Status = 1 ) = 0
>
> BEGIN
> -- There are no orders; return an empty recordset
> SELECT *
> FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID
> WHERE o.ID = 0
> RETURN
> END
>
> The above IF condition had to be re-written in SQL 2005 as follows to
> return
> to the original performance -
>
> --REVISED
> declare @ret int
> select @ret = COUNT(*) FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID JOIN ProcessGateways pg
> ON o.GatewayID = pg.GatewayID AND pg.ProcessID = 32
> WHERE o.RecordType = '001' AND
> (o.Status = 0 OR o.Status = 10) AND b.Status = 1
>
> IF ( @ret = 0 )
> BEGIN
> -- There are no orders; return an empty recordset
> SELECT *
> FROM OrderRecords o JOIN Batches b
> ON o.BatchID = b.ID
> WHERE o.ID = 0
> RETURN
> END
>
> Can someone please explain why the vast difference in timings between the
> 2
> selects in 2005.
>
> Any help greatly appreciated.
> Thanks
> Venu
>


 | 
Pages: 1
Prev: Copy Data (attempt 2)
Next: Problem prefixing object with dbo. when using system stored procedures