Finding mismatched debit and credit columns in a large transaction

30 Jun 2005 2:37 PM

Cynthia

Hi,

There is a requirement to find mismatched transactions for credit and debit
columns. The table is 29 Million rows. The case statement takes the current
amount in the credit column and finds the value in the debit column,
multiplies it by -1 and hopefully the sum of credit and debit together equals
zero. If they do not equal zero, the mismatched rows are identied that have
wrong credit and debit values.

Here is the select statement prepared by a vendor:

Select matchgroupid, matchdate, transamount, debitorcredit, bankbookflag,
userref1, effectivedate, postdate, matchedflag, taccountid, impaccountid,
TransactionType from transactions where matchgroupid in (select matchgroupid
from transactions where MatchedFlag = 'M' group by matchgroupid having
sum(case debitorcredit when 'C' then transamount else -1 * transamount end)
<> 0.00)order by matchgroupid;

This select is running for over 1 hour! IS there a more effiecient way to
determine mismatched credits and debits in a transaction table? The columns
in the table are in the select statement. How would you make this more
efficient?

Thanks for any feedback.

30 Jun 2005 2:45 PM

Arjen

Perhaps it is easier if you publish some more SQL statement.
- The CREATE table statement (transactions)
- Some INSERT statements (transactions)

This makes it easier to test.

Thanks,
Arjen

Show quote

"Cynthia" <Cynt***@discussions.microsoft.com> schreef in bericht
news:AE3F4060-92BF-488F-84FD-BF72D985E3E6@microsoft.com...
> Hi,
>
> There is a requirement to find mismatched transactions for credit and
> debit
> columns. The table is 29 Million rows. The case statement takes the
> current
> amount in the credit column and finds the value in the debit column,
> multiplies it by -1 and hopefully the sum of credit and debit together
> equals
> zero. If they do not equal zero, the mismatched rows are identied that
> have
> wrong credit and debit values.
>
> Here is the select statement prepared by a vendor:
>
> Select matchgroupid, matchdate, transamount, debitorcredit, bankbookflag,
> userref1, effectivedate, postdate, matchedflag, taccountid, impaccountid,
> TransactionType from transactions where matchgroupid in (select
> matchgroupid
> from transactions where MatchedFlag = 'M' group by matchgroupid having
> sum(case debitorcredit when 'C' then transamount else -1 * transamount
> end)
> <> 0.00)order by matchgroupid;
>
> This select is running for over 1 hour! IS there a more effiecient way to
> determine mismatched credits and debits in a transaction table? The
> columns
> in the table are in the select statement. How would you make this more
> efficient?
>
> Thanks for any feedback.

30 Jun 2005 3:31 PM

Ray

Try breaking this up into chunks. A smaller unit of measuer.
Try executing the query month
Then Open up 12 query analyzer connections and execute the query for each
month of the year.
Your basically doing a table scan of 24 million rows.
You will always be done faster if you open up a hundred connections to the
same table than if you do one connection to the same table.

Show quote

"Cynthia" wrote:

> Hi,
>
> There is a requirement to find mismatched transactions for credit and debit
> columns. The table is 29 Million rows. The case statement takes the current
> amount in the credit column and finds the value in the debit column,
> multiplies it by -1 and hopefully the sum of credit and debit together equals
> zero. If they do not equal zero, the mismatched rows are identied that have
> wrong credit and debit values.
>
> Here is the select statement prepared by a vendor:
>
> Select matchgroupid, matchdate, transamount, debitorcredit, bankbookflag,
> userref1, effectivedate, postdate, matchedflag, taccountid, impaccountid,
> TransactionType from transactions where matchgroupid in (select matchgroupid
> from transactions where MatchedFlag = 'M' group by matchgroupid having
> sum(case debitorcredit when 'C' then transamount else -1 * transamount end)
> <> 0.00)order by matchgroupid;
>
> This select is running for over 1 hour! IS there a more effiecient way to
> determine mismatched credits and debits in a transaction table? The columns
> in the table are in the select statement. How would you make this more
> efficient?
>
> Thanks for any feedback.

30 Jun 2005 3:47 PM

Alejandro Mesa

Try:

select
    t1.matchgroupid,
    matchdate, transamount, debitorcredit, bankbookflag,
    userref1, effectivedate, postdate, matchedflag, taccountid,
    impaccountid, transactiontype
from
    transactions as t1
    inner join
    (
    select
        matchgroupid
    from
        transactions
    where
        matchedflag = 'm'
    group by
        matchgroupid
    having
        sum(case debitorcredit when 'c' then transamount else -1 * transamount
end) <> 0.00
    ) as t2
    on t1.matchgroupid = t2.matchgroupid
-- order by
--     matchgroupid;

Avoid the "order by" clause if this resultset will be processed in a client
application.

AMB

Show quote

"Cynthia" wrote:

> Hi,
>
> There is a requirement to find mismatched transactions for credit and debit
> columns. The table is 29 Million rows. The case statement takes the current
> amount in the credit column and finds the value in the debit column,
> multiplies it by -1 and hopefully the sum of credit and debit together equals
> zero. If they do not equal zero, the mismatched rows are identied that have
> wrong credit and debit values.
>
> Here is the select statement prepared by a vendor:
>
> Select matchgroupid, matchdate, transamount, debitorcredit, bankbookflag,
> userref1, effectivedate, postdate, matchedflag, taccountid, impaccountid,
> TransactionType from transactions where matchgroupid in (select matchgroupid
> from transactions where MatchedFlag = 'M' group by matchgroupid having
> sum(case debitorcredit when 'C' then transamount else -1 * transamount end)
> <> 0.00)order by matchgroupid;
>
> This select is running for over 1 hour! IS there a more effiecient way to
> determine mismatched credits and debits in a transaction table? The columns
> in the table are in the select statement. How would you make this more
> efficient?
>
> Thanks for any feedback.

30 Jun 2005 5:56 PM

Cynthia

Thank you Alejandro for the suggestion. I did execute this select statement
and it took 54 minutes compared to the first try, which took 59 minutes. Is
there another idea of how to speed it up possibly?

Many thanks

Show quote

"Alejandro Mesa" wrote:

> Try:
>
> select
>     t1.matchgroupid,
>     matchdate, transamount, debitorcredit, bankbookflag,
>     userref1, effectivedate, postdate, matchedflag, taccountid,
>     impaccountid, transactiontype
> from
>     transactions as t1
>     inner join
>     (
>     select
>         matchgroupid
>     from
>         transactions
>     where
>         matchedflag = 'm'
>     group by
>         matchgroupid
>     having
>         sum(case debitorcredit when 'c' then transamount else -1 * transamount
> end) <> 0.00
>     ) as t2
>     on t1.matchgroupid = t2.matchgroupid
> -- order by
> --     matchgroupid;
>
> Avoid the "order by" clause if this resultset will be processed in a client
> application.
>
>
> AMB
>
> "Cynthia" wrote:
>
> > Hi,
> >
> > There is a requirement to find mismatched transactions for credit and debit
> > columns. The table is 29 Million rows. The case statement takes the current
> > amount in the credit column and finds the value in the debit column,
> > multiplies it by -1 and hopefully the sum of credit and debit together equals
> > zero. If they do not equal zero, the mismatched rows are identied that have
> > wrong credit and debit values.
> >
> > Here is the select statement prepared by a vendor:
> >
> > Select matchgroupid, matchdate, transamount, debitorcredit, bankbookflag,
> > userref1, effectivedate, postdate, matchedflag, taccountid, impaccountid,
> > TransactionType from transactions where matchgroupid in (select matchgroupid
> > from transactions where MatchedFlag = 'M' group by matchgroupid having
> > sum(case debitorcredit when 'C' then transamount else -1 * transamount end)
> > <> 0.00)order by matchgroupid;
> >
> > This select is running for over 1 hour! IS there a more effiecient way to
> > determine mismatched credits and debits in a transaction table? The columns
> > in the table are in the select statement. How would you make this more
> > efficient?
> >
> > Thanks for any feedback.

30 Jun 2005 4:42 PM

JT

The greatest performance improvement is not having to run the query in the
first place. Once you have this issue patched, you may want to implement a
check constraint on the table to prevent mismatched debits and credits from
being inserted going forward.

Show quote

"Cynthia" <Cynt***@discussions.microsoft.com> wrote in message
news:AE3F4060-92BF-488F-84FD-BF72D985E3E6@microsoft.com...
> Hi,
>
> There is a requirement to find mismatched transactions for credit and
debit
> columns. The table is 29 Million rows. The case statement takes the
current
> amount in the credit column and finds the value in the debit column,
> multiplies it by -1 and hopefully the sum of credit and debit together
equals
> zero. If they do not equal zero, the mismatched rows are identied that
have
> wrong credit and debit values.
>
> Here is the select statement prepared by a vendor:
>
> Select matchgroupid, matchdate, transamount, debitorcredit, bankbookflag,
> userref1, effectivedate, postdate, matchedflag, taccountid, impaccountid,
> TransactionType from transactions where matchgroupid in (select
matchgroupid
> from transactions where MatchedFlag = 'M' group by matchgroupid having
> sum(case debitorcredit when 'C' then transamount else -1 * transamount
end)
> <> 0.00)order by matchgroupid;
>
> This select is running for over 1 hour! IS there a more effiecient way to
> determine mismatched credits and debits in a transaction table? The
columns
> in the table are in the select statement. How would you make this more
> efficient?
>
> Thanks for any feedback.