Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Problem

When running a report or opening a form/window, data is slow to appear.

Solution

Following are some general suggestions for addressing general database performance tuning.

 

Warning
titleAdvance SQL Programming skills required

Advanced SQL programing and DBA skills are required to complete this article

 

Database Indexes

Databases use indexes on tables to locate data quickly.  If there are large changes in data or volumes of data over time - or if the database is moved to another SQL Server, maintenance may be required on the indexes. 

Code Block
languagesql
titleRebuild Indexes
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
 
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+ name +']' AS TableName
FROM sys.tables
ORDER BY name
 
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @TableName <> 'dbo.[SY_LicenceUsages]'
    BEGIN
        SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
        RAISERROR(@sql, 0, 1) WITH NOWAIT
        EXEC (@sql)
    END
    FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Anchor
Table_Statistics
Table_Statistics
Table Statistics

SQL server keeps statistics on the best strategy to use for queries - which indexes to use and when to use a table scan. This is constantly updated and sometimes will need to be rebuilt if the environment changes significantly or the data changes significantly in a short time frame (such as when large volumes of data are imported).

Jiwa comes with a stored procedure to rebuild statistics for all tables - run the following query to run this stored procedure. This may take some time to run. Degraded performance may be experienced in the short term after running this - but will improve quickly as the application submits queries and the SQL Server has time to build new statistics.

Code Block
languagesql
titleRebuild table statistics
update_all_stats


Content by Label
showLabelsfalse
max5
spacesJ7UG
showSpacefalse
sortmodified
reversetrue
typepage
cqllabel in ("sqlserver","performance","tuning") and type = "page" and space = "J7UG"
labelsperformance sqlserver tuning

Page properties
hiddentrue


Related issues