Re^2: perl & SQL best practices

[ksublondie]

...you aren't giving us much useful information to work with.

Well, I guess I was asking as a more theoretical feedback-on-what-others-do-so-I-can-develop-a-guideline-for-my-own-current-and-future-coding question as opposed to a specific "Here's what I got, what do I do now???".

But if it you're dying to know more details, I have 2 sql servers (one sql2005 and the other sql2008...all new db's are setup on the sql2008 and the sql2005 db's will eventually get upgraded as time allows...HA! Sorry, I can't keep a straight face typing that) with less than 20 db's each that are accessed by multiple programs...I have simple scheduled tasks that do nothing more than create reports which are then emailed to a few lucky individuals, and then I have few full blown CGI web apps that do transactional functions and everything in between...all with perl, of course. I currently don't have anything on the sql servers besides the data itself and some administration functions. All my sql interaction is through sql within my perl code.

Relatively speaking we're rather small. The total number of individuals accessing any single db will be AT MOST 200...and that's if everyone is in at the same time. My db's range in size of 17M-84G. Bottlenecks? I don't necessarily have any (that I know of anyway). Network, disk, RAM, etc is fine as far I as I can gather. As time goes on, we are adding more canned applications, along with my custom coding, that depend on sql server. I'm just trying to be a little proactive in my development and prevent any "Darn, I shoulda..." problems in the first place if I can. Remember, I'm the only programmer and I hate nothing more than going back to fix code, let alone figure out what I did in the first place!

[JavaFan]

So, you have no bottlenecks, no goals (performance wise), you're rather small, and all you can tell about your future is that you're adding "canned applications, along with my custom coding". Yet you ask from us suggestions that will prevent you from saying "Darn, I shoulda" in the future.

What do you expect to get? I mean, even if I said "don't smoke", it doesn't mean it's going to prevent a "darn, I shoulda" in the future. Some people smoke all their life, and never get cancer, and people get cancer who don't smoke.

Seriously, set goals, and measure your performance. Act if necessary. Magic bullets do not exist.

[ksublondie]

So, you have no bottlenecks, no goals (performance wise), you're rather small, and all you can tell about your future is that you're adding "canned applications, along with my custom coding".

Yea, you're correct.

What do you expect to get?

Your advice. Tell me what you do or would do as a programmer if you had complete control over your db server. Are there any practices you yourself follow? Of course, we all use strict. Do you have similar rules you follow and/or made in regards to sql db interaction? Do/would you develop your sql from your perl or instead put it outside on the db server? And what are your reasons for doing it that way?

[JavaFan]

Tell me what you do or would do as a programmer if you had complete control over your db server.

How many times do I need to repeat: IT DEPENDS. Define goals. Measure. Know what the budget is. Work out what needs to be done. There are no magic bullets.

Of course, we all use strict.

That's like saying: "what should I do when I need to transport something? Of course we all check our headlights first." If you don't tell me what you need to transport from which pickup point to which destination, at what time, what the budget for it is, what your resources are and when it has to be there, don't expect me to tell you whether you should use a bicycle, a pick-up truck a train, or whether you should hire a specialist company. Magic bullets do not exist.

Do you have similar rules you follow

I do have rules I follow (but they aren't as mindless, boring and and a bit more useful than "use strict"), and they are: do not do anything without knowing what's going on, what the goal is, and what the budget is (budget is to be taken in a broad sense. It includes, but is not limited to: money, people, time frame, regulations, knowledge). Aka, don't run around as a headless chicken, going in any direction monks in the peanut gallery are shouting to you.