Thursday, March 22, 2012

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

No comments:

Post a Comment