posting is very slow...

nrapendra

hi all,

i am using sql server 2000 database for navision.
as 5 months have been passed and there is a bulk no of
records are posted.when i go to post any record which is related to
g/l entry or item ledger entry,it iakes a lot of time to post

ie. i am simply posting a expence voucher.it takes near about 10 min. to post on clients,and 5-6 min. in server.I have aptimised all tables.but there
is not much more effect of that.

currently there are 12 lacs records in glentry table.

pls suggest how we can improve the speed.

Marije_Brummel

What are the slowest transactions when you monitor with client monitor?

DenSter

nrapendra wrote:

I have aptimised all tables.but there
is not much more effect of that.

What did you do to optimize all tables?

nrapendra

it is going slow while inserting record in Item ledger entry,G/L entry tables.
I have done optimizations through File-->Database-->Information-->Tables-->Optimize.

DenSter

You'll need to have someone tune the indexes of those tables. The optimizer doesn't actually optimize anything. All it does is remove empty SIFT and I believe it also reindexes. Neither one of those activities will increase the speed of your tables much.

Marije_Brummel

Have a look at the SQL Perform webcasts on the download part of this webpage. This will give you an impression of what to do.

Generaly tables which come from before 5.0 are over indexed and over sifted for SQL server and have to be adjusted.

kriki

DenSter wrote:

You'll need to have someone tune the indexes of those tables. The optimizer doesn't actually optimize anything. All it does is remove empty SIFT and I believe it also reindexes. Neither one of those activities will increase the speed of your tables much.

It also does a rebuildindex and thus calculates also SQLstatistics for that table.


If in the beginning you didn't have the problem, the problem is probably the use of a wrong index, so it needs to read the whole table and in the beginning with few records it is not a problem, but now with a lot of them it has become a problem.

nrapendra

we have not changed any in.dexes.they are as they were provided
by Navision.

Is it the problem of navision or sql server.

what i can do in this matter

kine

nrapendra wrote:

we have not changed any in.dexes.they are as they were provided
by Navision.

You found the source of the problem... ;-)

Standard NAV is not optimized. You need to optimize it for specific needs.