SQL Server Performance Forum – Threads Archive

Performance tuning a top 1

I have a query that i’m trying to performance tune a little better.
i’m stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i’m using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = ‘xyx’
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = ‘xxy’
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = ‘yxx’
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.

---

Can’t you use
Select StatusTypeID ,Max(date) group by
StatusTypeID

---

yea i did that too and it seems to just slow the query down. hmmmmmmmmm i’ll try it again and posts the results.

---

no b/c each date would be a seperate field going across and the tableID’s would be going down.

---

Hi,
which query u tried in grouping, i feel the option which dinesh gave
has to work smoothly for desired output.
What was wrong with that query.[?]

---

I don’t know if it will be faster or slower (it depends on data), but you can try:
SELECT SR.field1, SR.field2, SR.field3,
t.date1, t.date2, t.date3
FROM dbo.someTable SR
join (SELECT LS.tableID,
max(case
when LS.StatusTypeID = ‘xyx’ then LS.StatusDate
end) as date1,
max(case
when LS.StatusTypeID = ‘xxy’ then LS.StatusDate
end) as date2,
max(case
when LS.StatusTypeID = ‘yxx’ then LS.StatusDate
end) as date3
FROM dbo.TABLE_STATUS LS
GROUP BY ls.TableID
) as t on t.tableID = sr.TableId
FROM dbo.someTable SR
WHERE etc.

---

Interestingly I’m just writing a FAQ for this site on that topic. Here’s what I use as example:
use northwind select * from orders t1
where t1.orderdate=
(select max(orderdate) from orders t2
where t1.customerid=t2.customerid)
order by t1.customerid select * from orders t1 inner join
(select customerid, max(orderdate) as maxdate from orders group by customerid) t2
on t1.customerid = t2.customerid
and t1.orderdate = t2.maxdate
order by t1.customerid select t1.*
from orders t1
where t1.orderdate in
(
select top 1 t2.orderdate
from orders t2
where t2.customerid = t1.customerid
order by t2.orderdate desc
)
order by t1.customerid Now, when you have SET STATISTICS IO ON, you’ll find that (90 row(s) affected) Table ‘Orders’. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 19. (90 row(s) affected) Table ‘Orders’. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0. (90 row(s) affected) Table ‘Orders’. Scan count 825, logical reads 130143, physical reads 0, read-ahead reads 2. Keep in mind, that the Orders table only has 830 rows!!! So, I actually think, using TOP 1 in such cases is the worst alternative you have.
Btw, you didn’t tell anything about indexes on that table. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

]]>