Re: SQL performance question

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
--
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.

--
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.

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Unless you are the addressee (or authorized to receive for the addressee), you may not use, copy or disclose to anyone this email or any information contained in this email. If you have received this email in error, please advise the sender by replying to this email, and delete this email immediately. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Western Dental Services, Inc. Finally, the recipient should check this email and any attachments for the presence of viruses. Western Dental Services, Inc. accepts no liability for any damage caused by any virus transmitted by this email.