August 10, 2006 at 1:42 am
SELECT A.User_Id,A.Pay_Req_Id, substring(E.Cust_ID,11,len(E.Cust_ID)) as CUST_ACID, A.BILL_AMT AS Txn_Amt, E.BRANCH_ID as UST_BANK_ID, B.Payee_Name , B.Payee_id, '1900-01-01 00:00:00.000' AS bill_paid_dt,D.AUTOPAY_AMT AS AUTOPAY_AMT, A.BILL_AMT-D.AUTOPAY_AMT as Diff_Amt FROM PAYMENT_REQUEST A, PAYEE_MASTER B,ASP_MASTER C,CUSTOMER_PAYEE D,BRANCH_CUSTOMER_MASTER E
WHERE A.Payee_id = B.Payee_id
AND A.Pmt_stat = 'A'
AND D.AUTOPAY_FLG='Y'
AND A.BILL_AMT>D.AUTOPAY_AMT
AND ltrim(rtrim(E.acc_typ))+'/'+rtrim(ltrim(E.acc_no)) = ltrim(rtrim(D.autopay_acid))AND E.status_flg = 'N'
AND A.USER_ID=D.USER_ID
AND A.PAYEE_ID=D.PAYEE_ID
AND A.PAYEE_LIST_ID=D.PAYEE_LIST_ID
AND B.PAYEE_ID=D.PAYEE_ID AND A.USER_ID
=E.USER_ID AND (A.User_Id like 'SWB/%') AND C.ASP_ID = 'SWB' AND A.bill_due_date>='Aug 12 2006 12:00AM' AND A.bill_due_date<'Aug 16 2006 12:00AM' and E.BRANCH_ID = '400088039'
August 10, 2006 at 2:07 am
I suspect that your condition
ltrim(rtrim(E.acc_typ))+'/'+rtrim(ltrim(E.acc_no)) = ltrim(rtrim(D.autopay_acid))
could be modified.
If the application that writes into autopay_acid LTRIMed and RTRIMed the data then the query may be able to use any indices on autopay_acid. As it stands the expression is not SARGABLE
August 10, 2006 at 2:24 am
Could you post the structure of the tables, the indexes and the aprox rowcount that we're dealing with here?
How long does the current query take to run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2006 at 3:18 am
This query takes 10 min to find out 1000 records.
Please help me in tuning the query.
August 10, 2006 at 3:29 am
Please post the structure of the tables, the indexes on those tables and the number of rows in the tables.
Also please post the text execution plan. you can get that by running the following
SET Showplan_all on
GO
<query here>
GO
SET SHOWPLAN_ALL OFF
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2006 at 3:51 am
It is not possible to post the structure. Please if you have any alternatives of it then please help me.
August 10, 2006 at 3:57 am
I can't help you tune the query if I don't know what the underlying structure is like. Can you at least post the text execution plan and the row counts of the tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2006 at 3:59 am
You don't have to post the structure with the actual names, or the actual data, if it's sensitive. You may change names, and/or make up some data that behaves in the same way as your real stuff.
The most important to 'keep' is the correct datatypes and the 'format' of the data inside.
We can't do much more than guess and speculate without knowing the table structures and a feel for what your data looks like. It doesn't have to be much, just a handful of rows that demonstrates your problem is probably enough.
/Kenneth
August 10, 2006 at 4:11 am
On quickly looking at this, there is no join condition between ASP_MASTER and the other tables. This will produce a cartesian product; not at good idea!
You could probably optimise this query yourself if you formatted it better and used ANSI 92 join syntax.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply