Need Optimization

Last Post 10 May 2008 10:20 PM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
egerrits
New Member
New Member

--
10 May 2008 05:16 PM
My tables:

Table tCustomr (Customer table)
long lId PK
bool bInactive


Table tCutsTr (Customer transaction)
long lId PK
long lCusId (FK: to tCustomr.lId)



Table tCusTrDt (Customer transaction detail)
long lId PK
long lCustrId (FK: to tCusTr.lId)
int nTranType
double dAmount


Each tCustomr has a tCusTr record for each invoice. Each tCusTr has up to two tCusTrDt records with nTranType=0 for each invoice, and nTranType=1 for each payment. If an invoice is paid, it will have two records in this table, one with nTranType=0 and one with nTranType=1. If it remains unpaid, it will only have an nTranType=0 record.

I need to calculate total amount owing for each active customer (ie amounts for non-paid invoices). Currently, my SQL is as follows:


SELECT [lId],(SELECT SUM(dAmount) FROM tCustrdt t1 WHERE nTranType=0

AND NOT EXISTS (SELECT lId FROM tCusTrDt t2 WHERE nTrantype=1 AND t1.lCusTrId = t2.lCusTrId)

AND lCusTrId IN (SELECT lId FROM tCusTr WHERE lCusId=tCustomr.lid)) as balance
FROM tCustomr
WHERE bInactive=False



In English, I am selecting all records in tCusTrDt that have a nTranType=0 but not a nTranType=1 (hence, an unpaid invoice), taking its lId, getting its parent tCusTr record, looking up its parent tCustomer.lid and putting that tCustomer.lid along with the Sum of all nTranType-0 records.

The problem is this: it is extremelty s l o w, taking well over two minutes to get through just 2000 customers with a total of about 11000 invoices.

Is there a faster way, or perhaps a better written SQL I can use?

Thanks,


Erwin
SQLUSA
New Member
New Member

--
10 May 2008 10:20 PM
quote:

Originally posted by: egerrits

The problem is this: it is extremelty s l o w, taking well over two minutes to get through just 2000 customers with a total of about 11000 invoices.

Erwin


If this query executed once a day, it does not strike me that 2 min is too long since it is a batch job. If executed 1000 daily, that is a different story.

Take a look at the indexes. Are they up-to-date? Missing indexes?

Since you are using the isInactive flag, that probably forces table scan on the customer table.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
egerrits
New Member
New Member

--
11 May 2008 11:56 AM
Thanks Russel and Kalman,

Yes it is a production system, not homework =) Unfortunately, I had no hand in designing the beast, I can only work with what I was given. I am porting it from an Access dbase to SQL Server (hence the funny datatypes).

Anyway, yes, payments ARE in negatives, so a SUM of all records for a given ID will, in theory, give me the balance of the account. I didn't think of that.

I'll work on this again on Monday, and we'll see how she goes.

Thanks for now,

Erwin
egerrits
New Member
New Member

--
12 May 2008 07:25 AM
Success!

Just in case you're interested, my query now reads:

SELECT tcustr.lcusid as lid,SUM(dAmount) as balance
FROM tCustrdt,tCustr
WHERE tcustr.lid=tcustrdt.lcustrid
AND (tcustrdt.nTRanType=0 OR tcustrdt.nTrantype=1)
GROUP BY tcustr.lcusid

The query is much shorter, and executes in a fraction of the time my original did with the same results...

Thanks!

Erwin


Acceptable Use Policy
---