Query optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query optimization

Author

Topic

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2009-02-26 : 09:46:40

Hi.

Can I optimize this query? :


SELECT A.Col1, A.Col2, B.Col1,
  CASE WHEN A.Col3 IS NULL
    THEN (SELECT TOP 1 C.Col4 FROM dbo.TableC C WHERE C.Col1 = A.Col1)
    ELSE (SELECT TOP 1 D.Col3 FROM dbo.TableD D)
  END AS Value1,
  ISNULL(B.Col2, (SELECT TOP 1 C.Col4 FROM dbo.TableC C WHERE C.Col1 = A.Col1)) AS Value2
FROM dbo.TableA A, dbo.TableB B WHERE A.Col5 = B.Col6

I would like to not repeat twice this subquery:

(SELECT TOP 1 C.Col4 FROM dbo.TableC C WHERE C.Col1 = A.Col1)

.

I think I've seen somewhere article that is possible to write a subquery "before" the main query and then use it value again and again... or am I wrong?

Thanks for your time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 09:58:12

You mean a CTE?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 10:02:13

[code]SELECT a.Col1,
a.Col2,
b.Col1,
CASE
WHEN a.Col3 IS NULL THEN c.Col4
ELSE d.Col3
END AS Value1,
COALESCE(b.Col2, c.Col4) AS Value2
FROM dbo.TableA AS a
INNER JOIN dbo.TableB AS b ON b.Col6 = a.Col5
LEFT JOIN (
SELECT Col1,
MAX(Col4) AS Col4
FROM dbo.TableC
GROUP BY Col1
) AS c ON c.Col1 = a.Col1
LEFT JOIN (
SELECT TOP 1
Col3
FROM dbo.TableD
) AS d ON 1 = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

Wodzu
Yak Posting Veteran

58 Posts

Posted - 2009-02-26 : 10:35:06

Thanks Peso :)

I meant CTE but I didn't know it name:) However, second example was also very instructive.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 10:58:31

And how about performance?

If you run your example, how long does that take?
And how long does my suggestion take?

If you are sure there are values in TableD, change LEFT JOIN to CROSS JOIN and remove ON part.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources