My first sprint for this rewrite composed of SQL stored procedure optimisations.
We currently use a Sybase database so some of the below mentioned tips is specifically aligned to the sybase database.
- If you are analyzing SQL use 'set noexec on' and if you are analysing 'set fmtonly on'.Also run 'set showplan on' to view the plan/flow of execution of your sql statment
- Replaced the UNIONs with UNION ALL - this was because the datasets that I was merge were already unique and thus the distinct funtionality available with the UNION function was not needed
- Updated necessary tables where there indexes missing - The filters in the WHERE and INNER JOIN statements were not indexed and thus full table scans were taking place
- Created functional indexes based on columns that are being filtered but are changed to a different type for filter purposes eg. datetime field called varis used as convert(var, date) in filter. So a functional index is created for convert(var, date).
Hope the above tips gets you going on your optimization process.