September 28, 2012 at 4:57 am
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
September 28, 2012 at 5:09 am
I would recommend to start get rid of the distinct unless it really is necessary. Distinct usually indicates poor design or incorrect queries.
Change the view to use JOINs rather than joining in the where clause. This won't make it faster, it will make it easier to read and ensure you haven't made any mistakes in the join.
Then, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 5:15 am
Here's how to make the LEFT OUTER JOIN on E.
You should not put join conditions in the where clause - it's very old fashioned, & it's easier to read if you use the modern syntax.
To speed it up you will need indexes that target the joins. To identify what these might be we will need details of what indexes the tables already have, and a rough idea how many rows are in the tables
--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
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)
)
/*
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 ?
*/
September 28, 2012 at 5:29 am
GilaMonster (9/28/2012)
I would recommend to start get rid of the distinct unless it really is necessary. Distinct usually indicates poor design or incorrect queries.Change the view to use JOINs rather than joining in the where clause. This won't make it faster, it will make it easier to read and ensure you haven't made any mistakes in the join.
Then, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Indexes are there for every column. Seems a bad design but can't change it now.
I can only work with my query.
September 28, 2012 at 5:31 am
laurie-789651 (9/28/2012)
Here's how to make the LEFT OUTER JOIN on E.You should not put join conditions in the where clause - it's very old fashioned, & it's easier to read if you use the modern syntax.
To speed it up you will need indexes that target the joins. To identify what these might be we will need details of what indexes the tables already have, and a rough idea how many rows are in the tables
--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
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)
)
/*
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 ?
*/
Checked the DB , indexes are there for every column, I know its bad design but I can only play around with my query. Rows are around 5000
September 28, 2012 at 5:37 am
shield (9/28/2012)
GilaMonster (9/28/2012)
I would recommend to start get rid of the distinct unless it really is necessary. Distinct usually indicates poor design or incorrect queries.Change the view to use JOINs rather than joining in the where clause. This won't make it faster, it will make it easier to read and ensure you haven't made any mistakes in the join.
Then, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Indexes are there for every column. Seems a bad design but can't change it now.
I can only work with my query.
It is a bad design and it means you're probably getting table scans, which no code changes will fix.
About the distinct???
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2012 at 5:45 am
GilaMonster (9/28/2012)
shield (9/28/2012)
GilaMonster (9/28/2012)
I would recommend to start get rid of the distinct unless it really is necessary. Distinct usually indicates poor design or incorrect queries.Change the view to use JOINs rather than joining in the where clause. This won't make it faster, it will make it easier to read and ensure you haven't made any mistakes in the join.
Then, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Indexes are there for every column. Seems a bad design but can't change it now.
I can only work with my query.
It is a bad design and it means you're probably getting table scans, which no code changes will fix.
About the distinct???
Removed the distinct keyword , works little better ... does DISTINCT has no effect on my query ?? m confused
September 28, 2012 at 5:47 am
DISTINCT removes duplicate rows from the result set. Only remove it if there ARE no duplicate rows!:-)
September 28, 2012 at 5:51 am
By the way, if you change the query to left outer join on E, you will get NULLs in the E columns where there is no matching data.
You can handle these by using ISNULL like this, using an appropriate alternative value depending on the data type:
A.B5USFT11,
A.B5ALV3VAL,
ISNULL(E.BAH6TX, 0) AS BAH6TX,
ISNULL(E.BAEHCD, '') AS BAEHCD,
September 28, 2012 at 5:59 am
Guys the whole problem was DISTINCT ...removed it an d performance increased like anything 🙂
But to be on the safe side ,I want some alternative to DISTINCT please suggest
September 28, 2012 at 6:03 am
You could check out the data & see if it's possible to get duplicate rows.
Someone might have added it because its needed, or maybe 'just in case'...
September 28, 2012 at 6:08 am
shield (9/28/2012)
But to be on the safe side ,I want some alternative to DISTINCT please suggest
There isn't one. You use distinct if you need to remove duplicate rows. If there are no duplicate rows, you shouldn't use distinct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply