SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by R. C. van Dijk / Theoretical Performance Issue Handling / Latest Posts

RE: Theoretical Performance Issue Handling

L' Eomot Inversé — Thu, 29 Oct 2009 04:50:55 GMT

[quote][b]R. C. van Dijk (8/24/2009)[/b][hr]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![/quote]I thought the article was very good until I came across [quote]As a project manager or even a developer, you know better than the end user. They don't think so, but most of the time you have a better overview of the system and clients work to be done.[/quote]and then I began to wonder. Then the above remark compounded things.Most DBAs, project managers, programmers, consultants can't be polymaths (except maybe for the odd genius here and there). So unless the applications happen to be in only one area and you've learnt that area thoroughly, you probably don't know what's required better than the end user - you probably know and understand [i]a lot less[/i] than he does. Of course if the person setting the requirement is not an end user but a "management consultant" or an "efficiency consultant" then you possibly do know far more than him/her, because he is pontificating about a field (the application domain) that is not his/her claimed area of expertise (management, or efficiency). [i]In the IT industry such consultants have a terrible reputation - and you are advocating precisely the sort or arrogant and incompetent attitude that is largely responsible for that reputation.[/i]Yes, sometimes you can see that a stated requirement could have dire consequences - and then you have to work with the end user to find whether it's a real requirement (in which case you have to give hime the bad news and see where things go from there) or a badly expressed requirement: but when your real end user (a lawyer, perhaps, or an organic chemist) says something like "I have to demonstrate that this set of information was shown simultaneously on a single screen for verification in order to comply with Amcedonia's such and such regulation" or "I need to see these characteristics of the molecule together to determine that it will be safe in contact with ethylene" you had better not pretend that you know better.

RE: Theoretical Performance Issue Handling

mb97 — Wed, 26 Aug 2009 15:45:23 GMT

[quote][b]R. C. van Dijk (8/26/2009)[/b][hr]I totally agree. The complex queries I was talking about perform a lot of different actions, complex business logic is implemented in SP's. These kind of statements (seperate steps/parts of the SP) have inputs of which the domains are well defined. The individual parts of the resulting execution plan are stable. That is, until the described scenario happens. This will through everything of and a rethink of the rules, design etc is in place.The fact that the inputs can mess up the execution plan, means that the domains of the input are not well defined.If defining the domains is impossible, you are writing a system in which (parts of) the data is can not be defined upfront and you are probably better of rethinking the use of a relational database system (and a raise, because writing this is hell).In a stable situation, the resulting plan should always be predictable and stable. If this was not the case, optimising would be useless.[/quote]This is actually incorrect. If you are dealing with very large databases, with many, many users, there can be a large variance in the size of the pie that users are interested in, and I have seen situations where, for example, a clustered index scan performed much better for a query returning a very large number of rows while a nonclustered index lookup performed better for a user concerned with a much smaller set of data. It has nothing to do with the definition of domains, which are well understood. It seemed to me by your own admission that the exec plans were not stable, because they became suboptimal as the amount of data increased. This is not really that different from what I was talking about, except that both conditions exist simultaneously: the exec plan to deal with a small amount of data is not the same as the one to deal with a large amount of data from the same table. Let's define inputs as parameters supplied to sp_executesql, for example, because that's a clear situation where inputs can change without causing a new execution plan to be generated. So, optimizing can be useless in the conventional sense, because there is no optimal. Well, there is an optimal, but it cannot be perfect.

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Wed, 26 Aug 2009 13:54:41 GMT

I totally agree. The complex queries I was talking about perform a lot of different actions, complex business logic is implemented in SP's. These kind of statements (seperate steps/parts of the SP) have inputs of which the domains are well defined. The individual parts of the resulting execution plan are stable. That is, until the described scenario happens. This will through everything of and a rethink of the rules, design etc is in place.The fact that the inputs can mess up the execution plan, means that the domains of the input are not well defined.If defining the domains is impossible, you are writing a system in which (parts of) the data is can not be defined upfront and you are probably better of rethinking the use of a relational database system (and a raise, because writing this is hell).In a stable situation, the resulting plan should always be predictable and stable. If this was not the case, optimising would be useless.

RE: Theoretical Performance Issue Handling

mb97 — Wed, 26 Aug 2009 13:40:15 GMT

[quote][b]R. C. van Dijk (8/25/2009)[/b][hr][quote][b]wbrianwhite (8/25/2009)[/b][hr]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.[/quote]Non taken:-)However I wasn't talking about breaking up 1 SP into many. The SP's I was talking about consist of a lot of seperate steps of data processing. Analysing these seperately is the only way to go. Writing simple selects should not be a problem if you call yourself a developer. I write the standard selects blindfolded with the 'best' execution plan. It are the complex procedures which cause the problems I talk about.[/quote]Breaking a complex query into steps with intermediate temp tables usually results in more IO. I think the point is that very complex selects can cause unpredictable performance problems (or nearly impossible to predict), and the problems can appear at what appear to be random times. One of the reasons for the randomness can simply be that an execution plan created for one set of inputs can disastrous for a different set of inputs (it's frustrating to see a DBA "solve" one of these problems by recompiling a procedure), or conversely, an exec plan may be good until some event forces the system to create a new one and suddenly performance goes to heck. Other problems can be even more unpredictable, such as the number of processors available at the time of execution, or how many users are doing which things. The more complex the query, the more possibilities for an exec plan; complex procs can be hard to analyze because they there is so much noise to filter out to find the problem, but a complex query can be hard simply because the query and its inputs [i]are [/i]complex. Sometimes the best solution for one set of inputs is theoretically the worst solution for a different set, and you have to recognize that the best overall solution degrades performance under some circumstances.

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Tue, 25 Aug 2009 08:24:52 GMT

[quote][b]wbrianwhite (8/25/2009)[/b][hr][quote]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.[/quote]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.[/quote]Non taken:-)However I wasn't talking about breaking up 1 SP into many. The SP's I was talking about consist of a lot of seperate steps of data processing. Analysing these seperately is the only way to go. Writing simple selects should not be a problem if you call yourself a developer. I write the standard selects blindfolded with the 'best' execution plan. It are the complex procedures which cause the problems I talk about.

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Tue, 25 Aug 2009 08:19:16 GMT

[quote][b]wbrianwhite (8/25/2009)[/b][hr]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.[/quote]I meant 1000... little typo;-)..

RE: Theoretical Performance Issue Handling

wbrianwhite — Tue, 25 Aug 2009 08:10:09 GMT

[quote]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.[/quote]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.

RE: Theoretical Performance Issue Handling

wbrianwhite — Tue, 25 Aug 2009 08:04:54 GMT

[quote][b]R. C. van Dijk (8/24/2009)[/b]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. [/quote]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.

RE: Theoretical Performance Issue Handling

m_voinea — Tue, 25 Aug 2009 02:13:14 GMT

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...

RE: Theoretical Performance Issue Handling

emmchild — Mon, 24 Aug 2009 22:07:44 GMT

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;

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Mon, 24 Aug 2009 14:37:41 GMT

[quote][b]wbrianwhite (8/24/2009)[/b][hr][quote][b]Michael Ross (8/24/2009)[/b][hr] 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.[/quote]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.[/quote]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.

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Mon, 24 Aug 2009 14:28:17 GMT

thanks Michael Ross, I forgot about the DBA. I didn't think about that one, since we don't have one:-DBut 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.

RE: Theoretical Performance Issue Handling

wbrianwhite — Mon, 24 Aug 2009 13:54:21 GMT

[quote][b]Michael Ross (8/24/2009)[/b][hr] 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.[/quote]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.

RE: Theoretical Performance Issue Handling

wbrianwhite — Mon, 24 Aug 2009 13:49:26 GMT

[quote][b]R. C. van Dijk (8/24/2009)[/b][hr]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.[/quote]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.

RE: Theoretical Performance Issue Handling

Michael Ross-468051 — Mon, 24 Aug 2009 09:41:25 GMT

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.

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Mon, 24 Aug 2009 08:23:41 GMT

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.

RE: Theoretical Performance Issue Handling

wbrianwhite — Mon, 24 Aug 2009 08:14:10 GMT

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.

RE: Theoretical Performance Issue Handling

Drikus Roux — Mon, 24 Aug 2009 06:39:56 GMT

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 theoretically:hehe:.

RE: Theoretical Performance Issue Handling

scott mcnitt — Mon, 24 Aug 2009 06:19:18 GMT

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.[b]I would add one thing: the click-to-eyeball test. [/b]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".

RE: Theoretical Performance Issue Handling

Raju Lalvani — Mon, 24 Aug 2009 01:59:16 GMT

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.

RE: Theoretical Performance Issue Handling

ta.bu.shi.da.yu — Mon, 24 Aug 2009 01:28:58 GMT

RE: Theoretical Performance Issue Handling

R. C. van Dijk — Mon, 24 Aug 2009 01:21:16 GMT

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!

RE: Theoretical Performance Issue Handling

Barrie Holloway — Mon, 24 Aug 2009 00:07:15 GMT

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????

RE: Theoretical Performance Issue Handling

Raju Lalvani — Sun, 23 Aug 2009 23:47:30 GMT

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.

Theoretical Performance Issue Handling

R. C. van Dijk — Sun, 23 Aug 2009 06:28:10 GMT

Comments posted to this topic are about the item [B]Theoretical Performance Issue Handling[/B]