Re: Newbie help
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/21/04
- Next message: Alejandro Mesa: "RE: Very Frustrating Problem with DateTime"
- Previous message: Rafael Chemtob: "Numeric in VARCHAR field"
- In reply to: Steve: "Re: Newbie help"
- Next in thread: Demetri: "Re: Newbie help"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Sep 2004 21:29:02 +0200
On Tue, 21 Sep 2004 16:17:17 GMT, Steve wrote:
>Please
>elaborate on why my design is so "hideous" and how I might accomplish the
>same thing (gather data from a web source as rapidly as possible and store
>that data for later analysis again as rapidly as possible) in a less
>"hideous" way.
Hi Steve,
I think I already answered that question in my first reply in this thread:
start with a good design. Normalize the data, put date and time in one
datetime column and forget the idea of a seperate table for each date. I
don't usually quote Joe Celko (many of his iddeas are great, but I have
trouble with his writing style in newsgroups), but in this case I will:
you are using a relational database to emulate a 1960's file system.
If you insert raw data because 3,000 rows per 15 seconds is too much to
clean up, fix THAT instead of putting mess in your table. I think that 15
seconds is a lot of time to clean up some HTML code from 3,000 rows.
Don't leave out data from you rows because the value didn't change since
the previous measurement. Just put in all the values you got. Yes, this
will take some extra disk space. So what? Hard disks are awfully cheap
nowadays. (Plus, if you strip the HTML and store the numerical information
as numerical, you'll save a lot of space - maybe even more than this
replacing with NULL gets you!)
If you someday decide that you no longer need to monitor some value, or if
you want to start monitoring more values, get back to your design, make
changes, convert your existing tables and change your code. Don't try to
write code that will always work, regardless of any schema changes. The
main problem of such code is that it will indeed always work, even if it
really shouldn't. They can really cause a horrible mess if you're not
careful.
Think hard about what indexes to use. Run tests with realistic data to
compare various options.
If you really must have the best possible speed, invest in your hardware.
Get lots of RAM. Make a second copy of your database for querying, so that
queries and updates don't interfere with each other. Get more RAM. Make
sure that tables being written to, log file and tables being read from are
all on seperate devices. Use RAID 0 or RAID 1&0 for data, RAID 1 for log
files. Spend the rest of your money on even more RAM.
To be honest - all this is just my opinion. And I happen to be someone who
tries to avoid dynamic SQL whenever possible. I won't say that I'll never
use it, as I sometimes do use it, but I try to steer clear of it for as
long as possible. So I'll also reiterate what I said before: if it works
for you and you're happy with it - good for you. :-)
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Alejandro Mesa: "RE: Very Frustrating Problem with DateTime"
- Previous message: Rafael Chemtob: "Numeric in VARCHAR field"
- In reply to: Steve: "Re: Newbie help"
- Next in thread: Demetri: "Re: Newbie help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
