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