In my organisation Ricoh is the supplier of printers and copiers. Papercut is used for registering which copies are made and for which account. Another application working with Ricoh is registering copies in a different database, but both are somewhat linked.
My organisation wanted a central place to retrieve the total cost so I wrote some queries. Most work fine, but something weird is going on with the one below and I'm unable to figure out why this is failing. What could be the reason of the following issue.
It's hard to make a db<>fiddle as the databases are quite complicated. I'll try to explain the issue here.
In essence there are two main tables. A table maintained by Ricoh containing client identifiers
price (cost of the copywork), print timestamps and a job identifier. The table maintained by Papercut also has a timestamp, a
printed flag and a reference to the corresponding JobId in its
Now consider the following query which gives the total printcost when having printed using the seperate Ricoh application.
SELECT CliendId, SUM(ricoh.price) AS cost FROM (SELECT a.SystemId as ClientId, a.ProcessInternalUid as JobId, a.price FROM ricoh.printhistory AS a WHERE a.Exportdate >= '2020-10-01' AND a.Exportdate <= '2020-12-12') AS ricoh INNER JOIN (SELECT substring(b.job_comment, 16, 36) AS JobId FROM papercut.printer_usage_log AS b WHERE b.usage_date >= '2020-10-01' AND b.usage_date <= '2020-12-12' AND b.printed = 'Y' GROUP BY substring(b.job_comment, 16, 36)) AS papercut ON (papercut.JobId = ricoh.JobId) GROUP BY ricoh.CliendId
If I run this query I have instant results, where the subqueries
papercut have 96.321 and 9.354 rows respectivly.
But if I change the lower bound of the date range to
2020-11-01 the query takes 49 minutes (!). While the subqueries have 46.223 and 4.547 records respectivly.
How can this be? How can a join with larger subqueries result in much faster results? I'm really puzzled by this. What could be a reason for this increase in time? (Then I can try to debug further)
I've done some (initial) debugging and noticed the following:
- There is a limit date. If I set the lower bound to anything higher then
2020-10-08, the execution time gets large. I went looking for some troublesome records with that timestamp but found none. Also, choosing any date higher, like even
2020-12-01results in high execution times.
- If I remove the
GROUP BYaggregation (and the sum) execution is instantaneous. But if I add an
ORDER BY ricoh.pricethen execution time rises again. Could something with the price be an issue? It's a floating point number. I tried converting it to an integer but the issues remains.
Added the execution plans as requested by user J.D. I'm not really that familiar with these plans. I would guess the main difference is that
Also, this query is running on SQL server 2012. And since the database is managed by external firms I cannot add indexes. Is there a way to rewrite the query to force the 'fast' execution plan?