Articles
The Role of Table Design in SQL Query Performance Tuning
by Tosska Technologies sql server performance tuning- Tosska Technologies
Database professionals write plenty of SQL queries to fetch a
particular result from databases. Each different kind of statement is likely to
offer different levels of performance. For this reason, DBAs have to pick the
ones that provide the best performance and the quickest results. They can also
optimize other queries by tuning them to improve performance during execution
and data retrieval.
While working on SQL query
performance tuning,
you will have to consider the extent of improvement on the performance of each
query. In fact, a lot of professionals believe tuning queries is more important
than other components, such as software or hardware configurations that have an
impact on performance.
How to
Maintain Query Performance with Proper Table Designing
Here, we will discuss some important things to remember while
designing tables from a performance perspective in SQL Server. If you have read
this far in search of Oracle
performance tuning tips, you can refer to our other blogs and articles
covering this topic.
●
A primary
key is a must-have for every table as it assists with updating, deleting, and
fetching rows.
●
Always make
the first column a primary one.
● Conduct
normalization in your tables to the 3rd form, known as 3NF. 3NF implies that
the table is in 2NF without transitive dependencies. Doing this is necessary as
it decreases duplication, leaving fewer tasks for the database to take care of,
improving its performance.
● Conversely,
bring down the normalization or denormalize tables that are 4NF or 5NF to 3NF.
Otherwise, it can result in performance degradation since you will need more
joins for a lot of tables.
● Horizontal
partitioning can help with SQL query
performance tuning.
This is particularly true in the case of extremely large tables for both in-use
and archived tables.
●
Decreasing
column quantity in tables also helps improve database performance. That’s
because when a table has a low number of columns, it will obviously use less
space in comparison with tables that have more columns. Moreover, a single data
page will fit more rows, leading to lower processing to reach table
information.
● Give
preference to constraints over rules, defaults, and triggers wherever you can.
The reason behind this is the better efficiency of constraints that directly
helps performance. They also show greater reliability and consistency.
● Make use of
the file system rather than blob data type when you require storage for sizable
binary objects. This is because the server requires plenty of resources when it
has to place large binary objects inside a table. This is similar to Oracle
performance tuning tips
as it uses extra resources as it reads rows to scan the information. When the
database saves large binary objects in a file system, it simply makes use of
extra tables. These tables don’t undergo scanning, so their presence doesn’t
have a negative impact on database performance.
Summary
Regardless of the quality or capacity of the hardware, the speed and output of your database server can take critical hits from a bunch of bad queries. In fact, a single bad query can prove enough to make the database perform poorly.
The above-mentioned list contains some useful techniques you can
carry out to uncover queries with poor performance. You can then proceed with SQL query
performance tuning
to improve their performance. You can make great improvements with proper table
designing and by tuning the costliest or most frequently-used queries.
Sponsor Ads
About Tosska Technologies 
sql server performance tuning- Tosska Technologies
91 connections, 0 recommendations, 216 honor points.Joined APSense since, August 29th, 2019, From Kowloon, Hong Kong.
Created on Apr 27th 2022 02:58. Viewed 247 times.
