Background information

Before SQL-92 there was no possibility to use outer joins since they simply were not in the standard. To do an inner join you basically did:

SELECT * 
FROM t1,t2 
WHERE t1.id = t2.id

So there was not even the keyword JOIN that you can use today. Therefore people used subqueries quite a lot since it was the only possibility to do things an outer join can provide.

With outer joins available today there is the recommendation to rather use joins than subqueries since the Query Optimizer of major DBMS systems can handle joins better than subqueries. If you take SQL Server for example you will realize that the query optimizer tries to rewrite any subquery as a join. Since I treated that subject in my master thesis i know that Oracle DB does the same.

So you can help the query optimizer by using joins ....

continue reading

 

Today I want to address two very common issues when it comes to query plan tuning or query optimizing. At first I will have a look on implicit converts and the consequences. The second topic is all about the query optimizer and how quick one can have bad execution plans. For both issues I will five advice what to do in such a situation.

Implicit converts

Whenever you tell SQL Server to compare or evaluate two expressions that have different incomparable data types the SQL Server will adjust one of the expressions to a fitting data type so it can evaluate the expression. A very basic example is comparing integer with float numbers. Most likely the integer number will get transferred into a floating number to then compare it with the other float number.

But in SQL this can have fatal consequences. At first the optimizer does not know how a transformed column is ....

continue reading