[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2005-04-25
Medium Priority
Last Modified: 2010-03-19
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:

- 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

- 1 Table

- 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!

Question by:oxygen_728
LVL 30

Accepted Solution

nmcdermaid earned 2000 total points
ID: 13864022
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

Author Comment

ID: 13864119
Thanks for the advice. I'll check this out as soon as I get some time!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question