Is the 'BETWEEN' function very expensive in SQL Server?

Question

I'm trying to join two relatively simple tables together, but my query is experiencing serious hangups. I'm not sure why, but I think it might have something to do with the 'between' function. My first table looks something like this (with a lot of other columns, but this would be the only column I'm pulling):

RowNumber
1
2
3
4
5
6
7
8

My second table "groups" my rows into "blocks", and has the following schema:

BlockID     RowNumberStart     RowNumberStop
1           1                  3
2           4                  7
3           8                  8

The desired result I'm looking to get is to link the RowNumber with the BlockID like below, with the same number of rows with the first table. So the result would look like this:

RowNumber   BlockID           
1           1
2           1
3           1
4           2
5           2
6           2
7           2 
8           3

In order to get that, I used the following query, writing the results into a temp table:

select A.RowNumber, B.BlockID
into   TEMP_TABLE
from   TABLE_1 A left join TABLE_2 B
on     A.RowNumber between B.RowNumberStart and B.RowNumberStop

TABLE_1 and TABLE_2 are actually very large tables. Table 1 is about 122M Rows, and TABLE_2 is about 65M rows. In TABLE_1, the RowNumber is defined as a 'bigint', and in TABLE_2, the BlockID, RowNumberStart, and RowNumberStop are all defined as 'int'. Not sure that makes a difference, but just wanted include that information, too.

The query has now been hung up for eight hours. Similar queries on this type and volume of data are not taking anywhere near this long. So I'm wondering if it could be the 'between' statement that's hanging up this query.

Definitely would welcome any suggestions on how to make this more efficient.

Answer 1

BETWEEN is simply shorthand for :

select A.RowNumber, B.BlockID
into   TEMP_TABLE
from   TABLE_1 A left join TABLE_2 B
on     A.RowNumber >= B.RowNumberStart AND A.RowNumber <= B.RowNumberStop

If execution plan goes from B to A (but left join would indicate it has to go from A to B, really), then I'm assuming TABLE_1 is indexed on RowNumber (and that should be covering on this query). If it's only got a clustered index on RowNumber and the table is very wide, I recommend a non-clustered index only on RowNumber, since you'll fit a lot more rows per page that way.

Otherwise, you want to index on TABLE_2 on RowNumberStart DESC or RowNumberStop ASC, because for given A you'd need a DESC on RowNumberStart to match.

I think you might want to change your join to an INNER JOIN, the way your join criteria is set up. (Are you ever going to get TABLE_1 in no block?)

If you look at your execution plan, you should get more clues as to why the performance might be bad, but the Stop criteria is probably not used on the seek into TABLE_1.

Unfortunately SQLMenace's answer about the SELECT INTO has been deleted. My comment regarding that was meant to be: @Martin SELECT INTO performance isn't as bad as it once was, but I still recommend a CREATE TABLE for most production because SELECT INTO will infer types and NULLability. This is fine if you verify it is doing what you think it is doing, but creating a super long varchar or a decimal column with very strange precision can result in not only odd tables, but performance issues (especially with some of those big varchars when you forget a LEFT or whatever). I think it just helps to make it clear what you are expecting the table to look like. Often I will SELECT INTO using WHERE 0 = 1 and check out the schema and then script it with my tweaks (like adding an IDENTITY or adding a column with a timestamp default).