Re: SQL performance question

What does the Explain function in iNav run-sql-statements have to say
about it?

On 8/1/07, Jim Essinger <dilbernator@xxxxxxxxx> wrote:

Listers,

I have created a query that uses the CTE functions something like;

With t1 as (
select distinct a.KeyFld, a.Stsfld, b.StartDate, b.EndDate
from FileA a join FileB b on a.KeyFld = b.KeyFld
where a.StsFld like 'F%'
and Date2 > CURDATE()
order by a.KeyFld),

t2 as (
select distinct c.KeyFld
from FileC c join T1 on c.KeyFld = a.KeyFld
where C.Code in ('L10', 'L14', 'L30', 'L31', 'L16',
'L28', 'L18', 'L26', 'L22', 'L24',
'L38', 'L11', 'L55', 'L34')
and c.TranDate between b.StartDate and b.EndDate
order by c.KeyFld)

select decimal(Row_Number() over (order by d.KeyFld),5,0) as Nbr,
d.KeyFld, d.Name
from FileD d join T1 on d.KeyFld = a.KeyFld
where not d.KeyFld in (select C.KeyFld from t2)
order by d.KeyFld

The question is on the "decimal(Row_Number() over (order by d.KeyFld

),5,0)

as Nbr" entry. When that is part of the select, the query performs

great,

and gives me a list in a flash - less than one second. When I remove

that

from the select, it takes over 10 minutes - then I kill the query

because it

is taking 75% of the CPU. Any thought why having the row_number

function

over the Key field would speed put the query to such an extent?

Jim
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
Brian Johnson
brian.johnson.mn@xxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.