Hi,

I have a BaaN session that runs slower or takes forever sometimes when I run it first time. It runs faster and takes 1/5th of the time(that it took in first time run) after the first run. I have checked everything like database statistics, rebulding of indexes on all of the related tables etc but nothing helped. It seems that it has something to do with pinning the tables/objects in the shared memory etc. I am not sure what the real issue is. Here is the infor about our BaaN system.
Baan version: BaaN V c
Database: SQL Server 2005
(We recently upgraded to SQL Server 2005. This session used to work fine in SQL Server 2000 environment)

Is there anybody who can help in this regard and exaplin this behaviour?
Please help. Any help is appreciated.

Thanks.

May be there is a problem in your program logic . Send your program source
may be we can fix somethings.

Good luck !

Normally, the reason the second time the session takes a fivth of the first time, is due to caching; this totally makes sense.

The question is why the first time takes so long (forever). Could you tell us more about the session (customized??), the logic, the data selected, and - if you own it - the source?

Regards,

Eli

Following is the query that came up as culprit in the trace file. This is the only query which is taking significant amount of time (1-3 minutes for the first record). As I told the session runs faster after the first run, so the query. The same query took 2 or less than 2 seconds in the second run. Now, if its a problem with the code or query then it should take the same amount of time all the times, no matter first run or second run. Am I right?
*************************************
select cisli205.*,
cisli200.brad:additions,
tcmcs011.dsca,
tcmcs011.perc,
tcmcs011.days,
tcmcs013.txta,
tcmcs013.dsca,
tcmcs013.disa,
tcmcs013.disb,
tcmcs013.disc,
tcmcs013.prca,
tcmcs013.prcb,
tcmcs013.prcc,
tccom000.cadr:hold.tccom000.cadr,
tccom110.cadr,
tccom130.pstc,
tccom130.fovn:tax.number,
tcmcs056.dsca:desc.repo,
tccom111.cadr
from cisli205,
cisli200,
tcmcs013,
tcmcs011,
tccom000,
tccom110,
tccom130,
tcmcs056,
tccom111
where cisli205._index1 inrange { :i.sfcp.f, :i.ityp.f,
:i.idoc.f}
and { :i.sfcp.t, :i.ityp.t,
:i.idoc.t}
and cisli205.itbp inrange :i.itbp.f and :i.itbp.t
and cisli205.scmp = :i.scmp
and cisli205.stat inrange :status.f and :status.t
and cisli205.cmbb refers to cisli200
and cisli205.ccrs refers to tcmcs011
and cisli205.cpay refers to tcmcs013
and cisli205.sfcp refers to tccom000
and cisli205.ofbp refers to tccom110
and cisli205.stbp refers to tccom111
and cisli205.cidm refers to tcmcs056
and cisli205.itoa refers to tccom130
order by cisli205.cidm, tccom130.pstc, cisli205.itoa,
cisli205.idoc with retry
------------------------------------------------
Nr Rows Fetched : 1
Fetch Time for 1st Row : 96.281 sec
Total Time : 96.281 sec
================================================
********************************

Now, if its a problem with the code or query then it should take the same amount of time all the times, no matter first run or second run. Am I right?

Like I said before, probably your first query is cached, so when executing the second time your query is read from cache, which obviously results in a huge performance difference. So that is normal.

Regarding your query: I think the cause is your order by clause. Most probably the index actually used at runtime is index3: invoice-to address, invoice. In Oracle level 1 driver this is what would happen at least...

What is the reason for your order by clause? Is it for reporting sorting? Then maybe try to order on report level (set order priority on report fields), and have "order by cisli205._index1" as your order by clause.

The reason that this query first performs terrible and than fine is probably due to the fact that the first time this query reads a lot of blocks from disk. The second time from memory. If you wait between the 2 iterations about half an hour, your cache will be filled again with other data and the 2nd run will take "forever" again.

As this query is really destroing your cache with is very bad for other users as well, I recommend to improve this query.

As there is no index on cisli205.cidm and no range on this field always a full table scan will be performed. Adding an index will not help, as the ranges can be wide open. I really think you have to reconsider what you actually want to get by this query.

Hope this helps,
Dick

You can check out this guide (http://www.baanboard.com/baanboard/showthread.php?t=1956&highlight=Optimization+Guide) for hints on how to improve queries. There is also a guide in the sticky thread in the performance and tuning forum that may also help.

Good Luck!