SQL code optimisation - 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)

SQL code optimisation

Author

Topic

RichardAnderton
Starting Member

15 Posts

Posted - 2012-06-14 : 12:13:43

Hi all,

I have managed to cobble together a view that extends the description our system allocates to certain fields. It kind of emulates an excel VLOOKUP function. However, I get the impression it is a bit clunky, and I was wondering if there was a neat way to optimise it?

SELECT
dbo.vw_nltranm.co,
RIGHT(dbo.vw_nltranm.element_2, 2) AS region,
dbo.vw_nltranm.element_1 + ' - ' + scheme.nlmastm.description AS Lev1Description,
RIGHT(dbo.vw_nltranm.element_2, 2) + ' - ' + nlmastm_1.description AS Lev2Description,
RIGHT(dbo.vw_nltranm.element_3, 2) + ' - ' + nlmastm_2.description AS Lev3Description,
RIGHT(dbo.vw_nltranm.element_4, 2) + ' - ' + nlmastm_4.description AS Lev4Description,
RTRIM(dbo.vw_nltranm.element5) + ' - ' + nlmastm_3.description AS Lev5Description,
dbo.vw_nltranm.nlyear, dbo.vw_nltranm.journal_number, dbo.vw_nltranm.journal_date, dbo.vw_nltranm.journal_desc, dbo.vw_nltranm.origin, dbo.vw_nltranm.vat_code, dbo.vw_nltranm.pre_base_amt, dbo.vw_nltranm.trans_period,
dbo.vw_nltranm.element_1, dbo.vw_nltranm.element_2, dbo.vw_nltranm.element_3, dbo.vw_nltranm.element_4, dbo.vw_nltranm.posting_code, nlmastm_3.kind, dbo.vw_nltranm.trans_period AS month

FROM
dbo.vw_nltranm
LEFT OUTER JOIN scheme.nlmastm AS nlmastm_2 ON dbo.vw_nltranm.element_3 = nlmastm_2.nominal_code
LEFT OUTER JOIN scheme.nlmastm AS nlmastm_3 ON dbo.vw_nltranm.posting_code = nlmastm_3.nominal_code
LEFT OUTER JOIN scheme.nlmastm AS nlmastm_4 ON dbo.vw_nltranm.element_4 = nlmastm_4.nominal_code
LEFT OUTER JOIN scheme.nlmastm AS nlmastm_1 ON dbo.vw_nltranm.element_2 = nlmastm_1.nominal_code
LEFT OUTER JOIN scheme.nlmastm ON dbo.vw_nltranm.element_1 = scheme.nlmastm.nominal_code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-14 : 12:52:40

For optimization, I'd start with indexes, ensuring that the join conditions are handled. I don't see that your query is clunky, although we'd need to see the view too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:15:33

if you want to avoid multiple left joins, you can use this


SELECT
co,
RIGHT(dbo.vw_nltranm.element_2, 2) AS region,
element_1 AS Lev1Description,
element_2 AS Lev2Description,
element_3 AS Lev3Description, 
element_4 AS Lev4Description,
element_5 AS Lev5Description,
nlyear,journal_number, journal_date, 
journal_desc, origin, vat_code, pre_base_amt, dbo.vw_nltranm.trans_period, 
element_1, element_2, element_3, 
element_4, posting_code, kind, 
trans_period AS month
FROM
(
SELECT v.*,v.element + '-' + n.description  AS LevDescription
FROM
(
SELECT *
FROM 
dbo.vw_nltranm
UNPIVOT (elementvalue for element IN (element_1,element_2,element_3,element_4,posting_code))u
)v
LEFT JOIN scheme.nlmastm n ON v.elementvalue = n.nominal_code
)m
PIVOT (MAX(LevDescription) FOR element IN ([element_1,element_2,element_3,element_4,posting_code))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RichardAnderton
Starting Member

15 Posts

Posted - 2012-06-16 : 11:24:08

Thanks.

i'll give them both a go, and see which offers the best improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:41:32

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources