|
 |
Tom Austin wrote:
> Having unoptimized programs makes it very slow. Programs written as
> fast as possible do not benefit from being efficient.
Modern database systems are designed to take normalized tables and make
accessing them very fast. That's the fundamental operation a relational
database management system performs.
How big is your total collection of active databases right now? If it fits
in (say) a gigabyte, chances are you can't possibly make it too slow.
If it *is* too slow when done "right", *then* you get out the query analyzer
and look to see what it's spending its time doing, and you add the one index
that cuts that time from minutes to miliseconds.
> Thanks for the acronym - more info to go on :-)
>
> I don't know if we will try or need to set up transactions, but they
> could be helpful if something is not a simple insert or update.
You will need transactions. Trust me on this one. :-) If nothing else,
you'll need transactions when you're archiving out an old job.
That said, it's surprisingly common that if you structure your queries
right, you can make every operation essentially an "append to this table",
followed by an eventual "update that table in a way that if it fails, we can
reconstruct that table entirely from scratch". Think Journal -> General
Ledger.
>> The key is an identifier that won't change. What text do you have for
>> the entity that won't change? The company name? The road the pole is on?
>> The name of the person you should talk to about it? All these things can
>> change.
>
> point made - thanks :-)
That siad, btw, it's often the case that you *can* find text that won't
change often enough to make a difference. Stock ticker, street name, radio
station call letters. If it changes rarely enough that you're willing to
treat it as an "delete all the old, insert all the new" sort of transaction,
using text is OK. One generally doesn't do this with people, but saying "if
you change your stock ticker, you have to treat it as selling all the old
shares and getting the same number of new shares from the new ticker name"
isn't too unreasonable.
> OK, you want a job surveying utility poles :-) You have good insight.
I've just done this dozens of times in the last couple decades.
> At this point I am not used to setting up primary keys with multiple
> columns - but this is making some sense. The question we have is if we
> have time to implement some such beast :-)
Look at it this way: This is the data that is vital to your company, in the
strictest sense. Your company's job is to manage and evolve this data. You
don't have time to get it wrong.
What about your current system is so broken that you can't afford the time
to fix it?
The other possibility is to hire someone who actually does this sort of
stuff for a living for long enough to show you how to set up the basics of
the database.
>>> Exporting the data for archival purposes could be a good idea. Even
>>> exporting a fully functional database with only the particular job
>>> data required could be good.
>>
>> All those things are not-too-hard if all the data is in the database.
>>
>
> Yes, and if the database schema is identical, the same programs can use
> any database - from mySQL to .mdb files.
That's the goal. As you get more sophisticated, you find that breaks down.
When you use views in your "real" database and you archive it to something
that has no views, you find yourself with a problem.
That said, even a cheap machine nowadays has more than enough processing
power to do whole boatloads of database processing.
About how much data do you have, in total and in active jobs? Many people
seriously underestimate just how much processing even a $1000 machine can do
on a SQL database.
> Well, almost free - just takes time to set up. By the time all is said
> and done I figure that mySQL will take about $1k - $2k in my time to set
> up reliability. Need to get it installed on our server and set up
> connection methods and backup methods. Not too difficult, but I don't
> do it every day.
Be sure to factor in some ongoing maintenance time. Occasionally your
replication will break, or something like that, and you'll have to fix stuff
in a panic. :-)
--
Darren New, San Diego CA, USA (PST)
"How did he die?" "He got shot in the hand."
"That was fatal?"
"He was holding a live grenade at the time."
Post a reply to this message
|
 |