Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow running query Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 8:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:19 PM
Points: 164, Visits: 231
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?
Post #1398992
Posted Thursday, December 20, 2012 8:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 2,422, Visits: 7,442
Without way more information than that (see this article for what is missing), I'd be extremely surprised if anyone is able to give you an answer that doesn't just mean "It depends..."


Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1398995
Posted Thursday, December 20, 2012 8:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
... 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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1399011
Posted Thursday, December 20, 2012 3:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 36,777, Visits: 31,232
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1399118
Posted Friday, December 28, 2012 1:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:49 AM
Points: 21, Visits: 312
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.
Post #1401045
Posted Friday, December 28, 2012 1:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:19 PM
Points: 164, Visits: 231
Thank you very much for the suggestions - I will take a look.
Post #1401047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse