SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Suggestions needed to optimize view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shieldmyadd
Starting Member

2 Posts

Posted - 09/28/2012 :  06:59:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  07:07:33  Show Profile  Reply with Quote
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.
Go to Top of Page

shieldmyadd
Starting Member

2 Posts

Posted - 09/28/2012 :  07:36:48  Show Profile  Reply with Quote
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'

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  07:58:02  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000