SQL Server Query Performance Issue

Question

I have about 1,60,000 rows retrieved in 4 mins if I use following.

SELECT DISTINCT * 
FROM 
     (ABOUT 10 TABLES WITH LEFT OUTER JOIN )

But I need to extract only about 25 columns with some basic operations on them. But my below query is taking forever to execute (beyond an hour).

Col1, Col2 etc, ColA, ColB etc represents columns. Also I need to have DISTINCT on them because that is the final version that I need.

SELECT DISTINCT 
    Col1, Col2,...Col23,
    Table1.Description1 + Table1.Description2 as FinalDescription,
    (CASE WHEN COLA = 'XX' THEN 
            cast(round(COLB,2) as numeric(10,2))
          ELSE 
            cast(round(COLB*-1  ,2) as numeric(10,2))
     END) AS 'Amount',
    CASE WHEN ISNULL(COLC,'')='' 
            THEN  COLD + 'TO' +  COLE
         WHEN SUBSTRING ( COLC ,1 , 3 )IN ('XY')  
            THEN  COLD+','+ SUBSTRING (COLE ,5 , 12)
         ELSE  COLD + ','+ COLF
    END as 'Custom_Column'
FROM 
    (ABOUT 10 TABLES WITH LEFT OUTER JOIN)

What should I do to improve the performance to get the columns in the format that I am interested in?

First: compare query plans (CTRL-L). Second: work out why you need distinct. Do you have an incorrect join? — Nick.Mc
– Nick.Mc, Commented Nov 14, 2015 at 2:00
I dont have any inner joins. Distinct is required as the tables are not correctly designed and there are duplicates. That is how the set up. Unable to change the setup. Also dont have permission to see query plan. — Coding Panther
– Coding Panther, Commented Nov 14, 2015 at 2:53
Run your query with the Amount column removed and then run it again with the Custom_Coulmn removed. You will get to know which is problematic. — Adish
– Adish, Commented Nov 14, 2015 at 3:40
if you cant see the query plan and the data is inherently dirty then all you can do is remove pieces until you isolate the worst performing ones. — Nick.Mc
– Nick.Mc, Commented Nov 14, 2015 at 5:58
Here you can learn how get the explain plain stackoverflow.com/questions/7359702/… — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 17, 2015 at 13:59

Juan Carlos Oropeza · Accepted Answer · 2015-11-14 03:52:40Z

1

If you run your second query without distinct is faster? Maybe you can calculate your custom column and then perfom an union.

   SELECT *, 'Amount', 'Custom_Column'
   FROM Your Query
   UNION 
   SELECT 0.*, 0 as 'Amount', 0 as 'Custom_Column'

Anyway if you provide an explain plan and tell us what is your db we can give you better suggestions.

answered Nov 14, 2015 at 3:52

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Coding Panther · Accepted Answer · 2015-11-16 16:14:25Z

0

Below CASE statement in select is causing the issue. I just removed it from SELECT but DID NOT alter any join conditions & it finished very quickly. I am not sure why this below simple case statement is causing a performance issue. Any inputs on how to improve this?

   (CASE WHEN tabled.col5 = 'ABCD' THEN 'ABCD'

   ELSE 'UVWX' END) AS [Mode],

answered Nov 16, 2015 at 16:14

Coding Panther

294 bronze badges

Collectives™ on Stack Overflow

SQL Server Query Performance Issue

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related