Optimize a code

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

This looks like a candidate for an indexed view, or possibly multiple indexed views.

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

Is there a reason they did not want to use an IN Clause for the Type field?

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

CanadaDBA

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

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

CanadaDBA

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.

...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

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

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')

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.

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.

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

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?

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

CanadaDBA

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.