I am creating a stored procedure to send emails (with xp_sendmail, I think)
based on certain conditions. This is my logic:
I need to loop through all of the Table1 records.
if Table2 exists for Table1 and Table2.column='T'
send variation 1 of email
else if table2 exists for table 1 and Table2.column='F'
send variation 2 of email
else if table3 exists for table 1
send variation 3 of email
Should I use a cursor to loop through the Table1 records? Or should I join
Table1 and Table2/Table3 and not have a top level query? I'm thinking I
should not use a cursor.
My second problem is this. Depending on the email variation I need to loop
through some records and concatenate their values. Do I need a cursor for
that? Or do I have other options?
Thanks for any help, I really appreciate it.Nick
DECLARE @.EmailName VARCHAR(100),@.userid VARCHAR(20)
IF EXISTS (SELECT * FROM Table2 JOIN Table1 ON Table2.pk=Table1.pk AND
Table2.column='T')
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
SET @.userid='john,arie,alex'
SELECT @.EmailName=@.EmailName+COALESCE(Emailadd,
'') +',' FROM users where
CHARINDEX(',' + userid + ',',','+ @.userid +',')>0 and EmailName IS NOT
NULL
SET @.EmailName=LEFT(@.EmailName,LEN(@.EmailNam
e)-1)
--Send emails
I'm currently unable to test it but I'm sure it gives you an idea.
"Nick" <nickfinity@.nospam.nospam> wrote in message
news:48C35529-4878-455F-9DCC-F97057A4A5B8@.microsoft.com...
>I am creating a stored procedure to send emails (with xp_sendmail, I think)
> based on certain conditions. This is my logic:
> I need to loop through all of the Table1 records.
> if Table2 exists for Table1 and Table2.column='T'
> send variation 1 of email
> else if table2 exists for table 1 and Table2.column='F'
> send variation 2 of email
> else if table3 exists for table 1
> send variation 3 of email
> Should I use a cursor to loop through the Table1 records? Or should I join
> Table1 and Table2/Table3 and not have a top level query? I'm thinking I
> should not use a cursor.
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
> Thanks for any help, I really appreciate it.|||>From the limited information you've posted, it sounds like a CASE
statement is what you're looking for. In general, the only time I ever
find that I must use a cursor is when I have to call a stored procedure
on each value in a resultset. They seem to be much more useful to me in
ad hoc situations than in deployed solutions.
CASE Example in an UPDATE (sorry for the poor formatting):
UPDATE <tablealias1>
SET EmailAddress = CASE WHEN <condition1> THEN <expression>
WHEN <condition2> THEN <expression> ELSE <expression> END
FROM Table1 <tablealias1> INNER JOIN Table2 ON <...>
WHERE <condition>
Showing posts with label cursors. Show all posts
Showing posts with label cursors. Show all posts
Sunday, March 25, 2012
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
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
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
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
Subscribe to:
Posts (Atom)