Dear All,

I have a situation to export all customer (tccom010) information from baan 4 using exchange schema along with last doument date, amount. these fields are from tfacr200.

I created condition for document date and amount.

but the problem is total number of customer are around 16000 and number of records in tfacr200 are more than 20 lakhs. it is running more than 10 hrs for exporting data and client is not accepting customization session for exporting.

Condition maintained for Last document date:-

select tfacr200.docd
from tfacr200
where tfacr200._index2={:tccom010.cuno}
and tfacr200.trec =tfacr.trec.normal
or tfacr200.trec=tfacr.trec.invoice
or tfacr200.trec=tfacr.trec.anticipated
order by tfacr200.docd desc
as set with 1 rows
selectdo
return(tfacr200.docd)
endselect.


Could you please suggest me how to reduce the time.

advance thanks for your help.

Thanks,
sathish

Any other fields you might have? Are you doing this for each of the fields in tfacp200 that you have to return? Is it possible(been a while since I messed with exchange schemes) to do one query and return all the fields needed. My first thought was to build a new table with just the tfacp200 components in it that you needed - something that could be run in a batch job and build before the export. Not sure the customer would let you do it. There are other indexes, but it would get back to the same issue - sorting them descending each time. We have an index 5 which starts with supplier, document date. I am not sure an order by on that index descending would be any better.

Sathish,

Did you run all your SQL's in profilers to see they are performing well?

Checking your SQL code -


select tfacr200.docd
from tfacr200
where tfacr200._index2={:tccom010.cuno}
and tfacr200.trec =tfacr.trec.normal
or tfacr200.trec=tfacr.trec.invoice
or tfacr200.trec=tfacr.trec.anticipated
order by tfacr200.docd desc
as set with 1 rows
selectdo
return(tfacr200.docd)
endselect.

order by tfacr200.docd desc
as set with 1 rows
Why do you need to order by descending date when you are expecting only 1 record?
You can always use the max(tfacr200.docd)

The problem is the order by clause itself. However, it is required because you need the last date of document for the customer.

Try as follows:

order by tfacr200._index2, tfacr200.docd desc


This will force the use of the index which happens to be your primary constraint.

If that does not improve the performance, then try using max(tfacr200.docd). Then you will not need the order by clause - still recommend you use the same order by clause a above. You will of course need the group by. I am not familiar with Exchange, so not sure if max() is allowed. Also you will then have to assign the max value to a program variable.