Suggestions needed to optimize view

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

General SQL Server Forums

New to SQL Server Programming

Suggestions needed to optimize view

Author

Topic

shieldmyadd
Starting Member

2 Posts

Posted - 2012-09-28 : 06:59:17

check this view and then my questions below
CREATE VIEW BASASB5UVP AS ( SELECT DISTINCT
B.BUBRNB,
A.B5POTX,
C.BVEFTX,
C.BVEGTX,
A.B5AL26TXT,
A.B5AL27TXT,
B.BUEFTX,
B.BUEGTX,
A.B5AL28TXT,
A.B5AL29TXT,
B.BUEITX,
A.B5AL30TXT,
B.BUEJTX,
A.B5PNTX,
B.BUAPNB,
A.B5AL3VTXT,
A.B5AL3WTXT,
A.B5AGVA,
A.B5AHVA,
A.B5C0ST,
A.B5HYTX,
A.B5ALV4VAL,
A.B5ALADNBR,
A.B5BLNB,
A.B5ALCD,
A.B5BKNB,
A.B5AEPC,
A.B5C0VA,
A.B5I0TX,
A.B5AFPC,
A.B5C1VA,
A.B5USFT3,
A.B5C2VA,
A.B5I1TX,
A.B5USFT4,
A.B5C3VA,
A.B5I2TX,
A.B5USFT5,
A.B5ALVXVAL,
A.B5USFT8,
A.B5ALV0VAL,
A.B5I4TX,
A.B5USFT9,
A.B5ALV1VAL,
A.B5I3TX,
A.B5USFT6,
A.B5ALVYVAL,
A.B5I5TX,
A.B5USFT7,
A.B5ALVZVAL,
A.B5USFT10,
A.B5ALV2VAL,
A.B5USIN13,
A.B5USFT11,
A.B5ALV3VAL,
E.BAH6TX,
E.BAEHCD,
E.BAI5TX,
E.BAI4TX,
D.LOCATION,
D.MASTER0CO,
D.COMPANY0NO,
D.SYMBOL,
D.POLICY0NUM,
D.MODULE,
D.LINE0BUS,
D.TYPE0ACT,
D.ISSUE0CODE,
A.B5PMTX,
A.B5AL6TXT,
A.B5AL7TXT,
A.B5AL8TXT,
A.B5AL9TXT ,
A.B5AL10TXT,
A.B5AL11TXT,
A.B5AL12TXT,
A.B5AL13TXT,
A.B5ALWZVAL,
A.B5ALAAVAL,
A.B5ALABVAL,
A.B5AL14TXT,
A.B5AL15TXT,
A.B5AL16TXT,
A.B5AL17TXT,
A.B5AL18TXT,
A.B5AL19TXT,
A.B5AL20TXT,
A.B5AL21TXT,
A.B5USVA4,
A.B5USVA5,
A.B5ALACVAL,
A.B5ALADVAL,
A.B5AL32TXT,
A.B5AL33TXT,
A.B5AL34TXT,
A.B5AL35TXT,
A.B5CZVA,
A.B5ALAFVAL,
A.B5AL3XTXT,
A.B5C1ST,
A.B5ALACNBR,
A.B5ALAEVAL,
A.B5AKCD,
A.B5PTTX,
A.B5USIN20,
A.B5USIN21,
A.B5USIN22,
A.B5USIN23

FROM ASB5CPP A, ASBUCPP B, ASBVCPP C, PMSP0200 D , ASBACPP E WHERE

A.B5AACD=B.BUAACD AND
A.B5ABCD=B.BUABCD AND
A.B5ARTX=B.BUARTX AND
A.B5ASTX=B.BUASTX AND
A.B5ADNB=B.BUADNB AND

B.BUAACD=C.BVAACD AND
B.BUABCD=C.BVABCD AND
B.BUARTX=C.BVARTX AND
B.BUASTX=C.BVASTX AND
B.BUADNB=C.BVADNB AND

E.BAAACD=C.BVAACD AND
E.BAABCD=C.BVABCD AND
E.BAARTX=C.BVARTX AND
E.BAASTX=C.BVASTX AND
E.BAADNB=C.BVADNB AND

(A.B5BRNB=C.BVBRNB OR A.B5AENB=C.BVBRNB) AND
(B.BUBRNB =A.B5BRNB OR B.BUBRNB= C.BVBRNB) AND

D.SYMBOL = A.B5ARTX AND
D.POLICY0NUM = A.B5ASTX AND
D.MODULE = A.B5ADNB )

This is what I want to do now
1) Table E ,will not have values all the time ,so want it to be in left join or right join
2) When i do select * from this view , the performance is very slow, how to I optimize this ?

Please suggest

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 07:07:33

If you want to use any of the OUTER joins (left, right, full), you cannot use the ANSI style joins that you have in your query. Instead, use the joins like shown below:

...
	A.B5USIN21,
	A.B5USIN22,
	A.B5USIN23
FROM
	ASB5CPP A
	INNER JOIN  ASBUCPP B ON
		A.B5ABCD = B.BUABCD
		AND A.B5ARTX = B.BUARTX
		AND A.B5ASTX = B.BUASTX
		AND A.B5ADNB = B.BUADNB
	INNER JOIN ASBVCPP C ON 
....

You can turn on the query plan in SSMS and run the query to see what part of the query is taking up the most resources. Once you identify those, examine the number of rows in the tables that are participating and whether there are proper indexes that can be made use of.

shieldmyadd
Starting Member

2 Posts

Posted - 2012-09-28 : 07:36:48

MODIFIED QUERY LIKE THIS but still slow (this query is expected to return around 5000 rows)
SELECT
B.BUBRNB,
A.B5POTX,
C.BVEFTX,
C.BVEGTX,
A.B5AL26TXT,
A.B5AL27TXT,
B.BUEFTX,
B.BUEGTX,
A.B5AL28TXT,
A.B5AL29TXT,
B.BUEITX,
A.B5AL30TXT,
B.BUEJTX,
A.B5PNTX,
B.BUAPNB,
A.B5AL3VTXT,
A.B5AL3WTXT,
A.B5AGVA,
A.B5AHVA,
A.B5C0ST,
A.B5HYTX,
A.B5ALV4VAL,
A.B5ALADNBR,
A.B5BLNB,
A.B5ALCD,
A.B5BKNB,
A.B5AEPC,
A.B5C0VA,
A.B5I0TX,
A.B5AFPC,
A.B5C1VA,
A.B5USFT3,
A.B5C2VA,
A.B5I1TX,
A.B5USFT4,
A.B5C3VA,
A.B5I2TX,
A.B5USFT5,
A.B5ALVXVAL,
A.B5USFT8,
A.B5ALV0VAL,
A.B5I4TX,
A.B5USFT9,
A.B5ALV1VAL,
A.B5I3TX,
A.B5USFT6,
A.B5ALVYVAL,
A.B5I5TX,
A.B5USFT7,
A.B5ALVZVAL,
A.B5USFT10,
A.B5ALV2VAL,
A.B5USIN13,
A.B5USFT11,
A.B5ALV3VAL,
E.BAH6TX,
E.BAEHCD,
E.BAI5TX,
E.BAI4TX,
D.LOCATION,
D.MASTER0CO,
D.COMPANY0NO,
D.SYMBOL,
D.POLICY0NUM,
D.MODULE,
D.LINE0BUS,
D.TYPE0ACT,
D.ISSUE0CODE,
A.B5PMTX,
A.B5AL6TXT,
A.B5AL7TXT,
A.B5AL8TXT,
A.B5AL9TXT ,
A.B5AL10TXT,
A.B5AL11TXT,
A.B5AL12TXT,
A.B5AL13TXT,
A.B5ALWZVAL,
A.B5ALAAVAL,
A.B5ALABVAL,
A.B5AL14TXT,
A.B5AL15TXT,
A.B5AL16TXT,
A.B5AL17TXT,
A.B5AL18TXT,
A.B5AL19TXT,
A.B5AL20TXT,
A.B5AL21TXT,
A.B5USVA4,
A.B5USVA5,
A.B5ALACVAL,
A.B5ALADVAL,
A.B5AL32TXT,
A.B5AL33TXT,
A.B5AL34TXT,
A.B5AL35TXT,
A.B5CZVA,
A.B5ALAFVAL,
A.B5AL3XTXT,
A.B5C1ST,
A.B5ALACNBR,
A.B5ALAEVAL,
A.B5AKCD,
A.B5PTTX,
A.B5USIN20,
A.B5USIN21,
A.B5USIN22,
A.B5USIN23
FROM ASB5CPP A
INNER JOIN ASBUCPP B
ON
A.B5AACD=B.BUAACD AND
A.B5ABCD=B.BUABCD AND
A.B5ARTX=B.BUARTX AND
A.B5ASTX=B.BUASTX AND
A.B5ADNB=B.BUADNB
INNER JOIN ASBVCPP C
ON
B.BUAACD=C.BVAACD AND
B.BUABCD=C.BVABCD AND
B.BUARTX=C.BVARTX AND
B.BUASTX=C.BVASTX AND
B.BUADNB=C.BVADNB
INNER JOIN PMSP0200 D
ON
D.SYMBOL = A.B5ARTX AND
D.POLICY0NUM = A.B5ASTX AND
D.MODULE = A.B5ADNB
LEFT OUTER JOIN ASBACPP E
ON
E.BAAACD=C.BVAACD AND
E.BAABCD=C.BVABCD AND
E.BAARTX=C.BVARTX AND
E.BAASTX=C.BVASTX AND
E.BAADNB=C.BVADNB
WHERE
(A.B5BRNB=C.BVBRNB OR A.B5AENB=C.BVBRNB) AND
(B.BUBRNB =A.B5BRNB OR B.BUBRNB= C.BVBRNB) AND POLICY0NUM= '1000010'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 07:58:02

quote:
MODIFIED QUERY LIKE THIS but still slow (this query is expected to return around 5000 rows)

Changing the style of joins would not speed up the query. To see if you can make it more efficient/faster, you need to examine the query plan to see if there are areas that you can improve. Do a few things:

a) run the query after enabling the query plan viewer (Query->Include Actual Execution Plan from SSMS menu). The query plan will show you which parts of the query are taking up the resources as percentage.

b) Concentrate on those parts that are taking up the largest chunk. see if they have things like table scans and such that can be avoided if there are proper indexes. Do some googling to figure out what the bad indicators are, and how you might get around those. SQL Server also gives you some help in the query plan window via missing indexes, but don't take them at their word.

c) Make sure the database is maintained properly i.e., statistics are up to date, indexes are not fragmented etc. Again, google for it and you will find plenty of resources.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources