Optimizing Database

Databases
1

I need some advice.
In SQLite I optimize the database with

DB.SQLExecute("VACUUM;")  'Speicher freigeben und Datenbank defragmentieren / rebuild database file, repacking it into a minimal amount of disk space
DB.SQLExecute("ANALYZE;") 'Zugriffspfade optimieren / gather statistics for optimization of queries

To do the same in MS SQL Server, I try to optimize the database like this:

// Speicher freigeben und Datenbank defragmentieren  / rebuild database file, repacking it into a minimal amount of disk space
DB.ExecuteSQL("DBCC SHRINKDATABASE('"+DB_Name+"') TRUNCATEONLY;")

// Zugriffspfade optimieren / gather statistics for optimization of queries
DB.ExecuteSQL("ANALYZE TABLES COMPUTE STATISTICS;")

Is this correct and/or am I missing something?

At the moment I can’t test my MS SQL Server DB because I’m still waiting for the ODBCDatabase to be corrected (Xojo 2024r1 with #75322)!

2

This is not something I would expect an application to do to an MS SQL Server. MS SQL Server and SQLite are very different systems. SQLite is single user and exists as a file on disk. MS SQL Server is client server architecture and could have many clients attached at one time. Use of such commands could result in other users being denied access at important times.

I would leave such things to the database administrator to do on their own schedule.

3

I hadn’t thought of that!
Many thanks for the information. I’ll remove the optimization from my app again.

1 Like