Page Processing Efficiency

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Michael Hill
    #1

    Page Processing Efficiency

    If I had a page that was being generated using coldfusion from queries
    to an oracle table would it be better response time:
    A) pulling the all the data using 1 query and iterating over the same
    result table multiple time producing desired arrays, or
    B) pulling specific data using specific queries with less production of
    arrays and proccessing after te data was returned?

    In the example I had 2 date fields called start and comp in a table with
    other like data and wanted to know what the min value was from both
    fields.

    So I have to "select * from the table where mynum='131'" and then
    process the result set

    or

    "select * from the table where mynum='131'" as well as 2 more queries:

    "SELECT min(min_start) as min_start
    FROM
    (
    SELECT min(BASE_START) as min_start
    FROM TASK
    WHERE my_NUM = '131'
    UNION
    SELECT min(ACT_START) as min_start
    FROM TASK
    WHERE my_NUM = '131'
    )"

    and

    " SELECT max(max_comp) as max_comp
    FROM
    (
    SELECT max(BASE_COMP) as max_comp
    FROM TASK
    WHERE my_NUM = '131'
    UNION
    SELECT max(ACT_COMP) as max_comp
    FROM TASK
    WHERE my_NUM = '131'
    )
    "

    Tags: None
    • Jim Kennedy
      #2
      Re: Page Processing Efficiency

      use bind variables. Lot of cold fusion folks don't and it hurts their
      scalability. Cold Fusion does support it.
      Jim
      "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
      news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...
      If I had a page that was being generated using coldfusion from queries
      to an oracle table would it be better response time:
      A) pulling the all the data using 1 query and iterating over the same
      result table multiple time producing desired arrays, or
      B) pulling specific data using specific queries with less production of
      arrays and proccessing after te data was returned?
      >
      In the example I had 2 date fields called start and comp in a table with
      other like data and wanted to know what the min value was from both
      fields.
      >
      So I have to "select * from the table where mynum='131'" and then
      process the result set
      >
      or
      >
      "select * from the table where mynum='131'" as well as 2 more queries:
      >
      "SELECT min(min_start) as min_start
      FROM
      (
      SELECT min(BASE_START) as min_start
      FROM TASK
      WHERE my_NUM = '131'
      UNION
      SELECT min(ACT_START) as min_start
      FROM TASK
      WHERE my_NUM = '131'
      )"
      >
      and
      >
      " SELECT max(max_comp) as max_comp
      FROM
      (
      SELECT max(BASE_COMP) as max_comp
      FROM TASK
      WHERE my_NUM = '131'
      UNION
      SELECT max(ACT_COMP) as max_comp
      FROM TASK
      WHERE my_NUM = '131'
      )
      "
      >

        Comment

        • Michael Hill
          #3
          Re: Page Processing Efficiency

          Jim,

          Can you elaborate with a simple example?

          Mike

          Jim Kennedy wrote:
          use bind variables. Lot of cold fusion folks don't and it hurts their
          scalability. Cold Fusion does support it.
          Jim
          "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
          news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...
          If I had a page that was being generated using coldfusion from queries
          to an oracle table would it be better response time:
          A) pulling the all the data using 1 query and iterating over the same
          result table multiple time producing desired arrays, or
          B) pulling specific data using specific queries with less production of
          arrays and proccessing after te data was returned?

          In the example I had 2 date fields called start and comp in a table with
          other like data and wanted to know what the min value was from both
          fields.

          So I have to "select * from the table where mynum='131'" and then
          process the result set

          or

          "select * from the table where mynum='131'" as well as 2 more queries:

          "SELECT min(min_start) as min_start
          FROM
          (
          SELECT min(BASE_START) as min_start
          FROM TASK
          WHERE my_NUM = '131'
          UNION
          SELECT min(ACT_START) as min_start
          FROM TASK
          WHERE my_NUM = '131'
          )"

          and

          " SELECT max(max_comp) as max_comp
          FROM
          (
          SELECT max(BASE_COMP) as max_comp
          FROM TASK
          WHERE my_NUM = '131'
          UNION
          SELECT max(ACT_COMP) as max_comp
          FROM TASK
          WHERE my_NUM = '131'
          )
          "

            Comment

            • Jim Kennedy
              #4
              Re: Page Processing Efficiency

              It should be on the documentation. I don't have cold fusion docs. We are
              talking about parameterized queries.
              Jim
              "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
              news:3F96964A.F 15BA10E@ram.lmt as.lmco.com...
              Jim,
              >
              Can you elaborate with a simple example?
              >
              Mike
              >
              Jim Kennedy wrote:
              >
              use bind variables. Lot of cold fusion folks don't and it hurts their
              scalability. Cold Fusion does support it.
              Jim
              "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
              news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...
              If I had a page that was being generated using coldfusion from queries
              to an oracle table would it be better response time:
              A) pulling the all the data using 1 query and iterating over the same
              result table multiple time producing desired arrays, or
              B) pulling specific data using specific queries with less production
              of
              arrays and proccessing after te data was returned?
              >
              In the example I had 2 date fields called start and comp in a table
              with
              other like data and wanted to know what the min value was from both
              fields.
              >
              So I have to "select * from the table where mynum='131'" and then
              process the result set
              >
              or
              >
              "select * from the table where mynum='131'" as well as 2 more queries:
              >
              "SELECT min(min_start) as min_start
              FROM
              (
              SELECT min(BASE_START) as min_start
              FROM TASK
              WHERE my_NUM = '131'
              UNION
              SELECT min(ACT_START) as min_start
              FROM TASK
              WHERE my_NUM = '131'
              )"
              >
              and
              >
              " SELECT max(max_comp) as max_comp
              FROM
              (
              SELECT max(BASE_COMP) as max_comp
              FROM TASK
              WHERE my_NUM = '131'
              UNION
              SELECT max(ACT_COMP) as max_comp
              FROM TASK
              WHERE my_NUM = '131'
              )
              "
              >
              >

                Comment

                • Michael Hill
                  #5
                  Re: Page Processing Efficiency

                  So after returning say 150 rows of data from a single query I can further query
                  the results of the returned rows?

                  Mike

                  Jim Kennedy wrote:
                  It should be on the documentation. I don't have cold fusion docs. We are
                  talking about parameterized queries.
                  Jim
                  "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
                  news:3F96964A.F 15BA10E@ram.lmt as.lmco.com...
                  Jim,

                  Can you elaborate with a simple example?

                  Mike

                  Jim Kennedy wrote:
                  use bind variables. Lot of cold fusion folks don't and it hurts their
                  scalability. Cold Fusion does support it.
                  Jim
                  "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
                  news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...
                  If I had a page that was being generated using coldfusion from queries
                  to an oracle table would it be better response time:
                  A) pulling the all the data using 1 query and iterating over the same
                  result table multiple time producing desired arrays, or
                  B) pulling specific data using specific queries with less production
                  of
                  arrays and proccessing after te data was returned?

                  In the example I had 2 date fields called start and comp in a table
                  with
                  other like data and wanted to know what the min value was from both
                  fields.

                  So I have to "select * from the table where mynum='131'" and then
                  process the result set

                  or

                  "select * from the table where mynum='131'" as well as 2 more queries:

                  "SELECT min(min_start) as min_start
                  FROM
                  (
                  SELECT min(BASE_START) as min_start
                  FROM TASK
                  WHERE my_NUM = '131'
                  UNION
                  SELECT min(ACT_START) as min_start
                  FROM TASK
                  WHERE my_NUM = '131'
                  )"

                  and

                  " SELECT max(max_comp) as max_comp
                  FROM
                  (
                  SELECT max(BASE_COMP) as max_comp
                  FROM TASK
                  WHERE my_NUM = '131'
                  UNION
                  SELECT max(ACT_COMP) as max_comp
                  FROM TASK
                  WHERE my_NUM = '131'
                  )
                  "

                    Comment

                    • Jim Kennedy
                      #6
                      Re: Page Processing Efficiency

                      Look up parameterized queries or host variables in your documentation. You
                      aren't using them and you should if you want to scale well. A query with
                      bind variables will avoid the reparsing that dynamic sql has and hence you
                      will get better performance and scalability. (less latching, less CPU
                      usage).
                      eg select col1, col2, col3 from mytable where col1=?
                      Jim
                      "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
                      news:3F97E761.F 20208A@ram.lmta s.lmco.com...
                      So after returning say 150 rows of data from a single query I can further
                      query
                      the results of the returned rows?
                      >
                      Mike
                      >
                      Jim Kennedy wrote:
                      >
                      It should be on the documentation. I don't have cold fusion docs. We
                      are
                      talking about parameterized queries.
                      Jim
                      "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
                      news:3F96964A.F 15BA10E@ram.lmt as.lmco.com...
                      Jim,
                      >
                      Can you elaborate with a simple example?
                      >
                      Mike
                      >
                      Jim Kennedy wrote:
                      >
                      use bind variables. Lot of cold fusion folks don't and it hurts
                      their
                      scalability. Cold Fusion does support it.
                      Jim
                      "Michael Hill" <hillmw@ram.lmt as.lmco.comwrot e in message
                      news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...
                      If I had a page that was being generated using coldfusion from
                      queries
                      to an oracle table would it be better response time:
                      A) pulling the all the data using 1 query and iterating over the
                      same
                      result table multiple time producing desired arrays, or
                      B) pulling specific data using specific queries with less
                      production
                      of
                      arrays and proccessing after te data was returned?
                      >
                      In the example I had 2 date fields called start and comp in a
                      table
                      with
                      other like data and wanted to know what the min value was from
                      both
                      fields.
                      >
                      So I have to "select * from the table where mynum='131'" and then
                      process the result set
                      >
                      or
                      >
                      "select * from the table where mynum='131'" as well as 2 more
                      queries:
                      >
                      "SELECT min(min_start) as min_start
                      FROM
                      (
                      SELECT min(BASE_START) as min_start
                      FROM TASK
                      WHERE my_NUM = '131'
                      UNION
                      SELECT min(ACT_START) as min_start
                      FROM TASK
                      WHERE my_NUM = '131'
                      )"
                      >
                      and
                      >
                      " SELECT max(max_comp) as max_comp
                      FROM
                      (
                      SELECT max(BASE_COMP) as max_comp
                      FROM TASK
                      WHERE my_NUM = '131'
                      UNION
                      SELECT max(ACT_COMP) as max_comp
                      FROM TASK
                      WHERE my_NUM = '131'
                      )
                      "
                      >
                      >
                      >

                        Comment

                        Previous template Next
                        Working...