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 123»»»

Theoretical Performance Issue Handling Expand / Collapse
Author
Message
Posted Sunday, August 23, 2009 6:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 25, 2014 2:40 AM
Points: 281, Visits: 84
Comments posted to this topic are about the item Theoretical Performance Issue Handling


Post #775662
Posted Sunday, August 23, 2009 11:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:52 AM
Points: 23, Visits: 154
The amount of data shown to a user needs to be restricted not only from a performance point of view, but also from a user productivity view point. Many users ask for wild card searches that return large amounts of data when what the user is looking for a single entity on which to operate. The manual sifting of data by means of next page is a painful experience, its like looking for a needle in a haystack.
The other is that we give users a lot of fields on which he can search. In order to achieve this we take recourse to writing a challenging dynamic SQL query. In real life the search is carried out 99.99 % of time on two or three fields, does it make sense to achieve flexibility we sacrifice so much performance.
Post #775779
Posted Monday, August 24, 2009 12:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 24, 2010 11:30 PM
Points: 491, Visits: 74
And never, ever, forget to have the changes in the specifications signed off when you remove/alter some of the users 'absolutely critical' (but of little value) functionality. Even more important if they have changed their mind on its importance part way through the process.

Or am I just overly skeptical????
Post #775790
Posted Monday, August 24, 2009 1:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 25, 2014 2:40 AM
Points: 281, Visits: 84
The approach that the customer is always right, is totally wrong. Make them think they are right whilst feeding them with your own ideas is the way to go. Programmers (together with consultants) know best. Make them think your ideas are their own and yes, make them sign of on them!


Post #775814
Posted Monday, August 24, 2009 1:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
R. C. van Dijk (8/24/2009)
The approach that the customer is always right, is totally wrong. Make them think they are right whilst feeding them with your own ideas is the way to go. Programmers (together with consultants) know best. Make them think your ideas are their own and yes, make them sign of on them!


"But that's what you suggested to me I needed, and it sounded good! That does it, your product is crap and I'm ripping it out and replacing it with this other product."

Doesn't always work.


Random Technical Stuff
Post #775815
Posted Monday, August 24, 2009 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:52 AM
Points: 23, Visits: 154
Not many companies have the luxury of dog fooding the software that has been developed to understand the users perspective specially from a usability perspective. We provide a whole set of features which are a pain to use. For example many applications do not allow a date to be entered directly, the user is forced to use a painful calender control. The user interface should be designed keeping in view the users familiarity with software usage. It may perhaps worthwhile to provide training rather than give fancy mechanisms for the user to input data.
Post #775826
Posted Monday, August 24, 2009 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 11:28 AM
Points: 41, Visits: 383
Maybe "The Art of Theoretical Performance Issue Handling" would be a good title.

Data volume is not the only thing that can change over time (or from DEV environment to PROD). You may have the best query you can create, "in theory", and it can timeout in production where there are concurrent users, lock escalations, I/O bottlenecks, wrong color socks (my theory as to why something works one day and not the next).

I like your list of actions taken. Swearing does seem to help and in theoretical SQL it has been proven not to hurt.

I would add one thing: the click-to-eyeball test. The developer runs the query in Query Analyzer and it takes 1 second. A "really good" developer will run it a few times, clearing the cache each time to get the average of 1 second. At this point one must pronounce it "working good in theory".
Post #775935
Posted Monday, August 24, 2009 6:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 1:29 AM
Points: 22, Visits: 180
Quite correct, we tend to “pluck the pond from under the duck” or better “go on a wild goose chase” when confronted with DB performance problems, instead of pondering the root cause. It is well worth it to consider the “other” factors, e.g. time changes as well as environmental adjustments, as potential causes before we fiddle the code. Uhm, theoretical prognosis, the prognosis theoretically.
Post #775940
Posted Monday, August 24, 2009 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152
Actually, the first thing I would do to debug it would be to have the stored proc run in the actual environment in sql query editor (assuming it's not a data modification proc) and have the actual execution plan included in the query results. If it is data modification, you need to run it in a similar backup of the prod database, so you have similar table sizes and statistics. 8 out of 10 times, one hugely expensive part of the query will jump out at you. This is the quickest way to find out where the bottleneck is, because until you identify that, any 'optimizations' you do will be wasted.
Post #776014
Posted Monday, August 24, 2009 8:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 25, 2014 2:40 AM
Points: 281, Visits: 84
I totally agree that this is what should be done first! That is even one of the biggest reasons why we always want access to the production systems of our clients!
However, taking (only) this approach causes you to miss out on any possible functional issues there might be (e.g. to much data being shown or processed by the client(s) ).
Taking a step back and looking at the big picture forces you to look at all possible sides of the problem. Taking a techie approach is just not always the way to go.



Post #776020
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse