Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2005
    Posts
    63

    Unanswered: Reference tables

    I have over 150 reference tables with similar structure and I stored the SQL to fetch data from them in a lookup table with slightly varying where clauses. I fetch the sql from the lookup table and then send it to fetch the data for the dropdowns/etc from my middleware (PHP). This way, I could wrap the logic into a single class for all the reference tables instead of writing a separate class for each reference table or bunching the logic into a large cluttered class.

    My question is, are stored procedures better, more efficient and more maintainable than using a lookup table that stores SQL as above?

    TIA.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't KNOW, but I'd guess that a stored procedure whould be preferable to storing SQL in a table, esepcially if the the lookups are performed frequently.

    Also depending on which veriosn of PHP you are using most of the class could / should be ingerited, so it should nt be a problem spawning 150+ sub classes.

    Again it depends on volumne of transactions as to whihc solution makes 'more' sense. the acaedmic / theoretical answer must be go stroed procedures.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2005
    Posts
    63
    Thanks! I just did a test run with a stored procedure on a reference table but after fetching the results, the server bombs saying MySQL 5.1 lost connection:

    General error: 2013 Lost connection to MySQL server during query

    and the rest of the queries for the page failed.

  4. #4
    Join Date
    Jul 2005
    Posts
    63

    Reference tables

    I have over 150 reference tables with similar structure and I stored the SQL to fetch data from them in a lookup table with slightly varying where clauses. I fetch the sql from the lookup table and then send it to fetch the data for the dropdowns/etc from my middleware (PHP). This way, I could wrap the logic into a single class for all the reference tables instead of writing a separate class for each reference table or bunching the logic into a large cluttered class.

    My question is, are stored procedures better, more efficient and more maintainable than using a lookup table that stores SQL as above?

    In any case, I just did a test run with a stored procedure on a reference table but after fetching the results, the server bombs saying MySQL 5.1 lost connection:

    General error: 2013 Lost connection to MySQL server during query

    and the rest of the queries for the page failed.

    TIA.

  5. #5
    Join Date
    Jul 2005
    Posts
    63
    Forgot to mention that fetching of data is done only ONCE per day as data is 'cached' to flat files to improve performance, anyway (either lookup table or stored procedures). I don't really store the SQL in the lookup table - just some values and flags and compute SQL for each lookup using the lookup PHP 5 class:

    TableName: not null
    Columns: if empty, default to 'ID, Description'
    WhereClause: nullable
    WhereLanguage: if true, append the current language code to where clause
    WhereStatus: if true, append the 'Status = 1' clause to where clause
    OrderBy: nullable; if not empty, append order by columns to the SQL
    Limit: nullable; if not empty, append limit clause to the SQL

    I think the lookup table is more maintainable because, I can update all/part of the lookup data using DML SQL and/or alter the lookup class (that computes SQL to fetch data) to affect all the lookups. For instance, if I want to update the WhereStatus or WhereLanguage or OrderBy clauses, all I have to do is update the columns or alter one PHP class. But I am trying to figure out if stored procedures are better and more efficient, performance-wise. If so, I would like to switch to SPs.

  6. #6
    Join Date
    Jul 2005
    Posts
    63
    Forgot to mention that fetching of data is done only ONCE per day as data is 'cached' to flat files to improve performance, anyway (either lookup table or stored procedures). I don't really store the SQL in the lookup table - just some values and flags and compute SQL for each lookup using the lookup PHP 5 class:

    TableName: not null
    Columns: if empty, default to 'ID, Description'
    WhereClause: nullable
    WhereLanguage: if true, append the current language code to where clause
    WhereStatus: if true, append the 'Status = 1' clause to where clause
    OrderBy: nullable; if not empty, append order by columns to the SQL
    Limit: nullable; if not empty, append limit clause to the SQL

    I think the lookup table is more maintainable because, I can update all/part of the lookup data using DML SQL and/or alter the lookup class (that computes SQL to fetch data) to affect all the lookups. For instance, if I want to update the WhereStatus or WhereLanguage or OrderBy clauses, all I have to do is update the columns or alter one PHP class. But I am trying to figure out if stored procedures are better and more efficient, performance-wise. If so, I would like to switch to SPs.

  7. #7
    Join Date
    Jul 2005
    Posts
    63

    Reference tables - PHP/MySQL

    I don't know why but I posted this message on PHP forum but it got duplicated on MySQL forum so I'm changing the subject-line a l'il bit:

    I have over 150 reference tables with similar structure and I stored the SQL to fetch data from them in a lookup table with slightly varying where clauses. I fetch the sql from the lookup table and then send it to fetch the data for the dropdowns/etc from my middleware (PHP). This way, I could wrap the logic into a single class for all the reference tables instead of writing a separate class for each reference table or bunching the logic into a large cluttered class.

    My question is, are stored procedures better, more efficient and more maintainable than using a lookup table that stores SQL as above?

    In any case, I just did a test run with a stored procedure on a reference table but after fetching the results, the server bombs saying MySQL 5.1 lost connection:

    General error: 2013 Lost connection to MySQL server during query

    and the rest of the queries for the page failed.

    Fetching of data is done only ONCE per day as data is 'cached' to flat files to improve performance, anyway (either lookup table or stored procedures). I don't really store the SQL in the lookup table - just some values and flags and compute SQL for each lookup using the lookup PHP 5 class:

    TableName: not null
    Columns: if empty, default to 'ID, Description'
    WhereClause: nullable
    WhereLanguage: if true, append the current language code to where clause
    WhereStatus: if true, append the 'Status = 1' clause to where clause
    OrderBy: nullable; if not empty, append order by columns to the SQL
    Limit: nullable; if not empty, append limit clause to the SQL

    I think the lookup table is more maintainable because, I can update all/part of the lookup data using DML SQL and/or alter the lookup class (that computes SQL to fetch data) to affect all the lookups. For instance, if I want to update the WhereStatus or WhereLanguage or OrderBy clauses, all I have to do is update the columns or alter one PHP class. But I am trying to figure out if stored procedures are better and more efficient, performance-wise. If so, I would like to switch to SPs.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please stop re-posting the same question

    your previous cross-posted threads were merged, and this one has joined them

    you posted in both the php and mysql forums, and i made a judgment call to leave the merged thread in the mysql forum, which is why you didn't see it here in the php forum

    now the 3-thread merged thread resides in the php forum (php moderators, feel free to move it to mysql)

    one thread per customer per problem, please

    Last edited by r937; 06-18-06 at 19:48.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •