can anyone optimize this query

[xcavin]

select * from pending where custid <> '' and custid in ( select id from customer where status=1 and priority = ( select max(priority) from customer where custid in ( select distinct custid from pending where custid <> '' ) ) ) there is no constriants on both the tables.

[RichardGrimmer]

Yup - remove Select * FROM "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox

[Michael Potter]

This looks a little cleaner. Are you sure there is both id and custid in the customer table? Something doesn't look quite right. You can also get rid of the correlated sub-query by placing it in the INNER JOIN.

SELECT 
     pending.*
FROM 
     pending
INNER JOIN 
     customer cus
     ON (pending.custid = cus.id)
WHERE 
     pending.custid <> '' AND
     cus.status = 1 AND
     cus.priority = (SELECT
                         max(priority)
                     FROM
                         customer
                     WHERE
                         custid = cus.custid)

[michanne]

I am guessing "id" is really custid since you are looking for custid in it. Are you looking for the max(priority) for EACH customer (1) or the max(priority) for all customers in pending(2)? 1. Select * From( Select custid, max(priority)as Priority From customer Where status = 1 and custid <> '' Group by id ) as subtable Join pending On subtable.custid= pending.custid 2. SELECT * FROM Pending Join Customer on pending.custid = customer.custid WHERE priority = ( SELECT max(priority) FROM customer Join pending on customer.custid = pending.custid WHERE customer.custid <> '' and status = 1 ) Where pending.custid <> '' michanne

[xcavin]

Michael Potter wrote: Are you sure there is both id and custid in the customer table? Yes you are right, There is no custid its just id in the customer table. [code] select * from pending where custid <> '' and custid in ( select id from customer where status=1 and priority = ( select max(priority) from customer where id in ( select distinct custid from pending where custid <> '' ) ) ) [/code]

[xcavin]

Dear Michael, So i modified my query like this select * from pending where custid <> '' and custid in ( select id from customer where status=1 and priority = ( select max(priority) from customer where id in ( select distinct custid from pending where custid <> '' ) ) ) and your query. SELECT pending.* FROM pending INNER JOIN customer cus ON (pending.custid = cus.id) WHERE pending.custid <> '' AND cus.status = 1 AND cus.priority = ( SELECT max(priority) FROM customer WHERE id = pending.custid ) But when in checked the execution plan my query was showing less percentage for all the counters there. Where as your query is showing more. So my doubt was, does my lazy query has any advantage or perfomance gain that i am not aware of, may be coz of not using joins. btw still wondering how you guys are writing such optimized queries, actually i can program(c++) many complex issues, but still cannot optimize the SQL queries like that, is there any tips to get started ?? thanks

[Michael Potter]

My experience is that joins usually take a shorter time then do in statements. It really depends upon the size of your datasets. There are few hard and fast rules that would work in all instances. Usually, I just follow my nose - If a query takes longer then I desire, I try a different approach or modify my indexes. For example, the following index may help my solution:

CREATE INDEX IX_CUSTOMER_ID_PRIORITY
ON customer
(
     id,
     priority
)

If the table is not large, than the index would be ignored and a simple table scan would be used. The rule about not using * when you don't need every column is a rule that works in all instances.