General Performance Question

realJSOP

How bad would a stored proc have to be that could not return more than 3000 records before timing out, regardless of how big your timeout value was? We're talking SQL Server 2005 running on an Itanium server...

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Lost User

John Simmons / outlaw programmer wrote:

regardless of how big your timeout value was?

Even with a demanding query you'd have a hard time with timing out "regardless how big the value". I'd go for an endless loop in a cursor. Can you count the cursors, temporary tables and the udf's? :-\

I are Troll :suss:

realJSOP

Well, it does eventually come back (that's how they know about the 3000 record limit). An endless loop wouldn't come back at all...

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Mycroft Holmes

John Simmons / outlaw programmer wrote:

How bad would a stored proc have to be

Really crappy, I've seen this where SSMS performs the query in seconds and the UI takes ages. Is this the case? Have you looked at the execution plan, it usually suggests indexes if there are glaring requirements. Otherwise just look for the most expensive operation and try and optimise it.

Never underestimate the power of human stupidity RAH

Lost User

The latest SQL Management Studio has a cool query-analyzer that even proposes indexes. It might help in tracking down the worst offenders - that way you don't have to plow through all of them. What's it doing? "Just" some TSQL, or is it calling a webservice and writing the results to a networkdrive? --edit-- What MyCroft said, and I'm still curious to what it's doing :)

I are Troll :suss:

realJSOP

I dunno - the boss says it times out, shrugs his shoulders, and we all laugh a little at the problem.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Mycroft Holmes

Ok so is this the same proc you are bitching about in the Lounge - and you need to ask the question.

Never underestimate the power of human stupidity RAH

realJSOP

No (at least I don't think so). I'm not bitching about it either. Here, I asked a question. In the Liunge, I merely stated my amazement at somethig regarding the job.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Mycroft Holmes

So how did the execution plan investigation go? I just noted the coincidence of the enquiry here and the Lounge entry and wondered if the very slow proc was 1500 lines of unformatted tsql which would probably account for the slow response :-D

Never underestimate the power of human stupidity RAH

David Skelly

I'm not entirely sure what sort of response you're looking for here. How bad would a stored proc have to be? Well, I guess that depends on what it does. If it's a simple select, then it would have to be pretty bad. If it's a massive stored proc that does a zillion different things and only gets run once a year then I guess that it might not be badly written, it might just be slow and time-consuming, and you'll either have to live with it or find a different approach (don't do it all in one stored proc, break it down into manageable chunks, something like that). I don't know that anyone will really be able to give you much advice beyond what you've already got from the others, certainly not without knowing more detail about the stored proc.

Tim Carmichael

Is it a stored procedure issue or a data issue? We have a vendor supplied system where 90+ percent of the data is in one table. We also have a user requested report that requires us to perform an inner join on said table with itself. Tim

realJSOP

I don't really know any of the details. The only things I know are that "somewhere" during the process of doing whatever they do in the database, it times out and they can only get about 3000 records before it does. I was just looking for a starting point as to where to look. What makes it harder to be more specific is that they don't know what part of the process is timing out. Like I said, they just say it times out, they shrug, and everyone (except me) just kinda smiles about it. The front-end is written in VB, so that should give you an idea of what I'm dealing with.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

realJSOP

I don't know, because none of the people that should know have any idea what's causing it or at what point in the process it's happening.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

David Skelly

It can be very hard to track these things down. We once had a stored proc that ran a query, returned some data, the front end then set a flag and saved the data back again. In development it completed within seconds. In live production use, it took 15 minutes. When we looked into it, it turned out that the stored proc was actually running very quickly. So was the front-end processing to set the flag. All the time was being taken transferring the data across the network, because in the live database each row included a massive XML document that took forever to marshall off the database server, move it over the network and then marshall it all back for the front-end to receive. When the developer first tested it, the XML documents he used were teeny-tiny little things so it all ran quickly and no-one spotted the problem. We rewrote the application so that the update was done in the stored proc without returning anything to the front end, and it finished in less than 10 seconds. When we released it to the users, we had people phoning us up saying, "I don't think this did anything, it was too quick..."