SQL Server Performance

Optimize a code

Discussion in 'General Developer Questions' started by CanadaDBA, Nov 8, 2005.

  1. CanadaDBA New Member

    Users of one of the applications are suffering to get time-out. I noticed lots of usage of INNER JOIN and OUTER JOINs in the database. Most of them are like the following. Is it possible to rewrite it in a better optimized way? Is there any folrmulation that I ask the developers to replace such codings with a better one?


    SELECT ...
    FROM tblAcc a
    left outer join tblExp t1 on t1.AccID = a.AccID and t1.Type = 'R11'
    left outer join tblExp t2 on t2.AccID = a.AccID and t2.Type = 'R12'
    left outer join tblExp t3 on t3.AccID = a.AccID and t3.Type = 'R13'
    left outer join tblExp t4 on t4.AccID = a.AccID and t4.Type = 'R14'
    left outer join tblExp t5 on t5.AccID = a.AccID and t5.Type = 'R15'
    left outer join tblExp t6 on t6.AccID = a.AccID and t6.Type = 'R16'
    left outer join tblExp t7 on t7.AccID = a.AccID and t7.Type = 'R17'
    left outer join tblExp t8 on t8.AccID = a.AccID and t8.Type = 'R18'
    left outer join tblExp t9 on t9.AccID = a.AccID and t9.Type = 'R19'
    left outer join tblExp t0 on t0.AccID = a.AccID and t0.Type = 'R20'



    CanadaDBA
  2. DStevensTN New Member

    This looks like a candidate for an indexed view, or possibly multiple indexed views.
  3. CanadaDBA New Member

    Facts:
    1. AccID is int and PK and Identity in tblAcc.
    2. AccID is Int and Type is VarChar(10) in tblExp. Both make the PK and non-Identity in tblExp.
    3. tblAcc has 19737 rows.
    4. tblExp has 78193 rows.
    In the code I don't see anywhere saying TOP 100 or something to limit the returned values.


    CanadaDBA
  4. DStevensTN New Member

    Is there a reason they did not want to use an IN Clause for the Type field?
  5. CanadaDBA New Member

    You mean it is possible to rewrite the SELECT with IN and get same results?

    CanadaDBA
  6. DStevensTN New Member

    SELECT ...FROM tblAcc a left outer join tblExp t1 on t1.AccID = a.AccID and t1.Type in ('R11','R12'....)
  7. CanadaDBA New Member

    Are you sure it returnes the same results? I was told that the SELECT will return all 19737 rows!!

    CanadaDBA
  8. Luis Martin Moderator

    Sound the same to me.
    Because in each join the tables are the same and the type could be between Rxx.

    But I'm not a develper so, wait for experts.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  9. DStevensTN New Member

    It would return the same number of rows, the only thing that could be different is if the return values from t2, t3, t4 etc. are used in a specific fashion. It would seem if this were the case it could be handled differently as well.
  10. CanadaDBA New Member

    ...the only thing that could be different is if the return values from t2, t3, t4 etc. are used in a specific fashion.

    Do you mean if I have reference in the SELECT part? Like for example:


    SELECT t1.Colx, t6.Coly,... FROM ...



    CanadaDBA
  11. CanadaDBA New Member

    Another question: How about if in the original SELECT I have INNER JOIN instead of Left Outer JOIN? Is the optimization the same?

    Thanks,

    CanadaDBA
  12. mmarovic Active Member

    No it is not the same. My guess is that in select column list there are column values from each subset joined. You can't do it using in because you will have more rows and less columns. For example:
    select a.accID, t1.column1, t2.column1
    FROM tblAcc a
    left outer join tblExp t1 on t1.AccID = a.AccID and t1.Type = 'R11'
    left outer join tblExp t2 on t2.AccID = a.AccID and t2.Type = 'R12'
    is not the same as:
    select a.accID, t1.column1
    FROM tblAcc a
    left outer join tblExp t1 on t1.AccID = a.AccID and t1.Type in ('R11', 'R12')
  13. mmarovic Active Member

    quote:Originally posted by CanadaDBA

    Another question: How about if in the original SELECT I have INNER JOIN instead of Left Outer JOIN? Is the optimization the same?

    Thanks,

    CanadaDBA
    It is not the same again. When you use left outer join and for specific AccID there is no row with type = <something> row will still be returned and column value from that set will be null. If inner join is used, row wouldn't be returned. Read about joins in BOL.
  14. mmarovic Active Member

    Are primary keys mentioned clustered? How often do you rebuild, defragment indexes? How do you populate tables mentioned? How often data are inserted and updated? If you want more help post the complete query, table and index structure.
  15. CanadaDBA New Member

    Are primary keys mentioned clustered? How often do you rebuild, defragment indexes?
    PKs are clustered in both tables. I have a scheduled maintenance job that optimizes, checks the integrity and backups every night. Is it enought to rebuild the indexes and prevent defragment indexes?

    How do you populate tables mentioned? How often data are inserted and updated?
    The tables are populated everyday. How can I exactly provide an answer for this question?

    CanadaDBA
  16. mmarovic Active Member

    quote:Originally posted by CanadaDBA

    Are primary keys mentioned clustered? How often do you rebuild, defragment indexes?
    PKs are clustered in both tables. I have a scheduled maintenance job that optimizes, checks the integrity and backups every night. Is it enought to rebuild the indexes and prevent defragment indexes?
    It is. You should check fragmentation and rebuild only fragmented indexes. If you rebuild clustered index you should drop all nonclustered indexes on that table and create them after clustered one is defragmented. There is article on this site about automating that task.


    quote:Originally posted by CanadaDBA

    How do you populate tables mentioned? How often data are inserted and updated?
    The tables are populated everyday. How can I exactly provide an answer for this question?

    CanadaDBA
    Do you insert data for specific AccID in tblExp only when parent row in tblAcc is added or rows are inserted randomly? Do you update data in both tables? Is there a process that load them from somewhere or it is normal oltp processing?
  17. CanadaDBA New Member

    Thanks Mirko for the follow up!
    It is normal OLTP processing. What's your suggestion?

    CanadaDBA
  18. mmarovic Active Member

    I can't give you more recommendation without having question from my previous post answered and without knowing table structure and exact query. I need this to see what would be optimal index design and fill-factor. I guess you should keep pks clustered but maybe you should play with different fill factor.

Share This Page