|
Hi guys, I'm relatively new to databases and have been playing with SQL Server 2008 Express for some time. I want to build a database on stock prices and various other stock-related indicators across time. So basically it's going to have three dimensions: name of the stock, stock attributes, and time. The user will be able to query for certain stock's performance on a particular attribute across a designated time period.
Normally this would be a perfect job for OLAP (multidimensional database). However, due to financial constraint and other reasons  , I am not considering OLAP at the moment. With my limited knowledge on databases, I think I could achieve the above by using:
1) Relational database: if I need to track 1000 stocks, I will construct 1000 tables, with time and stock attributes on each stock table.
2) XML: I am relatively new to XML too, but it seems that with some clever XPath/XQuery coding, dumping all the data into one huge XML database is not a bad idea (or is it?), as long as data can be effectively retrieved.
In terms of speed/performance, maintenance convenience, and server-memory efficiency, which of the above two is a better choice? Or do I really need to migrate to using OLAP?
Sorry if this is a simple question, I am a total newbie regarding databases. 
Thanks in advance!!
|
|
|
|
|
I strongly advise against 1,000 tables. This is not a scalable solution and many of your queries will take a very long time as you will need to join many tables.
|
|
|
|
|
I wouldn't store similar information on different stocks in different tables, just the one table with a field added to identify the stock would be fine. It would be able to provide more functionality with less code, as you can now easily search/list over many stocks, and never need to enumerate the tables.
|
|
|
|
|
Thanks for the replies guys. But if I add a field to identify the stocks, it would be one huge table and it won't look as elegant.
By the way, are you suggesting the following structure:
Time StockName Open High Low Close EPS Dividend Return% ... ... ...
1/2/07 Citi 20 23 19 20.5 5m 5 4% ... ... ...
....
....
....
11/25/09 Citi 4 5 3 4.5 2m 2 -10% ... ... ...
1/2/07 BofA 35 37 32 36.4 7m 10 7% ... ... ...
....
....
....
11/25/09 BofA 12 13 11 10 3m 1 -7% ... ... ...
1/2/07 MSFT 45 47 41 42.5 28m 3 7% ... ... ...
....
....
....
11/25/09 MSFT 4 5 3 4.5 2m 2 -10% ... ... ...
If this is true, assuming that the table will contain 1000 stocks with 50 attributes for a 3-year period, this table will need to contain: 1000 x 50 x 260(business days) x 3 = 39,000,000 stock prices. Is this a little bit too large for one table? (If it is, as an alternative perhaps I could break 1000 stocks down into several smaller tables based on the market in which they are traded in? So one table for each market. What do you think?
Thanks!
|
|
|
|
|
James Shao wrote: are you suggesting...
yes I am. I haven't done this with millions of records, my DB apps aren't large, but yes that is what I would do. I avoid tables with identical structure, I only use one Persons table, one Vehicles table, etc.
|
|
|
|
|
Thanks a lot, I'll give this a try and come back if I've encountered errors.
|
|
|
|
|
My databases ARE in the millions of records, Luc is correct have 1 structure with all the information. Do not store any data twice (database design 101) eg is you are have industry or sector for an equity then have an equity table with the static data and a related table with the tick information.
If table size becomes an issue there are many better option than splitting by stock name. Partitioning by time is a better solution (partition by year would do). Doesn't express include analysis services (have not checked)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Breaking it down by market is not a good idea, but you do need to know what market something traded on. A single stock may trade on multiple markets in the US, even when you ignore after-hours trading and dark pools.
I've personally worked with global financial databases with billions of records. The DBA sets the appropriate indices and partitions and performance was fine.
What you do not want to do is design something fragmented where it is hard to change the structure later without losing your data.
As far as memory usage, you can use SELECT statements and limit the amount of memory that sql server will use. So if you have a 10 GB table, you can tell sql server not to use more than 500 MB of RAM for instance.
|
|
|
|
|
Hi Ted, thank you for the suggestions. I also feel that fragmenting the data is not a good idea. But if I dump them all into one table, that would create big redundancy in the date column (my 1st column), since I'll need to repeat it for every stock I have in my database. Is this okay?
Thanks!
|
|
|
|
|
Yes, you should use the date in your table (which is 4 bytes). If you created another table holding dates to try to reduce the size, then you would have an additional bottleneck.
Your primary key will be composed of multiple columns including ID, Date, and possibly source/exchange. If you really needed to save space in your table, you could use a smallint (2 bytes) for your date and map the minimum small int to Jan 1, 1970 or wherever you will start your database. Then it will be quick to add/subtract an offset to go back and forth from Excel's date format.
|
|
|
|
|
Thank you Ted, I'll give it a try and see how it goes.
|
|
|
|
|
hello all.
I want to validate category name before insert all information about category into database.
The following condition is the way i want to compare:
Ex: ------------------------------------------------------------------------
| name in TblCategory | name in Text box | comparative result |
------------------------------------------------------------------------
| white bag | whitebag | existing |
------------------------------------------------------------------------
| whitebag | white bag | existing |
------------------------------------------------------------------------
| White Bag | white bag | existing |
------------------------------------------------------------------------
I use this following function in sql database:
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblCategory c
WHERE (c.c_name=@name OR c.c_name=REPLACE(@name,' ',''))
AND c.c_id <> @id
)
SET @result=1
ELSE
SET @result =0
END
this give me bad result if TblCategory (white bag) and TextBox to compare has word(whitbag)...this can't compare to me
|
|
|
|
|
I already answered your question in the other forum. Don't cross post. Please follow the post from there.
|
|
|
|
|
Hi All,
I have a SQL database column "ID", in which I added one by one entries as 2,5,6,1,8... randomely.
I want to arrange these entries in Ascending order so that in datagridview it get displayed in order 1,2,3,4... like this.
Can someone help me on this?
|
|
|
|
|
Ever tried the ORDER BY clause?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Try this
declare @t table(id int)
insert into @t
select 3 union all
select 1 union all
select 5 union all
select 4 union all
select 2
Query:
select * from @t order by id
Output:
id
1
2
3
4
5
Niladri Biswas
|
|
|
|
|
I have already inserted "EmpID" as 1,2,3,4,5,6,7,8,9,10,11,12. But when i see the column data of table, i found that the data is arranged like this:
1
10
11
12
3
4
5
6
7
8
9
Since, the data is already inserted in the above order, i found that in crystal report the same format is coming...but i want the order in exactly ascending order in report finally, if not possible in database.
Can someone help me on this?
|
|
|
|
|
What's a good way to modify a large number of stored procedures? I took over a project that is not using SET NOCOUNT ON, i.e. the following query returns several hundred rows.
SELECT Distinct SO.Name, SC.TEXT
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.colid=1 --multiple colid's are utilized when the procedures text is very long
AND NOT UPPER(SC.TEXT) LIKE '%SET NOCOUNT ON%'
ORDER BY SO.Name
modified on Friday, November 27, 2009 2:17 AM
|
|
|
|
|
hi Ted
SET NOCOUNT ON is used if you dont want to show results. Lets say i have a Query that Updates a Table, at the end it will give me something like
100 Rows Affected
to make sure that the update does not show that message , you use SET NOCOUNT ON
i have never seen this use in a query passed as a parameter, am not sure if this is correct because as i remember correctly you are not supposed to do that. mybe you can explain your challenge clearly and we can suggest another option
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I wanted to add SET NOCOUNT ON in the body of the stored procedure after AS. I am relatively new to SQL Server (I used Oracle before) and the articles I read on google suggested that you should always SET NOCOUNT ON before executing queries inside a stored procedure.
|
|
|
|
|
then the correct way to do it is the Following
Create Proc myprc
as
set nocount on
--Do what Ever and set it back to off
set nocount off
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
That's not what was asked, he knows how to set nocount on.
He wants to know if there is a way to easily modify all of his stored procedures to include this line.
|
|
|
|
|
I must have misunderstood him , No there is no way even SQL Compare wouldnt do that
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You could perhaps do this using a cursor with sysobjects and sp_helptext.
You would use the cursor to step through "select name from sysobjects where xtype = 'P'".
You could then get the stored procedure using sp_helptext and modify it and execute accordingly.
|
|
|
|
|
Or you could go to your version control system, check out the scripts for the stored procedures, modify them as required (using a little noddy program to automate the process), test them, check them back in again and then re-run them to drop and recreate the stored procedures in the target database.
Oh, dear. No scripts.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
