|
![](/i/fill.gif) |
On 3/8/2011 4:43 PM, Darren New wrote:
> 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.
>
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.
>> 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.
>
I've worked with transactions some in the past and they make a lot of
sense. I'll have to try to incorporate them as we move forward.
Does it make sense to set up a transaction wrapper on even simple single
updates?
> 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.
>
> 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.
>
for some reason I am hesitant to present the key as something that the
end user interacts with. Maybe I'm a control freak :-&
>> 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?
>
> 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.
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.
>
> 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.
There were some cool features that I implemented - like a GIS type
interface - before GIS systems were popular.
> 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.
>>>> 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.
>
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.
> 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.
>
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.
>> 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. :-)
>
yes, just like last week with out servers.
In the past 3 years I have had 1 or 2 incidents that required immediate
attention.
The past week brought no less than 5 of them.
From full OS drives to failed RAID mirrors.
Luckily none of them were damaging. Just annoying and cost a couple of
hours of everyone's time.
Post a reply to this message
|
![](/i/fill.gif) |