Slow performance when retrieving data

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.

Advance 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.

Rebuild 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

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
print (@sql)
	EXEC (@sql)
	FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Slow performance when retrieving data

Problem

Solution

Database Indexes

Related articles