July 25, 2003 at 4:01 pm
Hello all,
I have a query that works just fine but it is very slow. This query uses Sub-Selects to generate a column. This causes too many selects, and as a result, very poor performance. I was hoping that someone could give me some ideas on how this query could be re-written to improve performance. I have included a scaled down version of the tables and query.
Query:
SELECT Table1.[ID], Table1.[Date], (SELECT TOP 1 Table2.Volume
FROM Table2
WHERE Table1.[Date] >= Table2.[Date] AND Table1.[ID] = Table2.[ID]
ORDER BY Table2.[Date] DESC) AS Volume
FROM Table1
Create Table1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL ,
[Date] [datetime] NOT NULL
) ON [PRIMARY]
Create Table2:
CREATE TABLE [dbo].[Table2] (
[ID] [int] NULL ,
[Date] [datetime] NULL ,
[Volume] [int] NULL
) ON [PRIMARY]
Sample Data Table1:
IDDate
1002003-01-01 00:00:00.000
1002003-02-01 00:00:00.000
1002003-03-01 00:00:00.000
1002003-04-01 00:00:00.000
1002003-05-01 00:00:00.000
1002003-06-01 00:00:00.000
Sample Data Table2:
IDDateVolume
1002003-01-01 00:00:00.0002000
1002003-04-01 00:00:00.0003000
Results:
IDDateVolume
1002003-01-01 00:00:00.0002000
1002003-02-01 00:00:00.0002000
1002003-03-01 00:00:00.0002000
1002003-04-01 00:00:00.0003000
1002003-05-01 00:00:00.0003000
1002003-06-01 00:00:00.0003000
So, what I am trying to accomplish is to have volumes from Table2 repeatedly join Table1 dates while the Table2 date is >= Table1 date and ID’s match
As shown above, the query results are correct but when working with a large amount of data, the performance is terrible.
Is there a better way of writing this query?
Any ideas would be greatly appreciated!
July 25, 2003 at 7:55 pm
The Sub SELECT TOP 1 gave me a bit of trouble but try this.
SELECT
tblB.[ID],
tblB.[T1xD] t1a,
tbl2a.Volume
FROM
(
SELECT
T1x.[ID],
T1x.[Date] T1xD,
MAX(T2x.[Date]) T2xD
FROM
Table1 T1x
LEFT JOIN
Table2 T2x
ON
T1x.[date] >= T2x.[date]
AND T1x.[id] = T2x.[id]
GROUP BY
T1x.[Date],
T1x.[ID]
) As tblB
INNER JOIN
Table2 tbl2a
ON
tbl2a.[ID] = tblB.[ID] AND
tbl2a.[Date] = tblB.T2xD
May still be a better way but as of yet I am still trying to think of it.
July 29, 2003 at 8:26 am
Thanks Antares686. Your idea helped alot. It did improve query time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply