Database Optimization Made Easy: MS SQL and MySQL Performance Tuning Services
Can you optimize my ms sql or mysql database for best performance?
Probably the most common request a DBA in general can get is to optimize a database or to perform a performance audit, which is really just another way of saying the same thing. The idea behind this request seems simple enough – you're the expert on databases. I have a database, please tell me if my DB is running as it's supposed to.
It should be no different from going to a mechanic and asking him/her to check if your car is working well or not. Right?
Well, the true answer is maybe. A database is very different from a car and can't really be compared. One of the biggest differences relevant to this discussion is the purpose. All cars have the same purpose – to get us from point A to point B. True, some of them are better suited for paved roads, and others for off-road driving, and yet others may be amphibian, but at the end of the day a car is a transport.
Not so a database. While it could be said that the ultimate purpose of a database is to store data and to enable users to access it, there is a lot more to it:
- First of all, a database has a schema which defines what can be stored and what can't. It has to be built in accordance with business needs. In some cases, malformed schema can prevent a business from being able to capture critical information, in milder cases of dysfunction it can cause developers to write code that has no chance of performing well. The code optimization for your database such as: as ms sql or my sql could be mission impossible.
- Even if the database design is 100% perfect there are still ways to write ugly queries. Using an ORM? It's almost assured that your queries will not perform and/or scale. Some companies aren't even conscious of the fact that data in their database is only accessibly via an API and that API needs to be defined and fine-tuned.
- Only after the above two issues are taken care of does a “traditional” DBA work begin. Once the logical layer is properly defined and tuned, we can deal with the physical issues such as type of storage for tables (including partitioning), memory usage including cache management, etc.
What does this mean in practical terms? Simple. If you're considering a database performance review start from the logical layer.
- Make sure that your entities are defined in accordance to all business needs. Which means keeping things as specific as possible.
- Many people with OOP background tend to create “generic” tables. That's a sure way to kill your database performance.
- Keep your data types as small as possible. Some people think that it's not an issue because storage is cheap. True, storage isn't as expensive as it used to be, but that's not the only reason to avoid using varchar(max) all over the place. One of the more obvious reasons is that well defined types make optimizing queries so much easier.
- Speaking of optimizing queries, watch your collations. Rarely is there a reason to have more than one collation in the same database. So, think twice before you decide that a particular field needs to be case sensitive in only one table. And while we're on the subject. Please make sure that you have a data dictionary for your model. Because most of these problems happen because someone just wasn't paying attention, or didn't notice, etc. Having a data dictionary eliminates most of these accidental differences.
As a side note, a data dictionary is also known as metadata which is the data about your data. At minimum you should have list of tables and columns with their data types. But it is much more than that and a whole subject on its own.
- Create your API in the database programmable layer. Whatever you do don't use an ORM. Many developers have a fear of stored procedures. I don't fully get it. Maybe your company can cover therapy sessions to help. Whatever it takes, keep your API close to your data.
- Design your database with the right tools. A data modelling tool will provide you a global view on the entire database schema and will warren you of any mistakes done within the database design. Our favorite tool for the design of database is xCase. But there are many other tools that will work just as well. Check our article about the Reverse Database Engineering and Database Design, and how we maximize our productivity of the data modelling tool we use.
An experienced Remote DBA can guide you through this process and help you create a solution that is both powerful and flexible.
I started to develop custom software since 1985 while using dBase III from Aston Tate. From there I moved to FoxBase and to FoxPro and ended up working with Visual FoxPro until Microsoft stopped supporting that great engine. With the Visual FoxPro, I developed the VisualRep which is Report and Query Engine. We are also a dot net development company, and one of our projects is a web scrapping from different web sites. We are Alpha AnyWhere developers, and the Avis Car Rental company trusted us with their contract management software that we developed with the Alpha Five software Engine.
Comments
ORM are good for writing logic quickly and with some safety, sure they have some limitations and performance costs, but they say premature optimisation is the root of all evil. I think it's better to do profiling to identify certain performance bottlenecks and replace those with stored procedures as necessary.
I have seen projects in the other extreme, where the backend was nothing but an anemic wrapper over more than 100 stored procedures. Sure it's fast, but it makes debugging, understanding the code base, and changing functionality a nightmare. It also used generic tables, as per your OOP comment, so maybe that was a factor.
So I think as always, the answer lies somewhere in the middle, but certainly, good knowledge of databases will always come in handy.
Like they manage our database optimizations needs and all our software development
needs for the last 25 years. Our database is fully optimized and the
performance is simply outstanding. Their database Experts monitor
our database e24/7. Thanks for sharing this article.
Jeff Peltz
Like True. Modern devs are so used (addicted maybe?) to ORMs that they don't even know or want to know how to put a stored procedure together. There's a great debate over whether ORMs are good or bad for development, but I'd say each approach has its own ups and downs. Using SPs and well-written database queries in general, especially when working only with SQL Server and don't need to make your application database-agnostic can be a real performance booster. On the other hand, ORMs will make your code more easy to maintain (and source control), will help you compartmentalize business logic (the parts strongly related to data access) more easily and allow for a whole set of layers, if you have the need to create more than one. But to make an ORM perform efficiently you need to utilize various caching and optimization strategies so you just end up switching the cost from database development to code.
My opinion is it depends on what you need to do with your application (and your data). If performance isn't your key factor, an ORM would probably be okay, but be prepared for scaling costs should the need arise. If performance is key, then you should be as close to your data as possible, having total control of even the slightest SQL statement, with proper index coverage and very specific query syntax that an ORM almost never achieves.
Like
Comments