Re: SQL performance question

Ok, Got it to show me something. The version with the Row number comes back
almost immediately with a very complex diagram. I'm not sure what I am
looking at or what it means. The version with out the ROW_NUMBER takes a
long time to come back, and shows me a much simpler diagram, but I still
don't know what it means, other than it takes a long time to run. What am I
looking for?

Jim

On 8/2/07, MKirkpatrick@xxxxxxxxxxxxxxxxx <MKirkpatrick@xxxxxxxxxxxxxxxxx>
wrote:

Depending on your naming, qualify it: LIBNAME.idate(coupdt,'*MDY')


midrange-l-bounces@xxxxxxxxxxxx wrote on 08/02/2007 12:07:44 PM:

Good suggestion Brian, but iNav SQL Scripts does not find the iDate

function

that is created on my system. How do I get it to see the function that

I

get in my green screen has no problem with? I am an iNav SQL nube.

Thanks!

Jim

On 8/2/07, Brian Johnson <brian.johnson.mn@xxxxxxxxx> wrote:

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

--
Brian Johnson
brian.johnson.mn@xxxxxxxxx