Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johnvm
Starting Member
18 Posts |
 Posted - 2007-07-27 : 03:33:12
|
| I have a very large database (has multiple tables, each with about ~2 billion+ rows), and run a large # of queries on it. I've been playing with a lot of T-SQL trying to figure out what the fastest/best way to run this query is:DECLARE @xml XML;SET @xml = N'<NewDataSet> <Table> <DATETIME>2006-01-02 15:46:00</DATETIME> <SYMBOL>SMH</SYMBOL> </Table> <Table> <DATETIME>2006-01-02 16:01:00</DATETIME> <SYMBOL>IUSA</SYMBOL> </Table></NewDataSet>';WITH XmlResultSet (symbol, datetime) AS ( SELECT c.value('(SYMBOL/text())[1]', 'nvarchar(50)') AS symbol, c.value('(DATETIME/text())[1]', 'datetime') AS datetime FROM @xml.nodes('/NewDataSet/Table') AS symbols(c))SELECT *FROM XmlResultSet xOUTER APPLY (SELECT TOP 1 [PRICE] FROM [2006] WHERE [2006].SYMBOL = X.SYMBOL and [2006].DATETIME >= X.DATETIME ORDER BY [2006].DATETIME ASC) AS yMy [2006] table has a clustered index on SYMBOL and DATETIME. Right now running this on ~120 rows takes about 90 seconds. Is there anything I can do to speed up/optimize this query? I've included the XML parameter above which reflects 2 example records passed to the query. Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-27 : 04:46:41
|
[code]SELECT w.Symbol, w.DateTime, w.PriceFROM ( SELECT x.Symbol, x.DateTime, y.Price, ROW_NUMBER() OVER (PARTITION BY x.Symbol ORDER BY y.DateTime) AS RecID FROM XmlResultSet AS x LEFT JOIN [2006] AS y ON y.Symbol = x.Symbol AND y.DateTime >= x.DateTime ) AS wWHERE w.RecID = 1[/code]
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
How can I optimize this query?