|
|
Posted by Erland Sommarskog on 05/10/06 00:45
sql_server_user (kaioptera@gmail.com) 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@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
Navigation:
[Reply to this message]
|