Torgsoft database optimization: indexes and statistics

Torgsoft Database Optimization: Benefits and When You Need It

Database optimization by rebuilding/reorganizing indexes and updating statistics is a mandatory service procedure to restore Torgsoft performance with large data volumes or after mass changes in the database.

When Optimization Is Mandatory: Time, Size, and Change Triggers

You should perform optimization if at least one factor applies:

• the database has been actively used for more than 6–12 months,

• mass operations were performed (product imports, inventory counts, log cleanup),

• the database is approaching the SQL Server Express (10 GB) limit,

• after a Torgsoft version update, significant slowdowns appeared.

Signs the Database Needs Optimization: Slow Forms, Reports, Errors, and the 10 GB Limit

Users typically complain about:

• very long execution of certain operations (closing inventory counts, generating documents),

• forms freezing in Stock Availability, Invoice-based Sales,

• reports taking minutes to generate,

• errors such as:

• Arithmetic overflow

• SQL filegroup is full,

• the database size reaches 9–10 GB and write failures occur.

Causes of Slowdowns

1. Index fragmentation
   Due to constant inserts/deletes, SQL uses inefficient execution plans.

2. Outdated statistics
   The SQL optimizer incorrectly estimates data volumes.

3. Excess data
   Old logs, closed-period statistics.

4. SQL Server Express limitations
   A hard 10 GB limit causes errors and performance degradation.

5. Outdated SQL Server version (2005)
   Does not support modern Torgsoft queries.

How to Speed Up the Database

1. Standard Optimization (Mandatory Minimum)

Run the service procedure:

File → Rebuild and Reorganize Indexes and Update Statistics

Result:

• SQL generates new execution plans,

• operation speed increases several times.

2. «Database Optimization» Mode (version 2022.0.48+)

For complex and «heavy» databases: File → Database Optimization

What happens:

• Database Engine Tuning Advisor is used,

• real slow queries are analyzed,

• custom indexes are created specifically for your database.

Observed effect in practice:

• forms speed up by 10–15x,

• closing inventory counts: from hours to minutes.

3. If the Database Is Approaching 10 GB (SQL Express)

Mandatory actions:

1. Delete unnecessary logs:

•    User Actions Log,

•    Document Change Log (keep, for example, 3 months).

2. Delete closed-period statistics (code 006).

3. After that:

•    rebuild indexes,

•    run Shrink via SQL Server Management Studio.

Expected result:

• database size reduced by 1.5–2x,

• overflow errors disappear.

4. Check the SQL Server Version

• SQL Server 2005 is not recommended; optimization may have little effect.

• Minimum acceptable: SQL Server 2014.

Post-Optimization Check

After optimization:

• forms open within seconds,

• reports generate without delays,

• users no longer experience freezes,

• the database size stays stable and does not grow abnormally.

Future Prevention

• run optimization every 3–6 months,

• close periods regularly,

• do not keep logs for years,

• monitor database size,

• use an up-to-date SQL Server version.

Summary: Action Rule

If Torgsoft starts slowing down, the first thing to do is optimize indexes and statistics.
If the database is large or close to 10 GB, do optimization + cleanup + shrink.