Multicolumn correlated subquery?

Hi,

I have a history table with about 400 million rows, with a unique
composite nonclustered index on two columns (object id and time period)
which is used for most of the queries into the table, and an identity
column for the clustered primary key.

Many of my queries use correlated subqueries to pull unique history
rows from the history table for each of a set of objects from the
object table, for instance, pulling the earliest history row for each
object in a set. These correlated subqueries reference the object table
and return the primary key of the history table, e.g.:

select *
from lp_object l
inner join lp_object_history h
on h.lp_object_id = l.lp_id

where l.lp_set_id = 'SOME_LITERAL'

and h.lp_id = (
select top 1 lp_id
from lp_object_history
where lp_object_id = l.lp_id
and lp_some_column > 0
order by lp_time_period)

Now, if lp_some_column is not indexed, this query has no choice but to
read the entirety of every single history row for every object in the
set where lp_set_id = 'SOME_LITERAL', so that it can determine if
lp_some_column > 0, and because the history table is clustered by the
identity column rather than the ID of the relevant object whose history
we're tracking, the reads take forever - they have to bop all around
the disk. The sets I deal with tend to have about 5K objects in them
and about 200K associated history rows.

I'm considering reclustering by the (object id, time period) index, but
then my queries will need an extra bookmark lookup step to get the row
data from the identity value returned by the correlated subquery. I
think it will still be faster, though, so I will probably build a copy
of the table with the alternative clustering scheme to run some
performance tests.

What I'm wondering is, if I were to dispense with the identity column
altogether and replace it with a composite primary key of (object id,
time period), would I be still be able to use my correlated subqueries?
Because then there wouldn't be a single column that uniquely identifies
each row in the history table and I don't think SQL Server supports
multicolumn correlated subqueries.

Thanks for reading,
Seth

sql_server_user ( ) writes:
> Many of my queries use correlated subqueries to pull unique history
> rows from the history table for each of a set of objects from the
> object table, for instance, pulling the earliest history row for each
> object in a set. These correlated subqueries reference the object table
> and return the primary key of the history table, e.g.:
>
> select *
> from lp_object l
> inner join lp_object_history h
> on h.lp_object_id = l.lp_id
>
> where l.lp_set_id = 'SOME_LITERAL'
>
> and h.lp_id = (
> select top 1 lp_id
> from lp_object_history
> where lp_object_id = l.lp_id
> and lp_some_column > 0
> order by lp_time_period)
>...
> I'm considering reclustering by the (object id, time period) index, but
> then my queries will need an extra bookmark lookup step to get the row
> data from the identity value returned by the correlated subquery. I
> think it will still be faster, though, so I will probably build a copy
> of the table with the alternative clustering scheme to run some
> performance tests.
>
> What I'm wondering is, if I were to dispense with the identity column
> altogether and replace it with a composite primary key of (object id,
> time period), would I be still be able to use my correlated subqueries?
> Because then there wouldn't be a single column that uniquely identifies
> each row in the history table and I don't think SQL Server supports
> multicolumn correlated subqueries.

Unless I'm missing something, the query without the IDENTITY column would
be:

select *
from lp_object l
inner join lp_object_history h on h.lp_object_id = l.lp_id
where l.lp_set_id = 'SOME_LITERAL'
and h.lp_time_period = (select max(h2.lp_time_period)
from lp_object_history h2
where h2.lp_object_id = l.lp_id
and h2.lp_some_column > 0)

Whether this will actually perform that much better I don't know, but
I can't see anything good coming through that IDENTITY column.

You may also want to try the effect of changíng

where h2.lp_object_id = l.lp_id

to

where h2.lp_object_id = h.lp_object_id

or even

where h2.lp_object_id = l.lp_id
and h2.lp_object_id = h.lp_object_id

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx