Select Query run very Slow with jOIN

8 июня 2012 г. 1:54

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

Все ответы

8 июня 2012 г. 2:01

0

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
- Ответить
- Цитировать
8 июня 2012 г. 5:29

0

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
- Ответить
- Цитировать
8 июня 2012 г. 5:33

0
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
- Ответить
- Цитировать

10 июня 2012 г. 1:20

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)

11 июня 2012 г. 2:22

0

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:27

0

Where is your Dynamic SQL?
Many Thanks & Best Regards, Hua Min
- Ответить
- Цитировать
11 июня 2012 г. 2:37

0

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 г. 4: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 г. 10:02

0

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:57

Отвечающий

0
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/

Blog : MS SQL Development and Optimization Blog : Large scale of database and optimization
- Изменено Uri DimantMVP, Editor 11 июня 2012 г. 10:57
- Ответить
- Цитировать

Select Query run very Slow with jOIN

Все ответы

Продукты

Ресурсы

Решения

Обновления

Пробные версии

Сайты по теме

Обучение

Сертификация

Другие материалы и ссылки

Продукты

Другие ссылки