• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

I'm new - Need help setting up a database please.

Databases are very much not my area of expertise... But my program now needs the speed of MSDE. Access isn't cutting it!

I'm going to provide you with all the information I can think of regarding the database I'm creating.
Please help me create a very fast database... I'm really looking forward to getting this database nailed down so that I can start working on more interesting parts of the project!

Information about Database:

General
- I'll be connecting to the database via VB.NET code
- 1 connection will be used only
- Thousands of queries per second (I need speeeed. Database is my bottleneck!)
- Each query will return around 5000 records

Tables
- 1 Table

Coumns
- Symbol: Text (max 6 chars)
- Date: Date
- Price: float
- Change: float
- Volume: int

a "visual" for the database setup

INTC 06/07/95 20.0 1.0 1200000
INTC 06/08/95 20.1 0.1 1234325
INTC 06/09/95 20.2 0.1 1123444
MSFT 06/07/95 3.0 0.2 1234555
MSFT 06/08/95 3.0 0.0 1234444
MSFT 06/09/95 3.0 0.0 3433344

My high speed database will have approximately 365,000 rows of this data.

How often do I need to write to this database?
- VERY SELDOM - Only during database creation or very seldom database updates (perhaps once weekly)

Typical Queries?
- Select ALL from Table where Date = "06/07/95"
- Select All from Table where Symbol = "INTC" AND Date = "06/07/95"
- Select ALL from Table where Symbol = "YHOO"
- Select ALL unique Symbols from Table

NOTE: I expect these quieries to return around 5000 results a piece, and I expect to do these queries hundreds of times per second.

I mainly need help setting up the database so that it will have high performance with the above queries.

Indexing is obviously a big concern of mine.

Thanks a lot for the help!


0
oxygen_728
Asked:
oxygen_728
1 Solution
 
nmcdermaidCommented:
Whaddya mean??? The database part IS the interesting part ;)


Going from Access to MSDE will be a huge performance boost in itself.


Some things come to mind:

1. Use the char datatype rather than text
2. Put one index on Date
3. Put another index on Symbol

I think there is a trick you can do to reduce the sixe of the index by combining the two fields, but I'll leave that to the uber-experts.

Selecting only the columns you need (rather than all) will speed it up marginally.

After you've set it up, profile it for a while (using profiler) then run it through the indexing wizard.

Make sure MSDE is the only App on the box



I don't think these things are avaialble in MSDE, but:

You can partition bits of the table on to different disks and get an IO increase that way.

For speed you theoretically put the indexes on one physical disk and the data on another physical disk. You've gotta have two physical disks, you cant just partition them.

If you want summary data there are two things to do to speed it up a hell of a lot:

1. Build an OLAP cube (though probably unlikely to be of use in this case)
2. Create some indexed views
0
 
oxygen_728Author Commented:
Thanks for the advice. I'll check this out as soon as I get some time!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now