The database slows down after the initial import: errors in the data, Excel file, and import settings

Launching a new retail outlet or switching to a new record-keeping program is always accompanied by mass data migration. Instead of weeks of manual entry, thousands of product catalog items are uploaded in minutes through import from Excel. However, it is at this stage, even before the first sales are made, that the system may unexpectedly start slowing down, freezing, or showing unclear errors.

Let us look at the most common reasons why a database «fails» at the start and review ways to fix them.

1. Limits of free SQL Express: why the database becomes full

By default, the free version of Microsoft SQL Server Express is used to store data in the program. It is well suited for small and medium-sized businesses, but it has strict hardware limitations that users are often unaware of at the start.

• Data volume limits. The SQL Server 2008, 2012, 2014, 2016 and 2017 Express editions have a strict database size limit of 10 GB. (For the very old SQL Server 2005 version, this limit is only 4 GB).

• RAM limits: Regardless of how much RAM is installed on your server, the free SQL Express will use no more than 1.4 GB of RAM.

At the start of work with a new database, mass product import usually does not cause the SQL database to become full. By default, product photos are stored in the file system, not inside the database, so they should not rapidly increase the size of the SQL file.

If the database starts slowing down immediately after import, first of all you should check not the SQL Server Express limit, but whether the Excel file was prepared correctly, whether the import settings are correct, and whether there are possible data errors: extra empty rows, incorrect formats, duplicates, abnormal quantity or price values. These are the errors that more often affect database stability at the start.

How to solve the problem:

• Database shrinking (Shrink) does not reduce the amount of actual data; it can only free unused space in the database file. It does not fix import errors or optimize the data itself.

• Clearing logs is mostly relevant for old databases that have been in use for a long time and have accumulated many records in journals. In a new database after the initial import, logs are usually empty or almost empty.

• Moving photos out of the database at the start is also not a typical solution, because product photos are stored in the file system by default. This is the recommended storage method. Storing photos directly in the database is an outdated approach that should not be used.

Therefore, if the system slows down after the initial import, first check the Excel file, import settings, the correctness of quantities, prices, barcodes, item codes, and the absence of abnormal values in documents.

2. «Arithmetic overflow» error: the barcode trap

One of the most damaging human-factor errors during initial stock receipt or inventory is accidentally scanning a barcode into the wrong field.

  Cause. Instead of the «Barcode» field, the user places the cursor in the «Quantity» or «Price» field and scans a standard 13-digit barcode (for example, 2955650064811). As a result, the program records the product quantity as several billion units. The database is not designed to process such mathematical values in standard data types (int). This leads to a critical error: Arithmetic overflow error converting expression to data type int (or numeric).

Consequences for the database:

• Any attempt by the program to automatically recalculate cost or close a period freezes completely on this problematic product.

• A product with such an abnormal movement history (for example, billion-unit balances) makes it impossible to correctly generate turnover statements.

 Solution: you need to find the document (stock receipt invoice or inventory sheet) where the barcode was scanned instead of the quantity and edit it by entering the correct value. 

You can find such a product yourself in the «Product Movement» form. To do this, simply sort the data by quantity: items with abnormally large values will be immediately visible. After that, open the corresponding document and correct the incorrectly entered quantity or price.

3. Collation Conflict: server language standards

Sometimes, after successfully installing SQL Server and importing products, the user faces a seemingly strange problem: the products are in the database, but on the «Warehouse Status» form they are not displayed when a filter or search is applied, or the program shows an error.

 Cause. This problem is related to Collation (sorting parameters) — a setting at the SQL Server level that defines character sorting rules and language standards (for example, Ukrainian_CI_AS or Cyrillic_General_CI_AS). If the database sorting parameters do not match the program’s expectations or the server settings, a conflict occurs.

 Solution. The problem is resolved exclusively at the database administration level by changing the Collation settings for a specific SQL Server or for the database itself.

4. Optimization after mass import

The procedure of mass importing thousands of products from Excel requires the right approach; otherwise, the database may freeze during the process:

• Out of memory. If, in an Excel document, the user accidentally selects empty rows or columns all the way to the end of the sheet, the program will try to process this huge empty array, which will result in an out-of-memory error (Out of memory).

• Price recording. If the «Record purchase price changes» setting is enabled during import, the program will search for and compare previous prices for each product, which critically slows down the database loading process.

 Mandatory action after import! When you upload thousands of new products to an empty database at once, SQL Server does not yet know how to search this information quickly. Its internal «maps» (indexes) are empty or outdated. If you start selling immediately, adding a product to a receipt or opening the warehouse will be very slow.

The «Restore and reorganize indexes and update statistics» operation is usually performed automatically during the nightly automatic cost calculation, unless this mechanism has been forcibly disabled.

Therefore, after the initial import, you do not need to run this service operation manually as a mandatory step. First, check whether automatic cost calculation is enabled and whether nightly scheduled operations are being performed. This recompiles the search structure in SQL Server, and after that, working with a multi-thousand-item catalog becomes fast and stable.