I just tried to replace some db.next(), db.prev()- functions I used some month ago. No, with a little more time, I tried to replace that "outdated" functions.

So I replaced them with SQL-Code as discussed here: http://www.baanboard.com/baanboard/showthread.php?t=8707&highlight=db.eq

But I found the performance of the SQLs very poor, so that I didn't change it. Does anybody have experience with the performance of db-functions compared with SQLs? Any hints to speed up the thing?

The problem which should be solved is, to analyze a big table (tdpur451). For that you have to to back and forth, from every selected record to check some things.

It all depends on the structure of you program. While you can replace the code with standard SQL, sometimes it is not effecient. It works when you can rebuild a section of code to work in a loop (i.e. within a select do).

The slow down comes on the select statement itself. It does take time to set up. Sometimes it is better to use dynamic SQL and set up the statement the long way. This way the setup is only done once.

A copy of your code or at least a representation of the flow of your code would get you more help.

Hi,

I think basicly the problem is, as you wrote, that there is a select (which takes time) with every record we find in tdpur451.

I think I shouldn't post the whole program, but the principle is:

- select tdpur451 (with some conditions)
- for every record in tdpur451 check if there is a previous one, with the same conditions.

I have a solution with db.next etc:


function domain tcbool check.if.delivered.to.much()
{
domain tcorno tmp451.orno
domain tcpono tmp451.pono
domain tcamnt tmp451.amta
domain tcbool previous.found
domain tcbool next.found

tmp451.orno = tdpur451.orno
tmp451.pono = tdpur451.pono

previous.found = false
next.found = false
ret = db.eq(ttdpur451)
|Vorgänger suchen
ret = db.prev(ttdpur451)
if ret <> 0 then
ret = db.eq(ttdpur451)
return(false)
else
if tmp451.orno = tdpur451.orno and
tmp451.pono = tdpur451.pono and
tdpur451.sqnb = 0 and
tdpur451.cotp.c <> "PR1" and
tdpur451.amta > 0 and
tdpur451.ckor <> tdsls.ckor.turnover then
db.next(ttdpur451)
return(true)
else
db.next(ttdpur451)
return(false)
endif
endif
db.next(ttdpur451)
return(false)
}


which is very fast, compared with this version:


function domain tcbool previous.pos.record.exist()
{
domain tcorno tmp451.orno
domain tcpono tmp451.pono
domain tcamnt tmp451.amta

select tmp451.orno:tmp451.orno, tmp451.pono:tmp451.pono, tmp451.amta:tmp451.amta
from tdpur451 tmp451
where tmp451._index1 < {:tdpur451.orno, :tdpur451.pono, :tdpur451.sqnb,:tdpur451.trdt,:tdpur451.ckor,:tdpur451.sern}
and tmp451.cotp.c <> "PR1"
and tmp451.ckor <> 3
and tmp451.sqnb = 0
and tmp451.ckor <> tdsls.ckor.cancellation
selectdo
if tmp451.orno = tdpur451.orno and
tmp451.pono = tdpur451.pono and
tmp451.amta > 0 then
return(true)
endif
endselect
return(false)
}


which runs endless.

Do you think dynmic sql can help something?

Dynamic SQL would help if the structure was the same as the original.

In the code you posted, the table is being read from the begining until the order is read, it i not efficient. I would add an Order by Statement with a desc (decending) search and that should put you where you need to be. Followed by a set with 1 rows, although you may want to leave this out until you are done debuggin.

Richard,

I do not agree that it is identical, sorry.

old:

ret = db.prev(ttdpur451)


new:

select ...
from tdpur451 tmp451
where tmp451._index1 < {:tdpur451.orno, :tdpur451.pono, :tdpur451.sqnb,:tdpur451.trdt,:tdpur451.ckor,:tdpur451.sern}
|NOT HERE: and tmp451.cotp.c <> "PR1"
|NOT HERE: and tmp451.ckor <> 3
|NOT HERE: and tmp451.sqnb = 0
|NOT HERE: and tmp451.ckor <> tdsls.ckor.cancellation
AS SET WITH 1 ROWS
selectdo
|YOUR CHECKS HERE
endselect


The parts that I marked "NOT HERE" mean that here the database could get a different sql code that on a simple db.prev().

The part "AS SET WITH 1 ROWS" is missing, so maybe your code loops over more than 1 record.

I'm not sure if that makes your code faster, but I just wanted to show that changing from db.prev() / db.next() to sql is a sometimes a bit more complex.

Günther

Toolswizard:
thanks I think I will copy the session and play a little around with that. I am curious what I can gain!

Günther:
What you marked with: "NOT HERE" should be part of the selection. I did the same (hopefully) in the db.prev/db.next-function. These conditions are also a reason 'cause I would like to get rid of db.prev/db.next.
And yes, I know it is not easy to replace one off these things with the other. But I found the use of db.prev/db.next a little dangerous. Even so it is outdated. On the other way it "can be" very easy, quick and simple to swith back and forth on record.

Btw: I just see that there is still two times the same "ckor"-condition in the code. Thougth I had removed it already ;-)

I will make assumptions about the function:
- that it returns true if "too much is delivered" and false otherwise.
- The condition for "too much delivered" is that a record with specified _index1 (and the other where clause conditions) is found.

What you need to do is possibly as follows:


select tdpur451.*
from tdpur451
where tdpur451._index1 = {:tdpur451.orno,
:tdpur451.pono,
:tdpur451.sqnb,
:tdpur451.trdt,
:tdpur451.ckor,
:tdpur451.sern}
and tdpur451.cotp.c <> "PR1"
and tdpur451.sqnb = 0
and tdpur451.ckor <> tdsls.ckor.cancellation
order by tdpur451._index1
as set with 1 rows
selectdo
|* Assign the tmp field values here if necessary ...
return(true)
endselect
return(false)


This should yield equivalent performance - if not, you need have the DBA 'tweak' the 'statistics' or whatever magic they do.

Note the "=" in the select instead of the "<" - that is what will make the most difference - inequality functions seem to kill the select, most of the times. Also, the 'order by' will force the index to be used.

Hi shah_bs,

interesting hints, thank you.
But one thing should be mentioned: I need the "<" because I have to know, if there is any "further" set with the same conditions, what means in relation with the flow of the rest of the program, that there is delivered to much.

If you have the proper constraints that applies, you will get better performance if you use 'inrange' instead of "<", and break out when your condition is satisfied - for example, you definitely know that the search is for a given order/position/sqnb - that leaves the date and the sern (since cotp.c and ckor are are 'hard constrained', so, if you have the from and to values of these based on earlier logic, you can try:

select tdpur451.*
from tdpur451
where tdpur451._index1 = {:tdpur451.orno,
:tdpur451.pono,
:tdpur451.sqnb}
and tdpur451.trdt inrange :trdt.f and :trdt.t
and tdpur451.sern inrange :sern.f and :sern.t
and tdpur451.cotp.c <> "PR1"
and tdpur451.sqnb = 0
and tdpur451.ckor <> tdsls.ckor.cancellation
order by tdpur451._index1
selectdo
if <<Condition is met>>
then
|* Assign the tmp field values here if necessary ...
return(true)
endif
endselect
return(false)


Depending on need, change the "order by" to "order by desc"

Actually, after I read your earlier post again, you can even do as follows:


select tdpur451.*
from tdpur451
where tdpur451._index1 = {:tdpur451.orno,
:tdpur451.pono,
:tdpur451.sqnb}
and tdpur451.cotp.c <> "PR1"
and tdpur451.sqnb = 0
and tdpur451.ckor <> tdsls.ckor.cancellation
order by tdpur451._index1 desc
selectdo
if <<Condition is met>>
then
|* Assign the tmp field values here if necessary ...
return(true)
endif
endselect
return(false)

Hi Shah,

if I understand u right, it is faster to put conditions "in" the selectdo-loop" than to have them in the select itself?

Btw.: Last week I made already some performance tests. But I found it a little strange, that the measured times for the same program with the same selects differ in a wide range. I think it must have something to do with caching. I have the feeling, that in most cases the first run of a program takes much longer than the next ones. But I'm not sure about that.

I did not mean it like that.

Based on the description of your requirement, what I understood is that you want to keep looking for more records until the condition of your decision criteria is met (meaning 'too much delivered' is satisfied or not). That is why the "if <<Condition is met>>" is inside the selectdo loop. It also allows to do away with the "<" condition.

In general, however, if you get the resultset that you require by placing the entire selection criteria in the 'where clause', that will almost always be faster. In your special case, it seems you have no choice (as far as I understand the requirement) but to put the check for condition inside of the selectdo loop.