Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Theoretical Performance Issue Handling


Theoretical Performance Issue Handling

Author
Message
R. C. van Dijk
R. C. van Dijk
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 87
Comments posted to this topic are about the item Theoretical Performance Issue Handling



Raju Lalvani
Raju Lalvani
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
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.
Barrie Holloway
Barrie Holloway
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
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????
R. C. van Dijk
R. C. van Dijk
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 87
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!



ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 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
Raju Lalvani
Raju Lalvani
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
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.
scott mcnitt
scott mcnitt
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 429
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".
Drikus Roux
Drikus Roux
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 236
Quite correct, we tend to “pluck the pond from under the duck”:-P 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 theoreticallyHehe.
wbrianwhite
wbrianwhite
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.
R. C. van Dijk
R. C. van Dijk
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 87
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search