{"id":82065,"date":"2009-10-13T20:57:31","date_gmt":"2009-10-13T20:57:31","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73275"},"modified":"2018-12-12T12:19:40","modified_gmt":"2018-12-12T12:19:40","slug":"sixth-pillar-well-performing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sixth-pillar-well-performing\/","title":{"rendered":"Sixth pillar \u2013 Well Performing"},"content":{"rendered":"<p>I guess eventually I had to admit that performance matters. Whenever I speak, and in all of my books, I try to stress over and over that performance is NOT the only thing. The fact is all too much time is spent trying to make database applications run faster when the real goal should be to architect data structures that solve the problems of the user in a natural way with proper integrity.<\/p>\n<p>But performance is important. Extremely important. Like a fancy sports car, it is the first second thing that a user will notice about the system\u2026The first will be the UI (the paint job).&#160; <\/p>\n<p>The problem is that it isn\u2019t the most important thing. Just like a sports car, the most important thing is that everything works. If the car runs 100 miles an hour in first gear, it doesn\u2019t matter if the seats aren\u2019t bolted in and the steering wheel works backwards.&#160; I initially defined well performing as \u201cGives you answers fast\u201d, and in the context of performance, that is true, but in reality, that is a very simplistic attitude.&#160; Certainly taken out of context, this is definitely NOT a good explanation of well performing. Maybe: \u201cDoes all operations required in a minimal amount of time in consideration of the realities of physics?\u201d&#160; <\/p>\n<p>When considering performance, there are many facets to the problem:<\/p>\n<ul>\n<li><strong>Initial Database design<\/strong> \u2013 Sometimes the reality of the problem to be solved cannot be done quickly, but often it is just poorly designed data structures that get in the way. Designing the database with the concepts of Normalization close at hand is the first step in getting things right. The relational engine of SQL Server is named relational because it likes data to be formed in a relational manner. The concepts of Normalization are the backbone of relational design (sorry, I have to make mention of Normalization every time I blog, I think).<\/li>\n<li><strong>Concurrency \u2013 <\/strong>Hey, if all I ever had to work on were single user systems, life would be easy.&#160; But the fact is, on the main database system I work with, there are FAR more than one user. And most of the \u201cusers\u201d aren\u2019t humans (no, they aren\u2019t zombies either) but rather machines, reading in data from mail, internet, and phone calls and processing the data.&#160; Actual human beings work slowly compared to what a computer can pump in data. And to make sure that the data isn\u2019t complete rubbish by the time the humans actually look at it, we have to use locks, and locks slow things down\u2026But the people want to feel like they come first\u2026it is a difficult task, but good design and decent throttling mechanisms can be used to make it happen. (And don\u2019t get me started on all of the moving parts, disk, memory, etc, etc. Beyond data level locks, you have hardware\/resource locks called latches that make certain that the laws of physics are honored.)<\/li>\n<li><strong>Indexing \u2013 <\/strong>The first thing most people think of when they are adjusting performance, and for a good reason.&#160; An index can turn a full table scan operation into a single row operation, simply by adding a primary or unique constraint (usually part of the logical database design) or any other unique or non-unique indexes that may be needed for the queries.&#160; But indexes aren\u2019t free, and you have to be careful not to put useless and irrelevant indexes on \u201cjust in case.\u201d<\/li>\n<li><strong>Hardware<\/strong> \u2013 SQL Server can be run on simple cheap hardware, and for most situations you wouldn\u2019t notice if it was run on a server that could easily be a file server. Unfortunately, the problem is that as you start to need \u201creal\u201d power, the hardware configuration cannot be so\u2026simplistic.&#160; Adding CPU power and RAM is simple, but the worst part of the process is disks.&#160; Until solid state drives really hit it big (and even afterwards in many ways) disk drive speed and redundancy is big in making your server run fast.<\/li>\n<li><strong>Good code that accesses your data \u2013 <\/strong>No matter how well your database is designed, if you code in a crappy manner, using loops where queries would work better, lots of unnecessary temp tables, poorly formatted search arguments like WHERE datediff(day, columnName, getdate()) &gt; 1 (would not use an index on columnName), you are hosed.&#160; If you have the foresight to use stored procedures, you can go back and tune later in the process as you need to (in case you get stuck with programmers who aren\u2019t really relational \u201cyet\u201d), or at least have an architecture where you can adjust the code being executed from your app, you can fix poorly performing code\u2026if not, well, good luck (and get ready to throw away a lot of bucks on hardware.)<\/li>\n<li><strong>Adjusted Database Design \u2013 <\/strong>sometimes you just can\u2019t use the design you have, and as a good designer\/architect, you have to be big enough to admit that.&#160; Denormalization can be a solution (especially if you are trying to optimize reports\u2026) but often you just have to go in and adjust the design in other ways.&#160; The better your code\/encapsulation layer, the easier it is to do (it might <em><strong>take<\/strong><\/em> a bit longer, but it will be straightforward\/safe).<\/li>\n<li><strong>Etc \u2013 <\/strong>Network speed, faulty hardware, poorly performing applications\u2026I could go on for days, but I won\u2019t.&#160; The fact is, there are many problems that can make performance bad, and sometimes they are database related, and sometimes not. <\/li>\n<\/ul>\n<p>I am not numb to the fact that performance is an issue.&#160; It is really a major pain point, because it changes so much with all of the aforementioned factors, but also because it is negotiable.&#160; Data integrity is not a negotiable factor. You can\u2019t say \u201cbah!\u201d to users because they complain that \u201ctheir numbers aren\u2019t adding up\u201d, but \u201cthe server seems kind of slow\u201d can easily be written off as whining (mostly because it usually is.)&#160; The problem is, whether your company admits it to itself or not, time is money, and if a user has to do a task 200 times a day and performance issues makes it take 10 seconds instead of 4 seconds, it doesn\u2019t seem like much\u2026 but 1200 seconds is not a small amount of time in the least.&#160; And if there are 100 people doing these same repetitive tasks\u2026that\u2019s a lot of time (and no uncommon in say, a call center.)<\/p>\n<p>So what\u2019s to be done?&#160; Planning, testing, having proper environments for testing performance are great things but to do it right you will probably need to double your spending on hardware to have a production and a \u201cpre\u201d production environment to test out your designs under load.&#160; Most smaller companies (particularly if the hardware isn\u2019t the backbone of what they do) don\u2019t have that.&#160; Production is where the load is tested, and they hope for the best. <\/p>\n<p>Well stop hoping for the best, and design for great performance. Whether you can test performance or not, you can still be prepared by building your database for performance and just as important, for tuning in the easiest manner.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I guess eventually I had to admit that performance matters. Whenever I speak, and in all of my books, I try to stress over and over that performance is NOT the only thing. The fact is all too much time is spent trying to make database applications run faster when the real goal should be&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82065","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82065","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82065"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82065\/revisions"}],"predecessor-version":[{"id":82295,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82065\/revisions\/82295"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82065"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}