What SQL query performance issues should you watch out for?

The sequence of columns in an index is significant. For example, several queries use similar columns from the same table: query1 uses col1 and col2; query2 uses col2 and col3; query3 uses col1, col2, col3; query4 uses col2. In this case, it is optimal to create a single index with the column sequence col2, col3, col1, and it will be used in all four queries. In the example above, there is another trick: if the number of columns used in the select section from the table for which we are going to create an index is small (in bytes), consider the option of including these data in the index storage structure (include). Thus, all the necessary data for the result will be in the index, and access to the table itself will not be needed.

Just creating an index is not enough especially with exponential growing data. It neefs also to be ensured that indexes are reindexed and maintained property so that fragmentation levels are low. If fragmentation levels go high then there will have performance issues

3 Steps: 1. Understand data structure and dataflow in your Database very well. 2. Create right and proper indexes. 3. write right query think about your query for 30 mnts and write your query in 1 mnt not the opposite

Missing indexes in SQL databases can significantly hinder performance, especially in large datasets. When an index is absent, the SQL engine resorts to a full table scan to locate relevant rows. This process is far less efficient than using an indexed search, where data is organized in a way that allows for quick retrieval. Full table scans are resource-intensive, consuming more CPU and memory, which can lead to increased load and slower response times on the database server. This inefficiency becomes more pronounced as the volume of data grows, making query execution progressively slower. Proper indexing is thus crucial for optimizing database performance, ensuring faster query responses, and maintaining overall system efficiency.

Cross -joins are the biggest killers for sql performance and I have seen even the most experienced with sql write bad join queries. If a query doesn’t perform well in a lower environment , don’t expect it to perform magically well in prod environment. Test , test , test for functionality and performance in a lower environment.

I think the focus is too much on getting the work done by identifying the tables for joins and we usually start with the tables from which we require most fields but we should always take time and think about the volume of data in each table and ensure that the positioning of the tables and order of joins is also considered. The order of joins can make a huge difference when you are looking to improve the performance as it could go from scanning 1 million rows and joining to another 10K rows to scanning 10K rows and matching it with 1 million rows and if the tables are properly indexed this could result in a huge performance boost.

JOINs are very helpful, but also painful when it slows down your query. There are many considerations when applying joins between tables. 1- Whether there is need of index between joining columns 2- Which table is hitting first, always use a smaller table on the left side of the join. 3- make sure that you're not using inline queries while joining. 4- make sure that you are using Not Exist where necessary to exclude data. 5- Never use NOT IN in joins And there may be some others as well.

The first sign of potential issues in a query designed for a well-normalized set of tables is the hasty insertion of the DISTINCT operator and reluctance to spend time understanding the reasons for duplicate rows. DISTINCT effectively addresses the issue of obtaining a unique set of rows but, at the same time, equally effectively conceals flaws in writing conditions in joins and, of course, adds headaches when optimizing performance.

Tables referred should be categorised into Large / Medium / Small (master data) based on the forecasted volume of data that it could contain. - In case of filters are applicable to common columns of the referred tables list , table alias pertaining to the smaller table should be referred in the filter which in turn shall drastically reduce the I/O - One should double check that joined columns should be indexed especially in case of large tables - Ensuring the right driving table for query execution brings in a noticeable performance gain - Sometimes query hints help in gaining performance during joins - indexed joins / Hash joins are always preferred in comparison to merged joins

Avoid "dynamic SQL" as long as possible! Avoid using in memory tables for smaller amount of data. Try to understand the structure of the data, don't do more joins than needed. Also, on the same note - do not create more tables than needed.

The use of EXISTs is often overlooked and wrongly replaced with IN or, worse, NOT IN, in many of the sub-optimal subqueries I see. Replacing IN or NOT IN with EXISTs or NOT EXISTs using a correlated sub-query eliminates the need for an optimizer to either perform a full table scan, in most NOT IN cases, or an inefficient uncorrelated sub-query. EXISTs, and NOT EXISTs, when used properly provides much more efficient use of resources and query plans.

How do you know if a query is sub-optimal? You need to learn to use query analyzers and benchmark your work. A lot of developers know how to join a few tables and the aggregate data, but far fewer know how to use an analyzer. How do you know if the subquery is a problem or not? Getting skilled with this is a critical skill if you want to be able build systems that preform at scale. Check your work by testing your work.

As much as possible, I try and build the sub query in the WITH clause. The materialize hint can be used in such queries, when the results are not huge. That way, the results are available for quicker access.

sql subqueries powerful but tricky. Use it wisely, it will slow down the result, try to change to sql joins, derived tables or common table expression to speed up the result and also keep in mind to avoid correlated subqueries as it creates roadblock of indexes & parallelism and impact the DB results.

When you are dealing with large amount of data and if you are performing a repetitive aggregation function, it is preferred to use materialized view for the aggregation queries. The performance will be significantly improved when you use precomputed results from a materialized view. Refresh cycle of materialized view depends upon each project. Another option is to create a partitioned table. Depending upon the filters used in the aggregation functions, the table can be partitioned to get better performance. There are few other ways to improve the performance of aggregation queries. Solution will vary according to the specific requirements.

In some cases, it is better for performance to use GROUP BY instead of DISTINCT. The last one leads to additional index spools, — for example, when the query calls XML PATH.

Effective aggregation is crucial for insightful data analysis. Key to this is focusing on columns with high cardinality (many unique values) when using functions like SUM, COUNT, AVG. This approach ensures meaningful groupings and efficient processing, avoiding the common pitfall of handling large data volumes for minimal output seen with low cardinality columns. Moreover, smart filtering plays a pivotal role. Utilize the HAVING clause for post-aggregation filtering to refine your results, rather than the WHERE clause which filters data before aggregation. This not only optimizes query performance but also enhances the relevance and precision of your analysis.

Sometime you think that creating a view to aggregate multiple table will simplify your access to scattered data. That's correct ! However it might cost you some performance issue especially if you use this view to retrieve a small amount of data that are stored in one or 2 tables. In conclusion, view can be useful but only if they are used for specific use-case

Generally speaking a transactional database is not the place to run large aggregations. These queries do not scale as the data grows, become increasingly slow, and may have adverse impact other transactional queries by holding locks. Consider using an OLAP database such as AWS Redshift, or export a snapshot to S3 and use Athena.

Improper data types in SQL queries affect performance through issues like data conversion, truncation, or index inefficiency. Choosing data types that match the nature and size of your data is crucial, ensuring compatibility in comparisons and joins. Unless necessary, avoid generic or large data types like VARCHAR(MAX) or NVARCHAR(MAX) unless they hinder database optimization techniques. www.IamCalvinBrown.com

The classic performance problem related to data types looks like this: "where id = '100' + @second_part_id + '00'", where id has the int type, and @second_part_id is text. If the optimizer cannot recognize that concatenating a string in the condition can be converted to int and decides to convert id to text and then compare, it will lead to the inability to use the index on the id column and result in a full table scan with simultaneous conversion of int to text and comparison. This issue is especially common in dynamically constructing query text, and when the construction of the query text is controlled by a framework. The query may appear to work, and performance problems may only become apparent during load testing at best.

Pay attention to the field types used for character fields in creating tables. It is easy to introduce subtle errors when comparing varchar and char fields. On occasion, this can come about from using a Create Table As Statement

A common data type misuse is a Boolean column that should be of type Bit, but is written as an Int or char type in the table. This should be avoided no matter what...

Improper datatypes affect sorting operations significantly and as a result suppresses the use of a qualifying index in case of a datatype mismatch. Automatic datatype conversion is also used by SQL engine / optimizer while executing queries which in turn disturbs the performance predictions. In fact incorrect use of data conversion functions on the left side of the expression suppresses the use of an existing index. Although function based indexes are also available, judicious use of this option should be encouraged.

I do not agree with the information that the complexity of the command is a cause of performance problems. The most current databases are fully capable of finding an optimal execution plan, as long as they are fed with updated statistics and configured correctly. In general, it is better to execute a complex command than a simpler one and, after these, another 2 or 3 equally simple ones, to obtain the same final data.

Of course, before executing any query the planer will plan the query. Unnecessarily making complex query will take extra time in planning phase. Use explain to make sure you are fetching minimum required data instead of pulling tons of data and then filtering out unnecessary records.

Use CTEs! The performance gains from parallelisation of queries is easiest done from CTEs. They force you to break up your code into chunks that do things and help you find common patterns in your data.

Keeping things simple will resolve a lot of performance issues. Writing simple and efficient queries can help to eliminate performance issues.

In my journey with SQL queries, I've found a few personal practices that significantly amp up efficiency. First off, adopting aliases for tables and columns isn't just about syntax; it's about making queries more readable and straightforward. Comments play a pivotal role too—using them judiciously not only clarifies logic but also promotes a culture of understanding and maintainability. Consistent formatting is like the unsung hero; it keeps things neat and reduces the overall complexity. Steering clear of nested or repetitive code and opting for a modular approach has been my mantra for enhancing query simplicity. Lastly, breaking down complex queries into simpler counterparts isn't just a strategy; it's a game-changer.

The best way to understand SQL is by understanding the order of execution. This will help you to write clear, concise, and efficient queries without any unnecessary complexity. Order of execution: 1) FROM/JOIN 2) WHERE 3) GROUP BY 4) HAVING 5) Aggregate functions 6) Window functions 7) SELECT 8) ORDER BY 9) LIMIT/TOP

My perspective is before moving ahead with any index creation we should first understand the execution plan of the query. If a FULL TABLE SCAN is taking place, we should first check for the number of rows actually required by the operation. If more than 10% rows are result set then creating index is of not much help. We should also consider table Joining order and Joining method. Sometimes due to inadequate statistics optimizer picks up inefficient join method or order. Many times the data being retrieved is very huge in such cases we should consider moving those queries to standby database.

Avoid the use of functions in the where clause in the logic conditions. The condition will be checked for every record… and the function executed for every record too.

Look at the execution plan, I know it’s boring, but this will EXPLAIN a lot. Most (if not all) databases and big data tools like Spark have optimizers and you can get the execution plan from them.

You can use database views to have better performance than improper indexing. A database view is a subset of a database and is based on a query that runs on one or more database tables. Database views are saved in the database as named queries and can be used to save frequently used complex queries. There are two types of database views: dynamic views and static views. In-memory data load using in-memory databases can help with speeding up indexed entries.