Skip to main content

Performance Review

This section helps you check and understand factors that can affect Nectari’s speed and overall performance. The information here is technical and best suited for those who are comfortable working with servers, databases, or network settings.

Most of the time, if you experience slow reports or delays, it’s caused by things outside of Nectari—such as your server, database, network, or data source.

Before you start using Nectari, it’s a good idea to review your system setup and make sure everything is running smoothly. The tips and examples here, focusing on SQL Server and Sage X3, helps you identify and fix the most common issues.

Data volume​

Assessing data volume in your data sources—especially the primary source—is critical for optimizing Nectari performance.

Follow these steps to assess and monitor data volume:

  1. Check overall database size.
    Review the physical database size of your main data source. In SQL Server, you can find this information on the Database Properties screen.
  2. Review sizes of key tables (row count).
    Identify critical tables or files (such as Customers, Inventory Items, Invoice Detail, Stock Journal, General Ledger Journal). Run the following SQL query for each table to see the number of records: 
    SELECT COUNT(*) FROM <YourTableName>
  3. Test server-side query performance.
    Use a tool like SQL Server Management Studio to run typical queries directly on the server. Choose queries that are representative of real reporting use and target large tables. Example queries: 
    SELECT TOP 500000 * FROM yourERP_CustomersTable
    SELECT TOP 500000 * FROM yourERP_GLJournalTable
    SELECT TOP 500000 * FROM yourERP_InvoiceTable
    Ideally, retrieving 500,000 records should take less than 20 seconds. If not, review the server’s configuration and resources.
  4. Test query response on the local network (LAN).
    Run the same queries from another computer inside the company’s LAN. If performance drops, investigate the local networking setup.
  5. Test query response over a remote (WAN) connection.
    Run the queries from a remote computer over the WAN. If response times increase significantly, check WAN infrastructure and connectivity, as these can severely impact performance.

note

Most performance issues at this stage are related to your database engine or server/network infrastructure—not the Nectari application itself.

For deeper troubleshooting, see Database Engine Performance and Hardware Performance, or consult with a technical specialist.

Performance​

This section focuses on optimizing Nectari Data Models, OLAP Cubes, and Excel Add-in usage. These optimizations help prevent performance issues within the application itself.

Make sure server and network performance are already confirmed before applying these steps.

Data model optimization​

If you experience slow performance with a Nectari view, first determine whether it’s built on a data model or a cube. If it’s based on a cube, refer to the next subsection. For data models, follow these steps:

For slow Data Models:​

  1. Locate the SQL query used by the data model in the Nectari log file. (See Nectari Logs for file locations.)
  2. Copy and run the query directly in a SQL client (such as SQL Server Management Studio) on the database server.
  3. Analyze and optimize the query as needed and consider adding indexes to improve speed.
caution

For Sage ERP on SQL Server, always use Sage's recommended tools to add indexes, or your changes may be lost during upgrades.

Excel Add-in optimization​

When you encounter performance issues with the Excel Add-in, start by identifying which data model the report uses and optimize that data model inside Nectari first. Check the Nectari log to view the SQL query behind your Excel Add-in report. (See Nectari Logs for file locations.)

After optimizing the data model within Nectari, use these additional guidelines if Excel performance is still slow:

  1. Review the number of Excel formulas in the report.
    Too many formulas (especially spread across multiple sheets) are a common reason for slow Excel performance.
    To improve efficiency, use parameters (like combo boxes) that let users filter data and reduce both the number and complexity of formulas.
  2. Check the Excel Add-in Cache Parameter.
    An incorrect cache setting is a frequent cause of performance problems with the Add-in.
    Double-check this setting, and consider additional training to ensure an appropriate cache setup for your environment.

For more details and tips on optimizing references and formulas, see Cache Optimizer.

Database engine performance​

If you have ruled out Nectari as the source of slowdowns but are still experiencing performance issues, review your database server’s resource usage and configuration. For SQL Server, two major factors are key: available memory and disk I/O.

Database Server Memory Settings​

  • Make sure the Maximum Server Memory setting in SQL Server is not configured to use the server’s entire RAM. Allowing SQL Server to use all available memory can starve other applications and services running on the server.
    Example: If the server is dedicated to SQL Server only, allocating most of the memory to SQL Server is reasonable. If the server also runs other applications or services, leave enough memory reserved for those as well.
  • As a general rule, set SQL Server to use a maximum of about 80% of total RAM (more precise tuning may be needed based on server workload).
  • If unsure, consult a technical specialist to determine the right value for your environment.

Monitor System Resources Using PerfMon​

Collect statistics for at least 15 minutes during typical workload using Windows Performance Monitor (PerfMon). Pay attention to these key counters:

  • Pages per Second (Memory Counter):
    Tracks the amount of memory the server is actively paging to/from disk.
    Consistent values above 150 suggest a memory bottleneck. High values may also be caused by other factors—consult a specialist for an in-depth analysis.
  • Page Life Expectancy (SQL Server Buffer Manager):
    Indicates how long data stays in SQL Server’s buffer pool before being evicted.
    Values above 300 seconds (5 minutes) are ideal; consistently lower numbers often mean the server needs more memory.
  • Average Disk Queue Length:
    Shows how many I/O requests are waiting to be serviced by the disk.
    Values consistently higher than 2 may suggest the disk is not handling load efficiently. Consider upgrading disk speed or reviewing I/O patterns.
  • % Processor Time:
    Measures the proportion of time the processor is busy.
    Sustained values above 70% indicate CPU stress and may signal a need for more processing power.

These metrics should be interpreted in the context of your server workload and environment. Always consult an experienced technical specialist for a comprehensive analysis and before making major configuration changes.

Hardware performance​

Performance problems can result from your server’s hardware, either before or after installing Nectari.

  • If issues occur before Nectari is installed, consult a technical specialist to identify the cause. Common sources include:
    • Network setup or infrastructure, such as VPNs or LAN configuration. Test performance by running queries on each type of network connection.
    • Server overload or incorrect sizing. The server may lack adequate resources for the current workload.
  • If issues begin only after installing Nectari, review and optimize your Nectari configuration as outlined in the Performance and Database Engine Performance sections. If problems persist, assess whether the server hardware is sized adequately for simultaneous applications.