Certain operations taking too long? Try re-indexing all the tables in the database
For SQL Server::
USE ccadb
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
For Oracle:
select 'ALTER INDEX '||INDEX_NAME||' REBUILD'
from cca_indexes
where owner=upper('cca');
How long will this take?
For SQL Server, a 100gig CCA database file took 3 minutes, but a 500gig took 8 hours; it does not go based on the size of the database, but more on the amount of rows and number of foreign keys
For Oracle, no time data has been captured
Results:
A Report that took 3 hours to run now runs in 2 minutes
Random jobs from 1 hour to 30seconds
## Backup Database performing this operation ##