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

Posted on 2005-04-25
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

    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

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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now