T-SQL INNER JOIN Optimisation

Question

Well, I really have read so many articles, used different techniques and done some random things to do this.

My problem was that a have big tables (over one million rows) and some small (with few hundreds rows) - we are talking about 8 inner joins involved.

What reduces the execution time from over 2 minutes to 30 seconds was very strange for me and I was not able to find why this happens.

When I select columns from the tables I cast them. What I did was to cast the column to the most small possible type.

For example:

• nvarchar(4000) to nvarchar(50 or 25)
• bigint to int
• int to tinyint

The result was over 1 minute and 30 second better execution time. Why this happen?

For example if my variable is string with length 10 the nvarchar(4000) and the nvarchar(50) will cast it to nvarchar(10) (or something close to that). So, why when I reduce the outcome type from cast the things go better?

Something more - I make a lot of test to check which function is better - cast or convert (do test for string to string, string to number, number to string) but was not able to define which works better. Sometime the convert gives me few seconds better execution time, but not enough to make a conclusion. Has anyone do something like this and succeeded in?

Thank for your time in advance.

Answer 1

The reason the performance is better is that the smaller data types have much less working set - see the execution plan (http://www.red-gate.com/our-company/about/book-store/assets/sql-server-execution-plans.pdf)

I expect you get the largest benefit from the nvarchar(4000) to nvarchar(50) - that's a reduction of 80x - and nvarchar(4000) can use up to 8K! of space. For a key, that is not a good idea.

In addition, the fact that there are no foreign keys probably mean you don't have a very good indexing strategy either. If you did have indexes (even for ridiculously large columns), you would probably find they could outperform the cast since it probably wouldn't spool as much.

In general, you don't want any operations on your keys in the join if at all possible, especially for large data sets.

Answer 2

To guess the motivation for the time difference you should compare the two query plans (with and without the casts, or with casts and converts) and the relative costs; you'll be able to identify in which phase there is a cost change.

Answer 3

if you need to join a nvarchar(4000) to a nvarchar(10) and million of rows are involved, Id use a presisted computed column where you do a LEFT(long_column,10). Depending on the query you can even index that computed column. I'll bet your join will preform much better.

Answer 4

You can take care of below point as well for Inner Joins

Please check the difference between these two queries

SELECT T1.ColumnName1,
       T2.ColumnName2
FROM   (SELECT ColumnName1
        FROM   T1
        WHERE  ID = 10)T1
       INNER JOIN T2
         ON T2.ID = T1.ID

SELECT T1.ColumnName1,
       T2.ColumnName2
FROM   T1
       INNER JOIN T2
         ON T2.ID = T1.ID
WHERE  ID = 10  

You can add Non Clustered Index as well to get rid of scanning the complete table

But this is normally handy when the query returns one record as being used in my Sub Query example:)