Hi,

Our users are facing performance problem with a BaaN session that takes forever and would hang. This session used to work fine a month ago. I anlyzed each and every query and tables. After a long research I found that there is a table tcmcs018 (only 15 records in production) which causes this performance issue. This session run with super fast speed as soon as I rebuild the index1 on this table. It works fine for a day or two and then it will fail again. I have to rebuild this index every other day (sometime 2-3 times in a day) in order to make this session work. Now, that can't be the permanent solution and I am unable to understand why such a small table would fail on index that frequently. Here is the information about our BaaN system.
Baan version: BaaN V c
Database: SQL Server 2005
Is there Guru who can help in this regard and suggest the solution?
Please help. Any help is appreciated.

Thanks.

Hi ,

Can you please post your script and table structure .

Regards,
Niraj

Hi,

Did you try yet to delete the entire table and create it again? Otherwise, I don't really have a solution for you, nor do I have an explanation, but I would advise you to ask DikkieDik from this forum (or wait patiently until he reads this thread and will answer). He is THE Guru to be asked on such issues...

Good luck,
Eli

He is THE Guru to be asked on such issues...
Thanks for the compliment Eli now I have to make sure I can solve this :rolleyes: with is not easy.

Indeed I also wonder what the query is and why it is causing your performance problem. I have a few questions:
- can you post the query (application and MSQL)
- can you post the access path as created in MSQL SQL Query Analyzer when it runs fine and when not.
- Are you running Level 1 or Level 2? I assume Level 2 else the strategy (access path) should be the same.
- I assume you recreate the queries by hand is that correct?
- In what way are you generating statistics and could that part influence the query?

I know, a lot of questions. The more answers you can post the more precise the recipe we can describe. I hope...

Kind regards,
Dick

Dick,

Thanks for your attention. Following is the query that I captured and causes performance issues in that BaaN session(Standard Receipts session whinh3520m000).
======================================
SELECT a.t_acti, a.t_ardt, a.t_astk, a.t_blck, a.t_bpid, a.t_btsp, a.t_cdck, a.t_clot, a.t_conf, a.t_cwar, a.t_fire, a.t_idat, a.t_item, a.t_itxt, a.t_loca, a.t_lsel, a.t_lsta, a.t_oorg, a.t_orno, a.t_oset, a.t_pkdf, a.t_pmsk, a.t_pono, a.t_prdt, a.t_psno, a.t_psnr, a.t_psqu, a.t_qadv, a.t_qput, a.t_qrel, a.t_qstk, a.t_qstr, a.t_rcno, a.t_rcun, a.t_revi, a.t_rstk, a.t_seqn, a.t_shid, a.t_stka, a.t_stkr, a.t_stun, a.t_txtn, c.t_item, c.t_obpr, c.t_locc, d.t_uset, d.t_kitm, d.t_itmt, d.t_cprj, d.t_kltc, d.t_eitm, d.t_citg, d.t_cuni, d.t_umer, d.t_csig, e.t_blcp, e.t_blcs, e.t_blpi, e.t_blpo, e.t_blps, e.t_blsv, e.t_csig, e.t_dsca
FROM ((((dbo.twhinh210100 AS a
LEFT JOIN
dbo.twhwmd400100 AS b
ON b.t_item = a.t_item)
LEFT JOIN
dbo.ttcibd001100 AS d
ON d.t_item = b.t_item)
LEFT JOIN
dbo.ttcmcs018100 AS e
ON e.t_csig = d.t_csig)
LEFT JOIN
dbo.twhwmd400100 AS c
ON c.t_item = a.t_item)
WHERE a.t_rcno <> @P1
AND (a.t_rcno = @P2
AND a.t_bpid = @P3
AND a.t_oorg = @P4
AND a.t_orno > @P5)
ORDER BY 33, 5, 18, 19, 20, 23, 37 OPTION (FAST 5)
==========================================

Answers to your question:
1. Query and application(given above)
2. Its all random and don't know which parameters it will fail on. Sometime it hangs when user opens up this Session and the same query appears at backend when it hangs. So it is not easy to get the execution plan.
3. Level2
4. No I don't create any query by hand, Its all standard code. I create query to rebuild the index at the backend only.
5. As I said, I use Alter Index command to rebuild the index and if need be I also run update statistics command with fullscan option. That's all I do at backend using SQL Query Analyzer.

Query shows a few less logical points.

Tot get an execution plan you put in your Query analyser the following:

declare @P1 char(9)
declare @P2 char(9)
declare @P3 char(9)
declare @P4 int
declare @P5 char(9)

SELECT a.t_acti, a.t_ardt, a.t_astk, a.t_blck, a.t_bpid, a.t_btsp, a.t_cdck, a.t_clot, a.t_conf, a.t_cwar, a.t_fire, a.t_idat, a.t_item, a.t_itxt, a.t_loca, a.t_lsel, a.t_lsta, a.t_oorg, a.t_orno, a.t_oset, a.t_pkdf, a.t_pmsk, a.t_pono, a.t_prdt, a.t_psno, a.t_psnr, a.t_psqu, a.t_qadv, a.t_qput, a.t_qrel, a.t_qstk, a.t_qstr, a.t_rcno, a.t_rcun, a.t_revi, a.t_rstk, a.t_seqn, a.t_shid, a.t_stka, a.t_stkr, a.t_stun, a.t_txtn, c.t_item, c.t_obpr, c.t_locc, d.t_uset, d.t_kitm, d.t_itmt, d.t_cprj, d.t_kltc, d.t_eitm, d.t_citg, d.t_cuni, d.t_umer, d.t_csig, e.t_blcp, e.t_blcs, e.t_blpi, e.t_blpo, e.t_blps, e.t_blsv, e.t_csig, e.t_dsca
FROM ((((dbo.twhinh210100 AS a
LEFT JOIN
dbo.twhwmd400100 AS b
ON b.t_item = a.t_item)
LEFT JOIN
dbo.ttcibd001100 AS d
ON d.t_item = b.t_item)
LEFT JOIN
dbo.ttcmcs018100 AS e
ON e.t_csig = d.t_csig)
LEFT JOIN
dbo.twhwmd400100 AS c
ON c.t_item = a.t_item)
WHERE a.t_rcno <> @P1
AND (a.t_rcno = @P2
AND a.t_bpid = @P3
AND a.t_oorg = @P4
AND a.t_orno > @P5)
ORDER BY 33, 5, 18, 19, 20, 23, 37 OPTION (FAST 5)

Then press <Ctrl><L> and you see the excecution plan.

The query above is the database query and not the application query. The application query can be found with several ways of application tracing see this link (http://www.baanboard.com/baanboard/showthread.php?t=7665) to a doc that explains a lot of features to do so.

I think the problem with this query is the

query.extend.where( "whinh210.rcno <> """" ")

If you have Baan code, you can try remove this line on a test system and see what happens. If that works we know the problem and can look for a cure.

Can you also share the db_resource and msql_driver_param file?

Kind regards,
Dick