In databases, I often need foreign keys in my tables, because I’ll want to use them to select content out from my tables. However this can often result in either bad index utilization in the selection or making additional indexes based on the foreign key(s) and the content I need to select out.
This in turn can result in ending up with “many” indexes and sometimes many big indexes.

However a method can be to make smaller indexes and use an INNER JOIN to join into the table an extra time.

I’m going to show the pattern with a relative simple example to illustrate, because it is about the pattern more so then the specifics layout, content and size of the table.
It is just a pattern/technique to keep in mind and have in the SQL toolbox.
Suppose you have two tables of a similar pattern to this:

(click for larger size)

I’ve let the SQL Server create my clustered indexes based on the primary key, which means I have a clustered index over my primary key(s).
When having to look up rows in JoinOne based on the foreign key, it’ll often look like this:

SELECT * 
FROM JoinOne T1
WHERE FKOne = <VALUE>  

Because I’ve added no other indexes to the tables, I’ll get an Index Scan or Table Scan when running the above query, which - as we - know is not a good thing most of the time.
This usually leads to the creating of a second index which indexes over my foreign key.
However if the table is large, and if I’ll need to extract many/most of the columns (like in my example with SELECT *), it can mean I’ll have to make either a complete index or one with many included columns, just ordered by my foreign key first.

If I make a small index with my foreign key and my primary key second, I can use this to join into my table again, and then after that use my primary key.
This is an index to illustrate:

 
(click for larger size)

Then I can make the following query:

SELECT T2.* 
FROM JoinOne T1
INNER JOIN JoinOne T2 ON
 T1.PKOne = T2.PKOne
 AND T1.PKTwo = T2.PKTwo
 AND T1.PKThree = T2.PKThree
WHERE T1.FKOne = <VALUE>  

When looking at its execution plan, it’ll show two index seeks instead of my previous index scan. This way I have a small index as possible, but maintain seeks in my execution.
Of course there are some considerations one need to take with this pattern. Firstly – if the tables are “small”, then the scan in itself might be alright, or the overhead of keeping a complete index for the foreign key is neglible.

However the advantage of the pattern is that I can have smaller indexes on (very) large tables, which means less overhead when inserting/updating – but still have only index seeks in my selections execution plans.
It is a useful technique in my opinion – when used at the right times, which of course is on a case by case evaluation as always.  

Despite having used SQL Server for quite a while I’ve not really had much practical usage for many of the new features. Lately however I’ve started to use Common Table Expressions (CTE) much more and I’m absolutely starting to love them. Another feature I am starting to love are the ranking functions, RANK().

Basically speaking CTEs are subqueries which you can reuse, so practically they remind me very much of temporary tables and similar techniques. But they not only provide vastly better clarity (in my view), but they can also perform better. So I’m going to be turning more attention towards CTEs and their usage and hopefully some performance checks in this and hopefully future blog posts.
RANK() is a function which enables you to – well rank – groups (aka partitions) of data, providing you with a powerful tool for solving problems. It is a good syntax to have in your problem solving toolbox.

The problem I was faced with at work was that we had some price data which we needed to clean up.
We had a number of products which had a number of prices. And out of these combinations we only wanted to keep the latest ones. So utilizing some of the new things in SQL Server 2005 we were able to solve it. For this problem we used RANK and CTE’s. Granted we could have solved it without using CTE’s but it was here I started seeing the benefits of using these common table expressions, and the solution turned out to be elegant thanks to these techniques.

We had a table which simplified consisted of a PriceID (identity), a price value and a product id foreign key.



Because we only wanted to keep the “newest” Price we knew that we only wanted the highest PriceID from each “group” of Products.
Then I started to look at the GROUP BY clause on ProductID, but it left me with a problem of getting the highest PriceID for each group, so I could delete the rows from Price which weren’t selected.
I then remembered the RANK function, which I’d noticed a few times, and the pieces of the puzzle simply clicked together.

What I did was selecting the RANK on the partition of ProductID (basically, you group the RANKs on the partition). And because I wanted to keep the highest PriceID I ordered by that field descending. Then I also selected the ProductID and the PriceID in my query. Like this:

SELECT
 ProductID, PriceID,
 RANK() OVER (PARTITION BY ProductID ORDER BY PriceID DESC) AS R
FROM Price      

Now this gave me a result akin to this (PriceID, ProductID, R)


You’ll see that I have now got each ProductID, PriceID with a grouped rank in my resultset. And then it is merely a matter of selecting the rows which have a rank higher then 1.
For this I used the CTE syntax like this:

WITH priceCTE AS (
 SELECT
  ProductID, PriceID,
  RANK() OVER (PARTITION BY ProductID ORDER BY PriceID DESC) AS R
 FROM Price    
)
SELECT * FROM priceCTE WHERE R > 1
GO 

As can be seen the CTE is declare using the WITH <NAME> AS syntax. Then the contens of the parenthises provides the subquery which you then can use after the paranthises.
Then it was just chancing the SELECT to a DELETE and I had achieved my goal. Simple and clean, and most of all – easy to read again in a month or two, incase I’m faced with a similar problem.

Then as always I was interested in how this performed against a temporary table variable so I simply made a dublicate of this query but instead of a CTE, I placed the subquery into a table variable, and ran the same.

DECLARE @T AS TABLE(ProductID INT, PriceID INT, R INT)
INSERT INTO @T
SELECT
 ProductID, PriceID,
 RANK() OVER (PARTITION BY ProductID ORDER BY PriceID DESC) AS R
FROM Price

SELECT * FROM @T WHERE R > 1
GO 

Then I took a look at the estimated query plans, and it listed the CTE one as using 47% of the batch and the table variable using 53%. So there is a performance benefit which I’ll look more into at a later point, although not major one in this case.

As you may or may not know, it is possible to use LEFT JOIN (and thus RIGHT JOIN) to filter out tables, just as it is with the NOT IN.
However, how do they perform against each other?
Well, using similar data as created in the "In versus INNER JOIN" example, I made two simple queries to see:

SELECT *
FROM TestOne
WHERE ID1 NOT IN
(
 SELECT ID1 FROM TestTwo
)
GO

SELECT T1.*
FROM TestOne T1
LEFT JOIN TestTwo T2 ON T1.ID1 = T2.ID1
WHERE T2.ID1 IS NULL
GO 

This gave the following execution plan:
(click to enlarge)

As can be seen the margin of difference - 46% versus 54% - is much smaller then with the IN syntax as linked above.
So I tried more complex queries where I just took the ones I made for the In versus INNER JOIN article, and modified them to be NOT IN and LEFT JOINS. Like this:


SELECT T1.*
FROM TestOne T1
WHERE T1.ID1 NOT IN (
  SELECT ID1 FROM TestTwo T2
  WHERE ID2 NOT IN (
   SELECT ID2 FROM TestFour T4
   WHERE ID4 NOT IN (
    SELECT ID4 FROM TestFive T5
   )
  )
 )
GO

SELECT T1.*
FROM TestOne T1
LEFT JOIN TestTwo T2 ON T1.ID1 = T2.ID1
LEFT JOIN TestFour T4 ON T2.ID2 = T4.ID2
LEFT JOIN TestFive T5 ON T4.ID4 = T5.ID4
WHERE T2.ID1 IS NULL
AND T4.ID2 IS NULL
AND T5.ID4 IS NULL
GO 

This gave a queryplan where the LEFT JOIN actually performened better, not by much but still 52 versus 48%. (I've minimized the query plans because I was only interested in the numbers, and not in the execution plan itself.)
(click to enlarge)

So where the case was more in favore of using IN instead of INNER JOINs when filtering inclusive, then exclusive looks to be more evenly matched when it comes to NOT IN versus the LEFT JOIN syntax.
But as always, use a case by case decision, by looking at your own querys details. This is just an example and guideline numbers.

I decided to do some testing on inner joins versus the in syntax, meaning what to use if I need to select the content from one table out, based on content in another table.
So what I did was to create a couple of tables:
CREATE TABLE TestOne
(
 ID1 INT NOT NULL,
 Value1 NVARCHAR(255) NOT NULL
)
GO

CREATE TABLE TestTwo
(
 ID2 INT NOT NULL,
 Value2 NVARCHAR(255) NOT NULL,
 ID1 INT NOT NULL
)
GO  

I then populated them with some test data (1000 rows for the first and 10000 rows for the second of pointless data). Note that I have added no indexes or restraints of any kind as I wanted the testing to be as base as possible. So then I basically pulled data out from TestOne filtered by ID1 having to be in TestTwo.
Like this:

SELECT *
FROM TestOne T1
WHERE
T1.ID1 IN (SELECT ID1 FROM TestTwo)
GO

SELECT T1.*
FROM TestOne T1
INNER JOIN TestTwo T2 ON T1.ID1 = T2.ID1
GO 

Then I looked at the estimated query plans and was a bit stupefied by the result.

(click to enlarge)

There was a massive difference between the two, and this shows that it is actually better to just use the IN operator instead of INNER JOIN when filtering. Of course if you actually need the content of the filtering table then you need to join, but just filtering – then IN looks clearly superior.
This result was made even more visible when I added a third table (similar to the second one) and joined that into the equation.

Then I wanted to make some more complex queries, because it is rare that I write such simple queries at work. But of course now it gets more “thought out” and fictive now, and I should have done this with real life work data to get a better indication. But this’ll have to do for now :)
Again note that no indexes, keys, restraints or anything are made.

So I populated the database two more tables (Four and Five) and these additional tables and data very similar to how I populated the first three.

Then I made the following queries

SELECT T1.*
FROM TestOne T1
WHERE T1.ID1 IN (
  SELECT ID1 FROM TestTwo T2
  WHERE ID2 IN (
   SELECT ID2 FROM TestFour T4
   WHERE ID4 IN (
    SELECT ID4 FROM TestFive T5
   )
  )
 )
GO

SELECT T1.*
FROM TestOne T1
INNER JOIN TestTwo T2 ON T1.ID1 = T2.ID1
INNER JOIN TestFour T4 ON T2.ID2 = T4.ID2
INNER JOIN TestFive T5 ON T4.ID4 = T5.ID4
GO     

What I select out is TestOne filtered by TestTwo which is filtered by TestFour which again is filtered by TestFive.
This gave a similar result as before, but with a smaller margin.
Now it was 41% versus 59% in favor of the IN syntax.
(click to enlarge)

(click to enlarge)

This means that we are now entering a more case-by-case area where one can’t say for sure which method is best. Also personally – I would say that the INNER JOIN syntax makes for a more readable query, but that of course is personal preference.
But also imagine that I suddenly had to get the information from TestFour as well, for example – that could make quite a nasty query if I tried to use the IN syntax, whereas I could simply change the SELECT in the latter query to SELECT T1.*, T4.* and then done.

Then I tried to add some keys and indexes, but that did not change the result.

So in conclusion, if making simple filtering, then the IN syntax looks vastly superior against the INNER JOIN syntax, but more complex queries and if you need the data from the filtering tables, makes the conclusion more gray and would need a case-by-case conclusion.

Another time, I’ll try to dabble in using LEFT and RIGHT JOINs as filtering against the NOT IN syntax.

 

Following up on my previous testing of table partitioning, I wanted to see if I could identify any notable overhead when using partitioned tables other then in selects, meaning delete, update and of course inserts. At the same time I thought I could test out with a partitioned table but where the files was located on the same physical drive.
So I created a new database, 2 partition functions and schemas and 3 new tables. TestOne was partitioned as my last time, and so was TestTwo. TestThree table was partitioned exactly like TestOne except it utilized file groups located on the same physical drive, whereas TestOne had its filegroups split across two physical drives.
I found out that selects provided no noticeable performance difference between TestOne and TestThree tables. I would think the main area where one would see performance differences here is dependent on hardware and load on the drives, and less so with the SQL Server performance.  So further testing into this will be outside the scope of at least this blog entry.

So – onwards to testing other SQL then selects. The first I tested was ordinary inserts for each of my 3 tables.

INSERT INTO <TABLE>
VALUES (1, 'test')
GO


When running this it provided me with this execution plan:

(click to enlarge)

Not surprisingly there is an overhead visible when it comes to inserting into a partitioned table compared to a non-partitioned one.  Plus the plans do not look the same. I would simply attribute the overhead to the fact that SQL Server needs to look up which file group to locate the data in based on the partition function.
The difference in this instance does however look relative small. The entire subtree cost for inserting into a partitioned table (TestOne and TestThree) were 0.010471 whereas for TestTwo it was 0.01.
So while the specific numbers might differ from system to system etc, it does show an overhead, albeit a small one.
When it came to both delete and updates, I found that the same issues as with select statements were in effect.
When I only needed to delete or update on a specific "grouping" of data, the partitioned tables were actually faster than the non-partitioned one. When I had to update across "groups" then the non-partitioned one was fastest.
For example:

UPDATE TestOne
SET MyValue='Test2'
WHERE MyID = 1
GO 

was faster then

UPDATE TestTwo
SET MyValue='Test2'
WHERE MyID = 1
GO 

by a factor around 20%, whereas

UPDATE TestOne
SET MyValue='Test2'
WHERE MyID = 1 OR MyID  = 2 

was about 10% slower then

UPDATE TestTwo
SET MyValue='Test2'
WHERE MyID = 1 OR MyID  = 2 

This leads back to my conclusion in the last piece that if you in fact can group your data in a manner which makes sense and avoids “cross-grouping data”, then it is faster performance wise to do so. The only time it looks to be noticeable slower to have partitioned data is with inserts.
This of course is not an ever valid conclusion, and I’m sure more concrete and scientifically gathered data could present cases against. But if your data abides to some rules which makes a grouping possible and the main focus on the data is reading versus inserting; then there looks to be mainly advantages when it comes to performance with table partitioning. And especially this can run transparent for normal users and database developers it is definitely something worth a thought.