Impact of module size

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Howdy,

I am just starting to refactor a moderate-sized CGI site (perhaps 80 scripts, typically 100-300 loc). A lot of the scripts use SQL to retrieve data via DBI. The actual SQL is typically stored in each individual CGI script. As you can imagine, certain queries are very common, and could easily be factored into a StandardQueries module.

My thinking is to replace a code fragment like this:

my $sql_get_username = '
SELECT  USER_NAME
FROM    USERS
WHERE   USER_ID = ?';

my $sth_get_username = $dbh->prepare($sql);
[download]

With one like this instead:

use StandardQueries;
my $sth = $dbh->prepare(StandardQueries::get_username);
[download]

I have three questions about this approach:

If StandardQueries gets large (say 100s of queries), what effect will this have on script performance?
I was not intending on exporting anything from StandardQueries: am I right in thinking this will avoid unnecessary overhead?
Are there any other ways of refactoring SQL-heavy code like this? In other words: is this approach reasonable, or are there pitfalls or better methods I didn't mention here?

Any thoughts much appreciated!

Comment on Impact of module size Select or Download Code

Replies are listed 'Best First'.
Re: Impact of module size by Abigail-II (Bishop) on Sep 12, 2003 at 18:49 UTC
It shouldn't. It may have a minimal effect on compile time, but with CGI there's no reason to compile for each request. If you want to save time here, use stored procedures instead of having the database server compile a query plan over and over again. Exporting is something done once, during compile time. See question 1. Keeping the SQL in each script has the advantage of not having to modify another file if you need to change the query. Abigail	[reply]
Re: Re: Impact of module size by dmitri (Priest) on Sep 12, 2003 at 20:54 UTC
I really have to disagree on no. 3. Keeping all queries in one place is a good practice: it makes it easy to modify queries, since you know where to look for them; other modules are not cluttered with SQL statements, which can be long and messy; it makes it easy to insert a level of APIs to support more than one flavor of SQL.	[reply]
Re: Impact of module size by adrianh (Chancellor) on Sep 12, 2003 at 22:18 UTC
Are there any other ways of refactoring SQL-heavy code like this? In other words: is this approach reasonable, or are there pitfalls or better methods I didn't mention here? Rather than putting just the SQL into another module, I would put all of the DB related code there using something like DBIx::AnyDBD. So rather than something like: `use StandardQueries; ... my $sth = $dbh->prepare(StandardQueries::get_username); ... # some code to get the username using the statement handle` [download] I would have `use StandardQueries; ... my $username = $dbh->get_username($user_id);` [download] In the first you're just hiding the SQL. In the second you're hiding the database and a lot of the schema.	[reply] [d/l] [select]
Re: Impact of module size by simonm (Vicar) on Sep 12, 2003 at 21:25 UTC
Are there any other ways of refactoring SQL-heavy code like this? There are at least two other approaches to consider: Store the SQL in a text file rather than as Perl code. For example, Class::Phrasebook::SQL uses XML to store the query definitions, or you could quickly whip up a plain-text file format. Have the SQL generated for you rather than hard-coding every query you might ever need. For example, DBIx::SQLEngine would let you write the above query as: `my $username = $sqldbh->fetch_one_value( table=>'USERS', columns=>'USE +R_NAME', criteria=> { 'USER_ID' => $userid } );` [download]	[reply] [d/l]