none
Select Query run very Slow with jOIN

    Вопрос

  • I have write Select STatement but it run very slow

    SELECT  Result = ISNULL(SUM(Amount_Lcy), 0)
    FROM    ( SELECT    Coll_ID , Amount_Lcy / sumone * Amount_Lcy AS Amount_Lcy , Coll_Chinhchu , Coll_HTTVV , SB_Code , Cust_ID , xyz.Yes , GL_Detail , sumone
              FROM  ( SELECT    Coll_ID , Amount_Lcy , Coll_Chinhchu , Coll_HTTVV , tblTwo.SB_Code , Cust_ID , GL_Detail , sumone
                          FROM ( SELECT    b.Coll_ID , b1.GL_Detail , b1.Amount_Lcy , b.sumone , b.Coll_Chinhchu ,
                                                b.Coll_Code ,
                                                b.Coll_HTTVV ,
                                                b.Cust_ID ,
                                                b1.Limit_Ref
                                      FROM      ( SELECT    y.Coll_ID ,
                                                            y.Coll_Code ,
                                                            y.Coll_Chinhchu ,
                                                            y.Coll_HTTVV ,
                                                            y.Cust_ID ,
                                                            y.Limit_Ref ,
                                                            x.sumone
                                                  FROM      Coll_Loan_Infor_Save y
                                                            LEFT JOIN ( SELECT
                                                                  SUM(tbl0.amount_lcy) AS sumone ,
                                                                  tbl.Limit_Ref
                                                                  FROM
                                                                  Coll_Loan_Infor_Save tbl
                                                                  LEFT JOIN CRB_Save tbl0 ON tbl.Limit_Ref = tbl0.Limit_Ref
                                                                  WHERE
                                                                  tbl0.GL_Code LIKE '2[1,2,3]%'
                                                                  AND ( GL_Detail LIKE 'LD%'
                                                                  OR GL_Detail LIKE 'PD%'
                                                                  )
                                                                  AND tbl.Save_date = @ToDate
                                                                  AND tbl0.Save_date = @ToDate
                                                                  GROUP BY tbl.Limit_Ref
                                                                  ) x ON y.Limit_Ref = x.Limit_Ref
                                                  WHERE     Save_Date = @ToDate
                                                ) b
                                                LEFT JOIN CRB_Save b1 ON b.Limit_Ref = b1.Limit_Ref
                                      WHERE     b1.GL_Code LIKE '2%'
                                                AND ( b1.GL_Detail LIKE 'LD%'
                                                      OR b1.GL_Detail LIKE 'PD%'
                                                    )
                                                AND Save_date = @ToDate
                                                AND SubBranch_ID IN (
                                                SELECT  Data
                                                FROM    dbo.fnParseList(',',
                                                                  @SubBranch_ID) )
                                    ) AS tblOne
                                    LEFT JOIN CollateralType tblTwo ON tblOne.Coll_Code = tblTwo.Code
                        ) AS tblThree
                        LEFT JOIN TochuckolaTCTD xyz ON tblThree.Cust_ID = xyz.customerID
            ) AS tblFour
    WHERE   tblFour.SB_Code != '01'
            AND tblFour.Coll_Chinhchu = 'YES'
            AND tblFour.Yes IS NULL

    with CRB_Save Table have avoid 20,000 record

    Help me



Все ответы

  • How long does it take? Consider to have an Indexed view on this set

    SELECT    y.Coll_ID ,
                                                            y.Coll_Code ,
                                                            y.Coll_Chinhchu ,
                                                            y.Coll_HTTVV ,
                                                            y.Cust_ID ,
                                                            y.Limit_Ref ,
                                                            x.sumone
                                                  FROM      Coll_Loan_Infor_Save y
                                                            LEFT JOIN ( SELECT
                                                                  SUM(tbl0.amount_lcy) AS sumone ,
                                                                  tbl.Limit_Ref
                                                                  FROM
                                                                  Coll_Loan_Infor_Save tbl
                                                                  LEFT JOIN CRB_Save tbl0 ON tbl.Limit_Ref = tbl0.Limit_Ref
                                                                  WHERE
                                                                  tbl0.GL_Code LIKE '2[1,2,3]%'
                                                                  AND ( GL_Detail LIKE 'LD%'
                                                                  OR GL_Detail LIKE 'PD%'
                                                                  )
                                                                  AND tbl.Save_date = @ToDate
                                                                  AND tbl0.Save_date = @ToDate
                                                                  GROUP BY tbl.Limit_Ref
                                                                  ) x ON y.Limit_Ref = x.Limit_Ref
                                                  WHERE     Save_Date = @ToDate
                                                
    on column Limit_Ref

    Many Thanks & Best Regards, Hua Min

  • How long does it take? Consider to have an Indexed view on this set


    Many Thanks & Best Regards, Hua Min

    Hi Hua Min, this bit is very hard to put into Indexed view, it against many of rules for creating an indexed view
  • try to write a stored procedure and break down your inner subqueries into temp tables.

    You can create the indexes on your temp tables to speed up the joining.

    this is just ideas.

    • Предложено в качестве ответа Hasham NiazEditor 8 июня 2012 г. 5:51
  • I've tried to refactor the query a bit by removing seemingly redundant derived tables. Surely, there is much more scope for improvement.

    SELECT Result = ISNULL(SUM(Amount_Lcy), 0)						  -- Only this one column is finally required so ...
    FROM   (SELECT --Coll_ID                                        , -- no need to SELECT all these columns here
                   Amount_Lcy / sumone * Amount_Lcy AS Amount_Lcy --, -- Not sure what this calculation is for ...
                   --Coll_Chinhchu                                  , -- because sumone is SUM of amount_lcy earlier!
                   --Coll_HTTVV                                     ,
                   --tblTwo.SB_Code                                 ,
                   --Cust_ID                                        ,
                   --xyz.Yes                                        ,
                   --GL_Detail                                      ,
                   --sumone
                   FROM    
                   (
    				SELECT y.Coll_ID       ,
                           y.Coll_Code     ,
                           y.Coll_Chinhchu ,
                           y.Coll_HTTVV    ,
                           y.Cust_ID       ,
                           y.Limit_Ref     ,
                           x.sumone        ,
                           b1.GL_Detail    ,
                           b1.Amount_Lcy   ,
                    FROM   Coll_Loan_Infor_Save AS y
                           INNER JOIN
                                   (SELECT  c1.Limit_Ref,
    										c1.Save_date,
    										SUM(r1.amount_lcy) AS sumone
                                   FROM     Coll_Loan_Infor_Save	AS c1 --tbl
                                            INNER JOIN CRB_Save		AS r1 --tbl0
    											ON      c1.Limit_Ref = r1.Limit_Ref
    											AND		c1.Save_date = r1.Save_date
                                   WHERE    r1.GL_Code        LIKE '2[1,2,3]%'
                                   AND
                                            (
                                                     GL_Detail LIKE 'LD%'
                                            OR       GL_Detail LIKE 'PD%'
                                            )
                                   AND      c1.Save_date = @ToDate -- This condition effectively makes it a INNER JOIN
                                   AND      r1.Save_date = @ToDate -- and limits to this date only so adding to GROUP BY
                                   GROUP BY c1.Limit_Ref,
    										c1.Save_date
                                   )
                                   AS x
                           ON       y.Limit_Ref = x.Limit_Ref
                           AND		y.Save_date = x.Save_date
                   --WHERE   Save_Date           = @ToDate -- This condition effectively makes it a INNER JOIN
                   LEFT JOIN CRB_Save AS b1
                   ON       b.Limit_Ref = b1.Limit_Ref
                   AND		b.Save_date = b1.Save_date
    			   WHERE    b1.GL_Code       LIKE '2%'		-- Probably this portion 
    			   AND										-- is redundant and can
    					   (								-- be removed because CRB_Save 
    							   b1.GL_Detail LIKE 'LD%'	-- is used as r1 or tbl0 too
    					   OR      b1.GL_Detail LIKE 'PD%'	-- within the derived table x
    					   )
    			   AND     SubBranch_ID IN
    					   (SELECT Data
    					   FROM    dbo.fnParseList(',', @SubBranch_ID)
    					   )
                   )
                   AS tblOne
                   LEFT JOIN CollateralType AS tblTwo
                   ON      tblOne.Coll_Code = tblTwo.Code
    			   LEFT JOIN TochuckolaTCTD AS xyz
    			   ON      tblOne.Cust_ID = xyz.customerID
           )
           AS tblFour
    WHERE  tblFour.SB_Code      != '01'
    AND    tblFour.Coll_Chinhchu = 'YES'
    AND    tblFour.Yes     IS NULL


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    10 июня 2012 г. 1:20
  • Problem Select Statement run takes about 2 seconds in SQL Management Studio

    But When I use Dynamic SQL with string SQL then command run takes about 2 minutes.

    I don't understand Why Statement Select run verry slow

    Help me

    11 июня 2012 г. 2:22
  • Where is your Dynamic SQL?

    Many Thanks & Best Regards, Hua Min

    11 июня 2012 г. 2:27
  • Hi,

    There are usually a variety of reasons for which dynamic sql runs slower than static TSQL. Usually, the SQL Server Engine can select a better execution plan when it knows the values of the parameters passed in ( as is the case with static TSQL). with dynamic tsql, the optimizer is blind to values and will hence generate a sub optimal plan which could be the potential cause of slowness. you also lose the benefit of a complied query plan 7 plan reuse. And, if not done carefully, dynamic sql is vulnerable to sql injection attacks. I would recommend encapsulating the query in a stored procedure, instead if using dynamic sql. Please follow these links for more detail.

    http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/

    http://weblogs.asp.net/aaguiar/archive/2006/06/22/Stored-Procs-vs-Dynamic-SQL.aspx

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    11 июня 2012 г. 2:37
  • When I use Dynamic SQL it run very slow

    Statement:

    DECLARE @Sql NVARCHAR(MAX)

    sET @Sql = N'

    SELECT Result = ISNULL(SUM(Amount_Lcy), 0) FROM ( SELECT Coll_ID , Amount_Lcy / sumone * Amount_Lcy AS Amount_Lcy , Coll_Chinhchu , Coll_HTTVV , SB_Code , Cust_ID , xyz.Yes , GL_Detail , sumone FROM ( SELECT Coll_ID , Amount_Lcy , Coll_Chinhchu , Coll_HTTVV , tblTwo.SB_Code , Cust_ID , GL_Detail , sumone FROM ( SELECT b.Coll_ID , b1.GL_Detail , b1.Amount_Lcy , b.sumone , b.Coll_Chinhchu , b.Coll_Code , b.Coll_HTTVV , b.Cust_ID , b1.Limit_Ref FROM ( SELECT y.Coll_ID , y.Coll_Code , y.Coll_Chinhchu , y.Coll_HTTVV , y.Cust_ID , y.Limit_Ref , x.sumone FROM Coll_Loan_Infor_Save y LEFT JOIN ( SELECT SUM(tbl0.amount_lcy) AS sumone , tbl.Limit_Ref FROM Coll_Loan_Infor_Save tbl LEFT JOIN CRB_Save tbl0 ON tbl.Limit_Ref = tbl0.Limit_Ref WHERE tbl0.GL_Code LIKE '2[1,2,3]%' AND ( GL_Detail LIKE 'LD%' OR GL_Detail LIKE 'PD%' ) AND tbl.Save_date = @ToDate AND tbl0.Save_date = @ToDate GROUP BY tbl.Limit_Ref ) x ON y.Limit_Ref = x.Limit_Ref WHERE Save_Date = @ToDate ) b LEFT JOIN CRB_Save b1 ON b.Limit_Ref = b1.Limit_Ref WHERE b1.GL_Code LIKE '2%' AND ( b1.GL_Detail LIKE 'LD%' OR b1.GL_Detail LIKE 'PD%' ) AND Save_date = @ToDate AND SubBranch_ID IN ( SELECT Data FROM dbo.fnParseList(',', @SubBranch_ID) ) ) AS tblOne LEFT JOIN CollateralType tblTwo ON tblOne.Coll_Code = tblTwo.Code ) AS tblThree LEFT JOIN TochuckolaTCTD xyz ON tblThree.Cust_ID = xyz.customerID ) AS tblFour WHERE tblFour.SB_Code != '01' AND tblFour.Coll_Chinhchu = 'YES' AND tblFour.Yes IS NULL'

    EXEC sp_executesql @Sql

    but it Run very slow

    11 июня 2012 г. 4:37
  • Yes, We understand that when you use dynamic SQL, the query runs very slow and my earlier reply tires to explain to you what could be the possible reasons. It also explains the use of stored procedures as a possible solution. Are the above responses clear to you? Do you have any other questions about this ?

    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    11 июня 2012 г. 10:02
  • Is that possible to show us an execution plan of the query. I think it uses parallelism, am I right?

    Best Regards,Uri Dimant SQL Server MVP

    http://sqlblog.com/blogs/uri_dimant/


    11 июня 2012 г. 10:57
    Отвечающий