Working out whats actually happening.

27 Aug 2005 12:09 AM

Aussie Rules

Hi,

I have a stored procedure that updates a single table. A bunch of params are
being passed in, and then there is a single update against a table.

The output in isql/w however shows.....

(0 row(s) affected)

(0 row(s) affected)

(17956 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

The query is rather slow, which it should not be as the update using a
clustered index (only one record updates) so should be quick.

My concern that looking at the isql output, there is something happening
that affects 17956 rows(which is every record in this one table). I figure
one of the keys/indexs is doing this, but I can't work out which key it is.

Profiler just shows the SP being called, and lacks the detail.

What is the best way to work out at least which index/key is the problem....

Thanks

27 Aug 2005 12:55 AM

Stu

Usually the output of each line is an indicator of an INSERT, DELETE,
or UPDATE being called. A simple method debugging is to use the PRINT
statement to interject messages to figure out where you are in the
procedure., e.g.:

INSERT INTO someTABLE
SELECT *
FROM someOtherTable

PRINT 'INSERT STATEMENT 1 DONE'

HTH,
Stu

27 Aug 2005 2:25 AM

Dan Guzman

You might check triggers on the table. A poorly written trigger may
inadvertently modify every row in the table due to a missing join to the
inserted or deleted tables.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Aussie Rules" <some***@somewhere.com> wrote in message
news:OScwbupqFHA.2596@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have a stored procedure that updates a single table. A bunch of params
> are being passed in, and then there is a single update against a table.
>
> The output in isql/w however shows.....
>
> (0 row(s) affected)
>
>
> (0 row(s) affected)
>
>
> (17956 row(s) affected)
>
>
> (0 row(s) affected)
>
>
> (0 row(s) affected)
>
>
> (0 row(s) affected)
>
>
> (1 row(s) affected)
>
> The query is rather slow, which it should not be as the update using a
> clustered index (only one record updates) so should be quick.
>
> My concern that looking at the isql output, there is something happening
> that affects 17956 rows(which is every record in this one table). I figure
> one of the keys/indexs is doing this, but I can't work out which key it
> is.
>
> Profiler just shows the SP being called, and lacks the detail.
>
> What is the best way to work out at least which index/key is the
> problem....
>
> Thanks
>