Hi All,

I have a customised session which explore BOM from bottom to top. The session is running slow and sometime I get error "Snapshot too old". Can anybody help me. below is my code

declaration:

table ttipln032 |* Maintain Lowest Level Sequence Item
table ttibom010 |* Production BOMs
table ttiitm001 |* Items

domain tcitem sitm(999999)
domain tcitem mitm(999999)


domain tcitem mitem
long ctr
long count
extern domain tcitem my.sitm, item.f, item.t
extern domain tcyesno check.eff.date
extern domain tcdate eff.date
long counter
extern domain tcmcs.str20 mess.disp

before.program:


|***************************************forms*******************************

form.1:
init.form:
get.screen.defaults()
display.all()

|*************************************choice********************************

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:

if rprt_open() then
print.report()
rprt_close()
else
choice.again()
endif


|*********************************fields***********************************

field.item.f:
when.field.changes:
item.t = item.f

field.eff.date:
before.input:
if check.eff.date = tcyesno.no then
eff.date = 0
attr.input = false
endif


|**********************************read record******************************

functions:
function print.report()
{
ctr = 0
select tipln032.*
from tipln032
where tipln032._index1 inrange {:item.f} and {:item.t}
order by tipln032._index1
selectdo
if tipln032.litm = tcyesno.yes then
init.array()
ctr = 1
sitm(1,ctr) = tipln032.item
explore.bom()
endif
endselect
}

function explore.bom()
{

long i, j
for i = 1 to ctr
mitem = sitm(1,i)
if not skip.item() then
continue
endif

select tibom010.sitm,tibom010.mitm,tibom010.exdt,tibom010.indt,tibom010.cpha
from tibom010
where tibom010._index2 ={:mitem}
as prepared set
selectdo
if tibom010.exdt = 0 or tibom010.exdt > eff.date then
else
continue
endif
if not skip.bom.item() then
continue
else
ctr = ctr + 1
sitm(1,ctr) = tibom010.mitm
endif
endselect

endfor

count = 0
for j = 1 to ctr
my.sitm = sitm(1,j)
select tibom010.mitm
from tibom010
where tibom010._index1 ={:my.sitm}
as prepared set
selectdo
upd.tipln032()
endselect
endfor
commit.transaction()
}

function upd.tipln032()
{
long count

db.retry.point()

select tipln032.*
from tipln032 |for update
where tipln032._index1 = {:my.sitm}
order by tipln032._index1
selectdo
selectempty
tipln032.item = my.sitm
tipln032.litm = tcyesno.no
tipln032.user = logname$
tipln032.time = time.num()
tipln032.date = date.num()

db.insert(ttipln032, db.retry)

commit.transaction()
get.item.dsca()
rprt_send()
endselect

}



function init.array()
{
set.mem(sitm,"")
set.mem(mitm,"")
}

function domain tcbool skip.item()
{
select tiitm001.*
from tiitm001
where tiitm001.item=:mitem
order by tiitm001._index1
selectdo
if tiitm001.citg="ZZZZZZ" or tiitm001.csig="06W" then
return(false)
else
return(true)
endif
endselect
return(false)
}

function domain tcbool skip.bom.item()
{
select tiitm001.*
from tiitm001
where tiitm001.item=:tibom010.mitm
order by tiitm001._index1
selectdo
if tiitm001.citg="ZZZZZZ" or tiitm001.csig="06W" then
return(false)
else
return(true)
endif
endselect
return(false)
}

function get.item.dsca()
{
select tiitm001.dsca, tiitm001.item
from tiitm001
where tiitm001._index1={:tipln032.item}
selectdo
endselect
}

I understand that you update the tipln-table. Is that done often? If you have a lot of "reading" between "commit"; problems like getting slower and db-problems related to rollbacks-segments (oracle) may appear.

Not knowing the specifics on how your data is, and not having studied your whole program in detail .... consider to commit more often, also if no db update has taken place.

Thanks Pellus,

I think that the problem is in arrays that makes program slower. I also modified the script to commit after every 50 transactions but it does not make any difference.

The code is hard to read without reasonable indents, but here are my 2 cent tips ...

1. I would suggest an additional commit.transaction() at the end of your function upd.tipln032() - just if you don't insert into tipln032.

2. The possible problem might be "as prepared set". You should check if you really need that.

Günther

Some suggestions from a quick glance at the code.

1. Dont use select * where not necessary especially tiitm001
2. Use tiitm001._index1 instead of tiitm001.item .
3. Use alias for tiitm001 and join instead of separate select in a function .
4. Program does not become slow with use of array. It becomes slow with excessive repeated disk io involved in SQL parse, exec and search . This may probably be functional requirement. If through re-design of code u can reduce this , u can achieve significant performance gains .

Hi all.

Thanks for your reply. I will try and check the performance.

For my experience there is another problem in the function upd.tipln032()

You are addding new records to your maintable. This will have effect to your first select and change your recordpointer.

I would suggest the following:

function upd.tipln032()
{
long count

<save the fields of the prim. key from the table tipln032>

select tipln032.*
from tipln032 |for update
where tipln032._index1 = {:my.sitm}
order by tipln032._index1
selectdo
selectempty
db.retry.point()
tipln032.item = my.sitm
tipln032.litm = tcyesno.no
tipln032.user = logname$
tipln032.time = time.num()
tipln032.date = date.num()

db.insert(ttipln032, db.retry)

commit.transaction()
get.item.dsca()
rprt_send()
endselect

<restore the fields of the prim. key from the table tipln032>

}


This also can be the reason for the long runtime.

hth

Regards
Christof

You also should chek if rollback segments and undo_retention parameters are enough in oracle.

Regards