[LogEntry] table needs a new index for scaling and reporting purposes

Meta
#1

PCG is requesting a database schema change in Quantify, this request will providing scaling & improve performance of the Quantify reports. I received the following bit of code to be added to the Quantify LogEntry table from our team to pass on:

SELECT COUNT(*)
FROM ASIRentalManager…Movement M
JOIN ASIRentalManager…MovementProduct MP ON MP.MovementID = M.MovementID
JOIN ASIRentalManager…Product P ON P.ProductID = MP.BaseProductID
JOIN ASIRentalManager…LogEntry LE ON LE.ParentID = M.MovementID AND LE.ChildID = MP.BaseProductID
JOIN ASIRentalManager…AvontusUser U ON U.UserID = LE.UserID
WHERE NOT M.MovementType IN (25, 28, 29, 30, 31, 32, 33, 34, 35, 36)
AND LE.LogDate >= @LogDate
AND EOMONTH(LE.LogDate) != EOMONTH(M.CreateDate)

Produced these stats:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘LogEntry’. Scan count 1, logical reads 860, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Product’. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘MovementProduct’. Scan count 3, logical reads 22, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Movement’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

After creating test index:
–create index for testing purposes
USE [ASIRentalManager]
GO
CREATE NONCLUSTERED INDEX [tx_Parent_child_date]
ON [dbo].[LogEntry] ([ParentID],[ChildID],[LogDate])
INCLUDE ([UserID])

Results:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Workfile’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘LogEntry’. Scan count 3, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Movement’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

#2

Hi Matthew. We advise against attaching directly to the database like this as it could potentially bog down the system and cause performance issues. We recommend using the API to collect source data that may be required to be used for other reasons (such as connection to Power BI or an accounting solution). We would be happy to assist you with the API here if you’d like…

#3

The requested change was for Avontus to add a database index. The API currently limits our ability to get the required data for our reporting needs in a timely manner. In order to prevent system degradation, all our customized report queries are executed against a secondary connection to Quantify which is in a replicated database, thus, to prevent system latency on the primary database.

#5

Hi Matthew. You can modify the schema of your secondary database no problem. Just make sure those changes don’t get propagated back to the original Quantify database.

#6

The modification of the secondary database is not a valid solution, as that is a licensed database. If the database was unlicensed, it would greatly affect the structure and efficiency of our reporting. This would also invalidate our contract with Avontus. Based on the initial and subsequent responses, we have revised our approach to the issue and are currently working on a different solution.