Page Processing Efficiency

[Michael Hill]

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'
)
"

[Jim Kennedy]

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.com> wrote in message
news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...[color=blue]
> 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'
> )
> "
>[/color]

[Michael Hill]

Re: Page Processing Efficiency

Jim,

Can you elaborate with a simple example?

Mike

Jim Kennedy wrote:
[color=blue]
> 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.com> wrote in message
> news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...[color=green]
> > 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'
> > )
> > "
> >[/color][/color]

[Jim Kennedy]

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.com> wrote in message
news:3F96964A.F 15BA10E@ram.lmt as.lmco.com...[color=blue]
> Jim,
>
> Can you elaborate with a simple example?
>
> Mike
>
> Jim Kennedy wrote:
>[color=green]
> > 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.com> wrote in message
> > news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...[color=darkred]
> > > 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[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> > > arrays and proccessing after te data was returned?
> > >
> > > In the example I had 2 date fields called start and comp in a table[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> > > 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'
> > > )
> > > "
> > >[/color][/color]
>[/color]

[Michael Hill]

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:
[color=blue]
> 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.com> wrote in message
> news:3F96964A.F 15BA10E@ram.lmt as.lmco.com...[color=green]
> > Jim,
> >
> > Can you elaborate with a simple example?
> >
> > Mike
> >
> > Jim Kennedy wrote:
> >[color=darkred]
> > > 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.com> wrote 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[/color][/color]
> of[color=green][color=darkred]
> > > > arrays and proccessing after te data was returned?
> > > >
> > > > In the example I had 2 date fields called start and comp in a table[/color][/color]
> with[color=green][color=darkred]
> > > > 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'
> > > > )
> > > > "
> > > >[/color]
> >[/color][/color]

[Jim Kennedy]

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.com> wrote in message
news:3F97E761.F 20208A@ram.lmta s.lmco.com...[color=blue]
> So after returning say 150 rows of data from a single query I can further[/color]
query[color=blue]
> the results of the returned rows?
>
> Mike
>
> Jim Kennedy wrote:
>[color=green]
> > It should be on the documentation. I don't have cold fusion docs. We[/color][/color]
are[color=blue][color=green]
> > talking about parameterized queries.
> > Jim
> > "Michael Hill" <hillmw@ram.lmt as.lmco.com> wrote in message
> > news:3F96964A.F 15BA10E@ram.lmt as.lmco.com...[color=darkred]
> > > 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[/color][/color][/color]
their[color=blue][color=green][color=darkred]
> > > > scalability. Cold Fusion does support it.
> > > > Jim
> > > > "Michael Hill" <hillmw@ram.lmt as.lmco.com> wrote in message
> > > > news:3F95C80B.3 0A66D28@ram.lmt as.lmco.com...
> > > > > If I had a page that was being generated using coldfusion from[/color][/color][/color]
queries[color=blue][color=green][color=darkred]
> > > > > to an oracle table would it be better response time:
> > > > > A) pulling the all the data using 1 query and iterating over the[/color][/color][/color]
same[color=blue][color=green][color=darkred]
> > > > > result table multiple time producing desired arrays, or
> > > > > B) pulling specific data using specific queries with less[/color][/color][/color]
production[color=blue][color=green]
> > of[color=darkred]
> > > > > arrays and proccessing after te data was returned?
> > > > >
> > > > > In the example I had 2 date fields called start and comp in a[/color][/color][/color]
table[color=blue][color=green]
> > with[color=darkred]
> > > > > other like data and wanted to know what the min value was from[/color][/color][/color]
both[color=blue][color=green][color=darkred]
> > > > > 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[/color][/color][/color]
queries:[color=blue][color=green][color=darkred]
> > > > >
> > > > > "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'
> > > > > )
> > > > > "
> > > > >
> > >[/color][/color]
>[/color]