SQl Query get data very slow from different tables

Question

I am writing a sql query to get data from different tables but it is getting data from different tables very slowly. Approximately above 2 minutes to complete. What i am doing is here : 1. I am getting data differences and on behalf of date difference i am getting account numbers 2. I am comparing tables to get exact data i need.

here is my query

select T.accountno,
       MAX(T.datetxn) as MxDt,
       datediff(MM,MAX(T.datetxn), '2011-6-30') as Diffs, 
       max(P.Name) as POName 
from   Account_skd A,
       AccountTxn_skd T,
       POName P 
where  A.AccountNo = T.AccountNo and 
       GPOCode = A.OfficeCode and 
       Code = A.POCode and 
       A.servicecode = T.ServiceCode
group by T.AccountNo 
order by len(T.AccountNo) DESC

please help that how i can use joins or any other way to get data within very less time say 5-10 seconds.

Please post the schema and query plan. http://stackoverflow.com/questions/4872891/what-is-the-equivalent-of-explain-form-sqlite-in-sql-server — Neville Kuyt, Dec 04 '13 at 12:41

score 0 · Answer 1 · answered Dec 04 '13 at 12:42

Since it appears you are getting EVERY ACCOUNT, and performance is slow, I would try by creating a prequery by just account, then do a single join to the other join tables something like..

select 
      T.Accountno,
      T.MxDt,
      datediff(MM, T.MxDt, '2011-6-30') as Diffs, 
      P.Name as POName 
   from   
         ( select T1.AccountNo,
                  Max( T1.DateTxn ) MxDt
              from AccontTxn_skd T1
              group by T1.AccountNo ) T
         JOIN Account_skd A
            on T.AccountNo = A.AccountNo
            JOIN POName P
               on A.POCode = P.Code         <-- GUESSING as you didn't qualify alias.field 
              AND A.OfficeCode = P.GPOCode  <-- in your query for these two fields
   order by 
      len(T.AccountNo) DESC

You had other elements based on the T.ServiceCode matching, but since you are only grouping on the account number anyhow, did it matter which service code was used? Otherwise, you would need to group by both the account AND service code (which I would have added the service code into the prequery and added as join condition to the account table too).

SQl Query get data very slow from different tables

1 Answers1