Database maintenance tool hangs correcting deposit slip sums

[mowgli]

I'm running into a really bothersome issue trying to run database maintenance for practices that have a lot of deposit slips. When the tool runs the query on line 556 in DatabaseMaintenance.cs, it can hang for 20 minutes or more while mysqld-nt.exe churns thru the data. This query retrieves deposit slip amounts and compares those with the sum of the claimpayments and the patient payments on each deposit slip, in order to correct those that don't match. I think the problem is that it does this for all deposit slips, making a very large join, and it's executed whenever the tool is run, causing a lengthy ordeal every time. A solution might be to somehow retain the depositnums from the preceding query to correct claimpayment sums. That way the following query would be a small join. I've tried taking apart and reworking the problem query, but it always seems to choke on trying to join all claim payments and patient payments together with deposit slip numbers, using very large amounts of memory (and resulting paging to disk). The 3 tables involved all have records in the thousands. See current query below:

SELECT DepositNum,deposit.Amount,DateDeposit, IFNULL((SELECT SUM(CheckAmt) FROM claimpayment WHERE claimpayment.DepositNum=deposit.DepositNum GROUP BY deposit.DepositNum),0)+IFNULL((SELECT SUM(PayAmt) FROM payment WHERE payment.DepositNum=deposit.DepositNum GROUP BY deposit.DepositNum),0) _sum FROM deposit HAVING ROUND(_sum,2) != ROUND(deposit.Amount,2)

[jordansparks]

I don't have a database to test this query on, so can you try this? Run the following two queries:
ALTER TABLE claimpayment ADD INDEX (DepositNum)
ALTER TABLE payment ADD INDEX (DepositNum)
Then try the query in question one more time. If the speed is significantly better, let me know, and I'll add the indexes as a bug fix with the next version.

[jordansparks]

I added those queries to the next build.

[mowgli]

Just saw your response this morning, since I posted at the end of the workday Friday, and added the indexes to a couple of problem databases. This resolved the issue completely! Thanks for addressing this so quickly.