Argh - So Frustrated
-
So, I probably shouldn't post this because knowing my luck our DBA follows this forum. But I'm so unbelievably frustrated right now that I'll go against my better judgement. Allow me to vent:
We have a stored procedure that is a simple join between two tables. In production, it takes anywhere from a few milliseconds to 30 seconds. Quite a huge range. This is a stored procedure being used by an application front-end which means our users can expect instantaneous results or they can expect to go get a cup of coffee. The query plan shows that it's using proper indexes. The problem seems to be the source of the data. Sometimes the server grabs it from disk which is really slow, other times the data is cached.
So I set up a meeting with our DBA to discuss and to pinpoint what the problem is. During that meeting my intelligence was insulted more times than I can count. It started on a bad note when he told me that we needed to determine the "base level of service" expected from the stored procedure. Fair enough... I answered that it should return in under 100 milliseconds. But apparently a straight answer isn't what he was looking for. So he played that angle for a while with all kinds of theory and assorted BS. Then another developer joined the meeting and suggested that run times might differ based on the parameters that were being passed to the stored procedure. You think? Or maybe the database usage is effecting the performance. Maybe there are table locks or something. Thank you Mr. Obvious.
Now, none of those points are particularly invalid, but that was the whole point of the meeting!!! Rather than approaching it from the standpoint of "maybe we can find trends in the performance of the stored procedure and tie it into periods of high usage within the database" it was just pointing out the obvious as if I didn't really understand what was going on.
The meeting eventually degraded into absolute disdain for one another. The DBA was asking if I even understood what an index was and where he was coming from. I pointed out that I understood indexes perfectly well but that he was missing the whole point of the meeting which was to determine what the problem was whereas he couldn't even admit that there was a problem. Apparently performance fluctuations between a few milliseconds and thirty seconds are perfectly acceptable to him.
By the way, before you assume I'm the moron in this situation, let me assure you that I have SQL certifications and have worked in a backup DBA roll before. I wouldn't consider myself a super-duper database expert, but I can certainly hold my own. I also went into that meeting with a very open mind. I knew there was a performance problem and I suspected that I knew the cause but I was open to any explaination. On the other side of the coin is my company where none of the databases have primary keys because they're bad (?!?!), very few databases have referential integrity built into them (i.e. foreign keys) and the overall database design is atrocious.
Argh. Rant over. Thanks for listening.
-
Now that you're all vented away
..Search this site for parameter sniffing and how it affects procs just like you described. Basically you have a plan created for the proc that doesn't work for some set of parameters. All you have to do is force the server to chose the right plan. You do that by copying the input parameters to local variables and use those variables in the query itself (so the server can no longer sniff them on the creation of the (bad) plan).
-
You know what? I was going to reply to that but I think I'll just skip it. Thanks.
-
Didn't mean to insult you in any way!
What did I say wrong?
-
hi schroeder.
i feel your pain,been there!
there can be a big unhealthy, unpleasant, and totally unnecessry divide between dbas and developers. it doesnt help anybody. try to sort it out, and be nice (even if you really, really want to murder him slowly).
easy
k
-
you might consider pinning the tables that the procedure is calling; If the data is pinned and in memory, you'd avoid the wait time while it goes to disk; I don't know how big the table is, but it might be an option.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
First off, I owe RGR'us an apology because I responded while still frustrated. Parameters are, like, the first thing I checked, but my response to you was inappropriate. Sorry. =(
The problem is almost certainly disk access. Regrettably, our database environment is horrendous and we've probably pinned just about everything that was possibly can. This particular query is joining two tables one of over 4 million rows and the other with over 100K. The 100K one is already pinned, but the 4M one is just too big.
I agree that the divide between developers and DBAs is very deep. I've always been a uniter of the two, however, because I've worked on both sides. Frankly, I think developers are usually the ones at fault because we tend to blame all of our issues on others.
-
Well, other than pinning, making sure indexes are appropriate, query hints, or identifying how the parameters maybe used differently, the only other option is making your own cross reference (index).
That is, build a smaller lookup table that has only the columns that you're looking for, then index that one. Then figure out a method for keeping it up to date (like if the big table is really only updated rarely, then a nightly job recreating your lookup table may be sufficient). That method is not elegant but I have used it in massively ugly databases where I had not the authority to make the necessary application/database changes necessary.
(Oh, yeah, one more: possibly, there's the multi-step process, where in you break your query into multiple steps storing intermediate results in temp tables, thus doing the work of the query engine but within your explicit control.)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply