Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re: perl & SQL best practices

by Jenda (Abbot)
on Apr 27, 2012 at 21:59 UTC ( [id://967723] : note . print w/replies, xml ) Need Help??


in reply to perl & SQL best practices

Dynamic SQL is dynamic SQL no matter where you construct it. There are times when it is the way to go, but you should try to keep them to a minumum. A (possibly generated) structure of

IF (@Whatever is not NULL) IF (@Other is not NULL) SELECT ... WHERE @Whatever = Whatever and @Other = Other ELSE SELECT .. WHERE @Whatever = Whatever ...
Tends to perform better if used often. Just make sure it doesn't get way too big. And keep in mind that if there is no index on a column (@Whatever is NULL or @Whatever = Whatever) is enough, no need to test that variable in an IF and use separate selects.

Jenda
Enoch was right!
Enjoy the last years of Rome.

Replies are listed 'Best First'.
Re^2: perl & SQL best practices
by ksublondie (Friar) on Apr 27, 2012 at 23:46 UTC
    Just make sure it doesn't get way too big.

    "Big" as in results, lines of code, too many if/else/then, or were you referring to something else?

      Lines of code. The code has to be compiled the first time you use the stored procedure that encloses the structure and if it gets way too long, the compilation will take too much time.

      Jenda
      Enoch was right!
      Enjoy the last years of Rome.