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 Monday, August 24, 2009 9:41 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 9, 2013 10:10 AM
Points: 167, 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.
Post #776113
Posted Monday, August 24, 2009 1:49 PM
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
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.
Post #776278
Posted Monday, August 24, 2009 1:54 PM
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
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.
Post #776281
Posted Monday, August 24, 2009 2:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 7, 2014 8:04 AM
Points: 281, Visits: 82
thanks Michael Ross, I forgot about the DBA. I didn't think about that one, since we don't have one
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.



Post #776291
Posted Monday, August 24, 2009 2:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 7, 2014 8:04 AM
Points: 281, Visits: 82
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.



Post #776298
Posted Monday, August 24, 2009 10:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 7, 2014 7:51 PM
Points: 42, Visits: 351
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;



Post #776428
Posted Tuesday, August 25, 2009 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 1:15 PM
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...
Post #776490
Posted Tuesday, August 25, 2009 8:04 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
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.
Post #776710
Posted Tuesday, August 25, 2009 8:10 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
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.
Post #776718
Posted Tuesday, August 25, 2009 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 7, 2014 8:04 AM
Points: 281, Visits: 82
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..



Post #776722
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse