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
Michael Ross-468051
Michael Ross-468051
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 154
I appreciate the overall message of "take a step back". This can be used in a lot of situatations when any problem arrises. Gather information and properly aim to identify the issue before fixing it. I guess thats where the "theoretical" part comes in. Are you fixing the real problem, or are you just chasing your tail?

That's what I find frustrating. There is a lot of over-the-top activities developers seem to engage in the name of "better performance" or "best practices". Rerunning queries multiple times and clearing the cache? Is that REALLY necessary every time? That just leads me to believe that the developer really doesn't know what they are doing, and they are merely hunting and pecking towards a faster result with little regard for data accuracy.

Finally, there were three letters auspiciously missing from the support call example. D-B-A. Where was she in all this? And, no, a developer with t-sql skills does not a DBA make.
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
R. C. van Dijk (8/24/2009)
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.


How are you going to decide if 'too much' data is being shown to the client? And what good is it going to do to say "no longer return these 10 columns" if that doesn't make the query run perceptibly faster? Unless ntext or nvarchar(max) columns are being returned and unused due to a sloppy "select *" (which you should almost never have in production code) trimming columns isn't likely to solve the reported business problem of the query being slow. "Runs too slow" seems an entirely techie problem to me.
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
Michael Ross (8/24/2009)
Rerunning queries multiple times and clearing the cache? Is that REALLY necessary every time? That just leads me to believe that the developer really doesn't know what they are doing, and they are merely hunting and pecking towards a faster result with little regard for data accuracy.

Once you run the query once, all the relevant indexes are likely to be in memory in SQL Server's cache, and every subsequent query may come back in 0.1 seconds, which will totally mask the problem that the first time you run the query it may take 5 seconds to run. It will also completely mask any significant changes in performance you get as a result of changing the query. It is really necessary to do that every time.
R. C. van Dijk
R. C. van Dijk
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 87
thanks Michael Ross, I forgot about the DBA. I didn't think about that one, since we don't have one:-D
But the reactions exactly draw out what I mean. To technical! Having 100 rows of data is in about 80% (or more) of the times to much info. Users are interested in data they can use. Overviews are reports and charts, not in a 'normal' screen. Most of the times, the user wants quick, accurate and easy to read data. As one of the reactions mentions the search. Look into these kind of functionalities. Is it useful? Does is benefit the user?
A programmers (designers) task is not to build what the user wants, but what the user needs. Most of the time the perception of the users of their needs is wrong and based on worst case scenarios. Never ever ever ever cater for worst case scenarios, this will only lead to an unworkable system which is great in 0.5% of the times.
Take a step back, look at what the system is doing, what is needed for the user to perform his/her task.
Take this approach, look at both the functional and technical sides of the stories. This is the task for a developer. If not, you are a typist and no more.



R. C. van Dijk
R. C. van Dijk
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 87
wbrianwhite (8/24/2009)
Michael Ross (8/24/2009)
Rerunning queries multiple times and clearing the cache? Is that REALLY necessary every time? That just leads me to believe that the developer really doesn't know what they are doing, and they are merely hunting and pecking towards a faster result with little regard for data accuracy.

Once you run the query once, all the relevant indexes are likely to be in memory in SQL Server's cache, and every subsequent query may come back in 0.1 seconds, which will totally mask the problem that the first time you run the query it may take 5 seconds to run. It will also completely mask any significant changes in performance you get as a result of changing the query. It is really necessary to do that every time.


Look at the execution plan, look at and think about what you are typing. Having complex queries will benefit from compiling and recompiling a stored procedure. This is because having an overview of a complex query is best described as impossible. Breaking it down in separate parts and reviewing their individual impact on the complete picture.
But again I have to agree with Michael, a lot of times developers don't have a clue what they are doing. 'I need to get such and such data' is the mainstream thought. Not the how, why and when.
As long as the how, why and when are not answered most of the times, the solution is crap and a disaster waiting to happen.



emmchild
emmchild
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 408
SET SOAPBOX_STATUS ON;
The title of programmer/administrator is obsolete. When anything that is built or manufactured in this country the item is subject to design approval and an inspection. Does this process result in perfect outcomes? It depends on the level of craftsmanship, materials used, and the PROCESS that was used for implementation of the original or final design. There has to be some sort of accountability on the part of so-called "developers", "engineers", and "architects". The current state of things has to stop. How about code reviews performed by "certified" independent third parties? How about the same for design? How about hardware/software implementations as well? Salaries go up as a result of the limited number of competent people that can work under these conditions. Efficiency is achieved as a result of focusing effort on doing things methodically and only touching a portion of a solution when it makes good business sense to do such a thing. Let's all haul keister so that we no longer have to work in our grandfather's or our father's IT environments.

SET SOAPBOX_STATUS OFF;



m_voinea
m_voinea
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
Well, I always have the problems like this in my activity (I develop GIS systems on SQL databases). But the problem isn't me or the technical staff. This problem must go to sales, because sales want more and more lines in reports, more layers (from complex analyses) in map windows that the client really needs, and sales insisted that the client must use that unnecessary garbage, because ,,it looks great and shows a big amount of work". So for the start all looks great and shiny (but from my point of view unnecessary complicated) and in time, when client puts more data in system, all will go down, including the interface (which became too hard to understand). So begins the ugly work of redesign interfaces and explain to the customer that the initial approach was simply wrong...
Designing a good/working interface is as important and difficult as designing a good database structure...
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
R. C. van Dijk (8/24/2009)Having 100 rows of data is in about 80% (or more) of the times to much info. Users are interested in data they can use. Overviews are reports and charts, not in a 'normal' screen. Most of the times, the user wants quick, accurate and easy to read data.


It depends ENTIRELY on the application space in which you develop if that statement is accurate. If I'm displaying a list of expense reports to approve, I display the entire list, whether it is 2 or 100. If I'm displaying a list of the prices for customers on a contract, I display all the customers, whether it is 2 or 100. In both those cases the end user wants and needs to see all the items. Those are more 'admin' types of functionality, which I develop more. If I'm showing a list of flights from a to b then I would show only a subset with an option to view more or toggle search criteria - but I am likely to STILL return most of the data to the front end, and do that filtering on the front end in javascript, only significant changes (like airport) would trigger new database calls.

Why would you make all your customers mad by changing a scree to only return 5 results in a query in order to band-aid over the fact that the query is poorly designed and can't deal with returning 100 results in a reasonable time period? If you're talking 1,000+ that is one thing - but 100 is a trivial amount of data to return to the front end, and it should be quick to return it to the front end.
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
Look at the execution plan, look at and think about what you are typing. Having complex queries will benefit from compiling and recompiling a stored procedure. This is because having an overview of a complex query is best described as impossible. Breaking it down in separate parts and reviewing their individual impact on the complete picture.


No offense, but I disagree. One large query that gets all your data at once is likely to be significantly faster than separate stored procedures that grab bits and pieces. The fundamental nature of a SQL query is that you saying "these are the results I want", and SQL Server then figures out the best way to give them to you. When you tell it 5 different things you don't let it optimize for what you really want.
Depends on what you are optimizing. If you are optimizing dev time, then yes, small building block procs are faster. If you are optimizing run time (like the theoretical example) then it is worth spending more dev time to get a faster running query.
R. C. van Dijk
R. C. van Dijk
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 87
wbrianwhite (8/25/2009)
If you're talking 1,000+ that is one thing - but 100 is a trivial amount of data to return to the front end, and it should be quick to return it to the front end.


I meant 1000... little typo;-)..



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