Thousands of "sp:starting" events for one SELECT in a proc

Question

Post reply

Thousands of "sp:starting" events for one SELECT in a proc

PennCreativeGroup

Valued Member

Points: 62
More actions
October 17, 2007 at 10:34 pm

#375914

Hello everyone,
I've encountered an odd problem with a SQL 2000 stored proc. It only has one relatively simple SELECT statement, with no temp tables created or referenced, and it's been running very slowly. Here is the pseudo-code:
SELECT [fields]
FROM [tables with several left outer joins]
WHERE [several clauses, including an EXISTS that uses a subquery]
GROUP BY ....
ORDER BY...
The main table being referenced only has about 20000 rows, but the proc is taking anywhere from 3-30 seconds to return the dataset. I decided to execute it while running Profiler to see if I could find anything. Well, I did!
Everytime the proc executes, the Profiler log is filled with (literally) thousands of SP:Starting events, and the TextData of each one is the SELECT statement from the proc, and not the "exec procname" statement. At least now I know why the proc is slow to return data, but can anyone shed some light on why it would behave this way? I've written lots of procs but I've never seen one do this before.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004374 More actions · Answer 1

The EXISTS with the sub-query, probably a correlated one at that, needs to be replaced with a join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PennCreativeGroup Valued Member Points: 62 More actions · Answer 2

Thanks for the quick reply, Jeff.

I was able to track down the cause... I forgot to mention that one of the select fields is derived using a udf (it takes 5 varchar fields as parameters and returns a varchar field). It seemed innocuous enough, but when I commented it out and reran the proc, everything was back to normal (one sp:starting instead of 000's).

Thanks again!

Jeff Moden SSC Guru Points: 1004374 More actions · Answer 3

Yeaup... that would also be one of the problems...

Change the EXISTS to a regular join on a "derived" table or a normal table and see if things don't pick up a bit as well... might not but well worth the try if it does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004374 More actions · Answer 4

PS... can you be a bit more specific on what you're UDF does and post the code? UDF's are inherently NOT set-based... might be able to show you a work around.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PennCreativeGroup Valued Member Points: 62 More actions · Answer 5

The purpose of the udf was to take a person's name, stored in separate fields, and concatenate it into one field in the format [Last Name] [Suffix], [Prefix] [First Name] [MiddleName]. This is legacy code I inherited that is being used to populate pages in a ASP.NET 2.0 web app.

My solution was to ditch the udf altogether, return the fields as-is and let the ASP code handle the concatenation.

(Since it isn't my code, I don't think I'm allowed to post it anywhere, but suffice it to say -- it's a mess. 😉 )

Thanks again for your help-

John

Jeff Moden SSC Guru Points: 1004374 More actions · Answer 6

Sounds like a pretty good plan... GUI is the place where such formatting should occur.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)