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]

use bind variables. Lot of cold fusion folks don't and it hurts their
scalability. Cold Fusion does support it.
Jim
"Michael Hill" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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'
)
"

[Michael Hill]

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" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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'
)
"

[Jim Kennedy]

It should be on the documentation. I don't have cold fusion docs. We are
talking about parameterized queries.
Jim
"Michael Hill" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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'
)
"

[Michael Hill]

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" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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'
> )
> "
>

[Jim Kennedy]

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" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F**************@ram.lmtas.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" <hi****@ram.lmtas.lmco.com> wrote in message
news:3F***************@ram.lmtas.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" <hi****@ram.lmtas.lmco.com> wrote in message
> news:3F***************@ram.lmtas.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'
> > )
> > "
> >