Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: perl & SQL best practices

by JavaFan (Canon)
on Apr 27, 2012 at 20:56 UTC ( [id://967718] : note . print w/replies, xml ) Need Help??


in reply to perl & SQL best practices

There's no best practices, and you aren't giving us much useful information to work with.

First, you security/SQL injection: use place holders. That should cover most of the cases (although there are still cases where you cannot use them).

But performance, it all depends. What are you doing? Where are your bottlenecks? Inserts? Queries? Do you have an online transaction system, or are you dataware housing? How many clients/server do you have? With many clients, it makes sense to offload tasks from the server -- but if the clients are already busy with other tasks, it may make less sense. Just focussing on the database server itself, where is your current bottleneck? How's your disk I/O? You storage? Can you hold the entire database in RAM? How's the network doing? How's the fibre doing? How's your replication lag?

Unless you have (detailed) knowledge of how your systems work, what the current bottlenecks are, and where you would l like to be, anything you do with the idea of performance is just stabbing in the dark, with half the people in the room being your friends. You should measure, measure and measure again, and be prepared that in 3 months, the situation may be different, and you will have to redo your work.

Replies are listed 'Best First'.
Re^2: perl & SQL best practices
by ksublondie (Friar) on Apr 27, 2012 at 22:11 UTC
    ...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!

      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.

        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?