Need Help retriving data from 4m row table

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need Help retriving data from 4m row table

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2008-04-17 : 10:07:51

I am trying to join some stock price data so that I can calculate the return over a time period. My price table is approximately 4 million rows and I am trying to figure out a more efficient way to get the two prices on each row. As you can see below I am currently joining the price table twice. when i look at the estimated query plan it shows a cost of 22% for the first join and a 31% for the second join. Anyone have any ideas as to how I can make this more efficient. Right now if I run it using a scenario which returns approx 95k rows it takes 5 seconds. It seems like it should be quicker but maybe I am missing something. As far as indexing I noted which columns are indexed below. Any help is greatly appreciated as I have been struggling with this for a while.

Thanks


--CREATE SAMPLE DATA
DECLARE	@StockPrice TABLE (ID INT, Date_Id INT, Dt smalldatetime, Price smallmoney)

--clustered index: id and date_id which is a primary key
--non-clustered index:  Id, Date_id, price 

INSERT	@StockPrice
SELECT 10, 92, '01-31-1990', 10.00 UNION ALL
SELECT 11, 92, '01-31-1990', 18.00 UNION ALL
SELECT 10, 93, '02-01-1990', 10.25 UNION ALL
SELECT 10, 120, '02-28-1990', 10.50 UNION ALL
SELECT 11, 93, '02-01-1990', 19.25 UNION ALL
SELECT 11, 273, '07-31-1990', 19.00 UNION ALL
SELECT 10, 273, '07-31-1990', 11.00 UNION ALL
SELECT 11, 457, '07-31-1991', 20.00 UNION ALL
SELECT 10, 457, '07-31-1991', 11.50 

DECLARE @Date TABLE (Date_ID int, Dt smalldatetime, MonthEndTradingDay bit, MonthSequence int)

INSERT @Date
SELECT 92, '01-31-1990', 1, 2844 UNION ALL
SELECT 93, '02-01-1990', 0, 2845 UNION ALL
SELECT 94, '02-02-1990', 0, 2845 UNION ALL
SELECT 273, '07-31-1990', 1, 2850 UNION ALL
SELECT 457, '01-31-1991', 1, 2856 UNION ALL
SELECT 512, '03-27-1991', 0, 2858 UNION ALL
SELECT 638, '07-31-1991', 1, 2862

DECLARE @Stocks TABLE (Id int, Ticker varchar(5))

INSERT @Stocks
SELECT 10, 'INTC' UNION ALL
SELECT 11, 'MSFT'


--THIS SP NEEDS TO BE OPTIMIZED
DECLARE @months	int	--forward return 
SET @months = 6

SELECT t.id, t.CurDt, d.price as CurPr, t.FwdDt, e.price as FwdPr
FROM
(
	SELECT	 a.id
			,b.dt as CurDt
			,b.date_id as Dt1
			,c.dt as FwdDt
			,c.date_id as Dt2
	from @Date b
		left outer join (select * from @Date where MonthEndTradingDay=1) c on 
				c.MonthSequence = b.MonthSequence + @months
	cross join (select * from @stocks where id between 10 and 11) a
	WHERE b.MonthEndTradingDay = 1 and b.dt < '7/1/1991'
) t
INNER JOIN @StockPrice d on d.id = t.id AND d.date_id = t.Dt1
INNER JOIN @StockPrice e on e.id = t.id AND e.date_id = t.Dt2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources