Slow running query

Question

Slow running query

Rog Saber

SSCrazy

Points: 2548
More actions
December 20, 2012 at 8:10 am

#267927

I am a developer and not a DBA. My customers are complaining about how slow my application is running. My application has about 3 queries that are very similar. They contain unions and case statements. When I run a particular query in management studio, sometimes it takes about 10 seconds. So, with three queries you can see that it takes a while in the application.
However, sometimes when I run the query in management studio, it comes back instantly or maybe takes a second or two. I know there are many factors involved in DB performance - lots of people hitting the DB at the same time, etc. I woud think that if there was a major problem with the query that it would always take a while to retrieve but that is not the case.
My question is if my queries actually do come back instantly, do I even spend any more time trying to tweak them or just write it off to a server, other issue and look elsewhere?

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Without way more information than that (see this article for what is missing[/url]), I'd be extremely surprised if anyone is able to give you an answer that doesn't just mean "It depends..." 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

... I woud think that if there was a major problem with the query that it would always take a while to retrieve but that is not the case.
My question is if my queries actually do come back instantly, do I even spend any more time trying to tweak them or just write it off to a server, other issue and look elsewhere?

Actually, it can be quite the opposite: you may have real performance problem with your query, but sometimes it runs very faster. It can be due to caching.

Until you provide your queries, DDL's for tables involved (together with indexes) and current query execution plan, you very unlikely to get any relevant help from anyone here.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

Rog Saber (12/20/2012)
I am a developer and not a DBA. My customers are complaining about how slow my application is running. My application has about 3 queries that are very similar. They contain unions and case statements. When I run a particular query in management studio, sometimes it takes about 10 seconds. So, with three queries you can see that it takes a while in the application.
However, sometimes when I run the query in management studio, it comes back instantly or maybe takes a second or two. I know there are many factors involved in DB performance - lots of people hitting the DB at the same time, etc. I woud think that if there was a major problem with the query that it would always take a while to retrieve but that is not the case.
My question is if my queries actually do come back instantly, do I even spend any more time trying to tweak them or just write it off to a server, other issue and look elsewhere?

You've already answered that question. Performance as perceived by the customer is the only criteria you need to meet and you've already said that your customer is not happy with the performance. Yes, there are many factors that can affect the performance of the server but your customer doesn't care about that. I'd recommend revisiting your code or your customer might not be revisiting you in the future (unless you can actually PROVE that the server is at fault.) 😉

--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)

ngreene SSC-Addicted Points: 461 More actions · Answer 4

A few places to start with troubleshooting.

- Check for blocking in sys.sysprocesses or sys.dm_exec_requests

- launch perfmon and look at Avg. Disk Queue Length.

Dont pay attention to size of number (calculating your ideal range can vary based on disk e.g. SAN vs Local vs # of drives), but you can at least see what the numbers are when the server is running normal. Then see what the values are when the query runs good vs when the query runs slow. Basically get your baseline and compare.

-Size of Results returned. If large amounts of data is returned to web browser it can take time for the application and browser to render the results

-check sys.dm_exec_requests and see what the wait type is. Most likely it will be IO related, but you may get surprised.

Rog Saber SSCrazy Points: 2548 More actions · Answer 5

Thank you very much for the suggestions - I will take a look.