As one who has optimized and re-written more than a few queries and stored procedures (including a fair few JIWA ones!), I can tell you if there is a datetime parameter involved (e.g. @AsAtDate) then this is almost always a good optimization target - I've had them kill more than one query.
I had this exact problem last year with a creditor balance as at query (IIRC).
The OPTIMIZE FOR UNKNOWN usually helps.
I don't know what SQL does with datetime and optimization, but it often gets it horribly wrong. IIRC, the newer DATE type doesn't have this problem.
Actually, there's a suggestion I might pop through the suggestion box - all the database fields that only need to be DATEs (which is most of them for accounting purposes), should be changed from DATETIME to DATE fields. DATE takes up less space (just 3 bytes vs 8), is MUCH more efficient (I believe it's implemented in memory as a 32 bit integer), isn't affected by time zones and daylight savings, and it makes queries simpler because you can go
- Code: Select all
DECLARE @StartDate DATE = '20200401';
DECLARE @EndDate DATE = '20200430';
...
SELECT ...
WHERE TransactionDate >= @StartDate and TransactionDate <= @EndDate
instead of
- Code: Select all
DECLARE @StartDate DATETIME = '20200401';
DECLARE @EndDate DATETIME = '20200430';
...
SELECT ...
WHERE TransactionDate >= @StartDate and TransactionDate < DATEADD(day, 1, @EndDate)
At the same vein, for all new development involving date/time values, I now use DATETIMEOFFSET, since it allows the time zone to be included, which is helpful for a) anywhere with daylight savings and b) anywhere operating across more than one time zone. It also allows you to specify how many fractional seconds you want (for me, it's almost always 0, because in accounting transactions, sub-second resolution is not usually helpful).
