Tom Austin wrote:
> I think most of our slow downs is currently in the applications that 
> access it.  Basically inefficient routines and use of objects.  A 
> required approach when coding time as of the essence.

Yeah, but now you hve some luxury to do it right, I'd wager, since you 
already have a working system.

> Does it make sense to set up a transaction wrapper on even simple single 
> updates?

If you're modifying more than one row at a time, yes. In most database 
engines, each statement you submit becomes its own transaction. This isn't 
true on all types of MySql tables. For example, if you have a MyISAM table 
and you say "update blah set x = 50 where y = 3" and it gets interrupted 
half way, you might update only half the rows where y = 3.

A single append of a single row is atomic. You're not going to corrupt the 
database per se. Just the consistency of the data inside the database.

So, yes, there's really so little overhead in using a transaction around 
anything that affects more than one row that it makes no sense not to do so.

>> 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.
>>
> 
> OK, I've been dealing with databases for 15 years now - on mostly a low 
> level.  that just went right over my head.  Makes sense, but I have no 
> concept of how it is implemented.

I've usually done financial databases. So, basically, all the history of 
everything in and out of the database is recorded in one set of tables, 
while the current state is in a second set of tables.

Like your checkbook: Every check you write gets a row appended, and you 
never change that row except to set a flag that you never unset (the "this 
check cleared" flag).  But there's another table that lists each account and 
says how much money is in it. In theory, you can construct the latter from 
the former, if you keep all your old checkbook registers around.

> for some reason I am hesitant to present the key as something that the 
> end user interacts with.  Maybe I'm a control freak :-&

No, there's almost never a reason to present the key that way. That doesn't 
make it less useful as a key. :-)

>>> 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.
> what - survey telephone poles?

No, I only did that once. :-) Design databases from scratch.

> that is very correct.  Our first stab was fast and mostly dirty.  We 
> spent a lot of time to try to get the data right, but we missed some 
> pieces of data.

That's not unusual. The trick is to have the database structured in a way 
you can add the missed data later without *changing* what's already there. 
The best way to do that is to primarily record into the database exactly 
what's outside the database, reflecting reality as closely as possible.


>> What about your current system is so broken that you can't afford the
>> time to fix it?
>>
> 
> It has layers upon layers of fixes.  The end users had to fight with it 
> to get it to put out data correctly.  it was an ad-hoc rich system to 
> the point where I refused to make any more changes because of the risk 
> of data corruption/loss and our client getting crap for a deliverable.

Been there done that. Along with "before I start, has this ever been backed 
up?" Answered by "No, it has never been reliable enough we could get a 
backup of the data."  Followed by "Thanks, but I like my current job enough."

>> 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.
>>
> 
> It's a balance.  Depending on our requirements it could take just as 
> much of our time working with the guy and doing it ourselves.

This is true. I don't think it would take any less time, but you'd probably 
have a more robust system when you were done.

> yes, one just has to pay attention and not set up things that break.
> It could be that applications do not rely on the views (or other 
> non-common features)  But then one could be crippling themselves.

Yes. It's better to just say "if we need multiple archives of this database, 
we'll use the same engine."

> yes, I don't think it will take much of a machine.  We have an old 
> server box (PIII 600 I think) that would likely do just that.  Or I 
> might incorporate MySQL on our current file server (Linux) to simplify 
> things and use the old box for development.

Yeah. And you'll likely want a replicated server as well.

> Luckily none of them were damaging.  Just annoying and cost a couple of 
> hours of everyone's time.

Yeah, that's much better than the place catching on fire. :-)

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