Need help optimizing query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help optimizing query

Author

Topic

btriz
Starting Member

1 Post

Posted - 2005-11-03 : 00:01:47

Hello,

I have a "customer" table, an "invoice" table, and a "payment" table. My customer table has a primary key called "customer_id". Both invoice and payment tables have a "customer_id" foreign key.

Now, I also have a table called "service_request". This table contains 2 fields, a foreign key "customer_id", and a varchar field called "purpose". The "purpose" field can contain one of 2 values, either "Disconnect" or "Reconnect".

Now, heres the problem - I need to write a query which displays all customers who need to be reconnected. A customer needs to be reconneced if 2 conditions are met

1) This customer is fully paid (sum(amount) from payments > sum(amount) from invoice)

2) This customer has is disconnected (sum("Disconnect") > sum("Reconnect")

Here is the query I wrote... which works... but is VERY slow! Does anyone have any idea on how I can optimize this to work in real time? It will be greatly appreciated!

select *
from customer
where customer_id in
(
select customer.customer_id from (customer left join invoice on customer.customer_id = invoice.customer_id)
left join payment on invoice.customer_id = payment.customer_id
GROUP BY customer.customer_id
having(coalesce(sum(tm_total_invoice_amount), 0) - coalesce(sum(amount_paid), 0) <= 0)
)
and customer_id in
(
select customer.customer_id
FROM customer
WHERE
(SELECT COUNT(*) FROM service_request_info WHERE service_request_info.customer_id = customer.customer_id AND purpose='Disconnect for Non-Pay')
>
(SELECT COUNT(*) FROM service_request_info WHERE service_request_info.customer_id = customer.customer_id AND purpose='Reconnect after Disconnect for Non-Pay')
)

Please note how the first part of the query returns a list of all customer_ids who are fully paid, and the second part returns a list of all customer_ids who are disconnected.

Thanks for any help!

Nedra
Starting Member

21 Posts

Posted - 2005-11-08 : 15:41:26

Here's something that I came up with....

select *
from customer as cust
inner join (select conn.customer_id
from (select customer_id
from service_request_info
where purpose in ('Disconnect for Non-Pay', 'Reconnect after Disconnect for Non-Pay')
group by customer_id
having sum(case purpose when 'Disconnect for Non-Pay' then 1 else 0 end) > sum(case purpose when 'Reconnect after Disconnect for Non-Pay' then 1 else 0 end) as conn
inner join payment as pay
on pay.customer_id = conn.customer_id
inner join invoice as inv
on inv.customer_id = conn.customer_id
group by conn.customer_id
having sum(inv.tm_total_invoice_amount)- sum(pay.amount_paid) <= 0) as invpay
on cust.customer_id = invpay.customer_id

I would also convert the purpose column to an integer value, and store the related text in a separate table like this:

Table: LookupDesc

Id Description
1 'Disconnect for Non-Pay'
2 'Reconnect after Disconnect for Non-Pay'

Make sure there are indexes on
1. customer.customer_id
2. service_request_info.customer_id and service_request_info.purpose
3. payment.customer_id and payment.amount_paid
4. invoice.customer_id and invoice.tm_total_invoice_amount

Hope that helps.
Nedra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources