0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Coding Panther
  • 29
  • 4
  • 1
    First: compare query plans (CTRL-L). Second: work out why you need distinct. Do you have an incorrect join? – Nick.Mc Nov 14 '15 at 02: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 Nov 14 '15 at 02: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 Nov 14 '15 at 03:40
  • 1
    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 Nov 14 '15 at 05:58
  • Here you can learn how get the explain plain http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Juan Carlos Oropeza Nov 17 '15 at 13:59
  • 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], – Coding Panther Nov 23 '15 at 15:59

2 Answers2

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.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
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],
Coding Panther
  • 29
  • 4