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 |
|
legacyvbc
Starting Member
37 Posts |
 Posted - 2007-09-27 : 13:18:19
|
| I'm creating a db to be used to store historical trades which will then be used to report on various strategies/returns etc...I'm struggling with the correct way to store things like price etc...Right now I have a table tStock which has pkStockId,StockName,SectorI have a table tPrice which stores the historical prices which has pkCounter,fkTickerId,fkDate,Price,Volume. This seems odd to me since dates are being repeated. Right now I have about 100 stocks in table tStock and the tPrice table has 10 years of price data for each stock so there are a date range of historical prices which is approximately 252,000 records. That isn't a ton but I will probably end up with 500+ stocks and 20 years of data. Am I storing this correctly? Right now I have the pkStockId indexed and the pkCounter index and I also created an indes using fkTickerId and fkDate. Is there a way to create the tables such that looking up a price is faster? The reason I would like it to be faster is that when analyzing the trade history I need to get the portfolio market values based on various dates so at a certain date I will sum all buys - all sales for every stockid in the portfolio table and then look up the prices for each to get the market value of the portfolio on a particular day. I feel like I am missing something in the design process but don't have experience in this so I am asking for help.Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 13:51:51
|
"This seems odd to me since dates are being repeated"Can you just clarify that? You have multiple rows for a single date (for the same Stock)? Or is there a Time component too?"Is there a way to create the tables such that looking up a price is faster?"yeah, put that in the index too  If you can "cover" the query with the index it will save physically getting the row. But if you need other columns on your query [from the historical prices table] that won't help.SELECT pkStockId,StockName,Sector, PriceFROM tStock JOIN tPrice ON pkStockId = fkTickerIdWHERE fkDate >= '20070101' AND fkDate < '20070201' and assuming an index on fkDate, which should also include fkTickerId (assuming that it part of the PK and the PK is clustered), then you have both of those satisfied from the index. So if you append Price to that index then the index will cover the three columns used in this query.Even if you don't, the index will still "cover" fkDate and fkTickerId, which will take care of both the WHERE and the JOIN.But "it all depends" comes into it as well, so its a good idea to check the Query Plan that is generated, try adding / dropping indexes and see how that changed the plan and the Logical I/O and Scan counts [which is a good measure of how hard SQL Server has to work to answer the query, and reducing them generally results in better real-world performance.Kristen |
 |
|
|
legacyvbc
Starting Member
37 Posts |
Posted - 2007-09-27 : 20:14:08
|
| Multiple rows of a single date but the rows are different because each row represents a different stock on that particular date. I will look at the query plans and try some of your suggestions. Thanks for the help. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Trading History Database Help