August 10, 2006 at 1:40 am
Please help in optimizing the following query. Please also guide me how to optimize the queries.
SELECT A.User_Id,A.Pay_Req_Id, A.Cust_Acid, A.Txn_Amt + C.TRANS_AMT AS Txn_Amt, A.Cust_Bank_Id , B.Payee_Name , B.Payee_id, a.bill_paid_dt FROM PAYMENTS A, PAYEE_MASTER B,ASP_MASTER C
WHERE A.Payee_id = B.Payee_id
AND A.Pmt_stat = 'P'
AND (A.User_Id like 'SWB/%' OR A.user_id='pay/saraswatbank')
AND C.ASP_ID = 'SWB' AND A.Cust_Bank_Id = '400088039'
August 10, 2006 at 5:14 am
Are you sure this produces the results you require? You don't have a join predicate to ASP_MASTER, so for every row in the result of the first join, you will get the number of rows in ASP_MASTER where ASP_ID = 'SWB'. I would recommend that you research the ANSI join syntax, and use it instead of the old-style join syntax.
Anyway, to answer your question: it depends. If this is a query that you run often then make sure that there is an index on every column in the WHERE clause, and every column in the select clause. Read about covering indexes - one of those may help you out here. Don't forget that every index you add will cause overheads when you add a row to the table, so if yours is an OLTP database, you have to strike a balance between read and write performance. You will almost certainly benefit from a clustered index on each of the tables if you don't already have them. There are loads of tips on indexing at http://www.sql-server-performance.com.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply