SQLTeam.com | Weblogs | Forums

View created by joining three tables (using Left outer join) takes more time to execute


I had created a view by joining three tables as follows,

SELECT  ROW_NUMBER() OVER (ORDER BY dbo.PhoneBook.PhoneBookID) AS MyPrimaryID, dbo.PhoneBook.PhoneBookID, 
dbo.PhoneBook.Name, dbo.PhoneBook.Department, dbo.PhoneBook.Email, dbo.PhoneBook.Section, dbo.PhoneBook.Designation, 
dbo.PhoneBook.Extension, dbo.PhoneBook.PhoneBookCreator, dbo.TagTable.TagID, dbo.TagTable.TagDate, dbo.TagBaseTable.TagBaseID, 
dbo.TagBaseTable.TagName, dbo.TagBaseTable.TagImage, dbo.TagBaseTable.PublisherFROM  dbo.PhoneBook LEFT OUTER JOIN   
dbo.TagTable ON dbo.PhoneBook.PhoneBookID = dbo.TagTable.PhoneBookID LEFT OUTER JOIN   
dbo.TagBaseTable ON dbo.TagTable.TagBaseID = dbo.TagBaseTable.TagBaseID

It is very slow while executing. It takes around 3mins to execute 354 rows. Why is it so? Kindly assist me on this.

Javed Ahmed


Are the columns involved in joins indexed?


No. How do I index them?


The execution plan is to display all the rows in asp .net MVC view page. I executed the view now and it took 2mins 5sec to execute 354 rows. Last time it had taken more (I think 2:24). Phonebook table has 66 rows, TagBase table has 43 rows and TagTable has 349 rows.

CREATE INDEX IDXPhoneBookPhoneBookID ON dbo.PhoneBook(PhoneBookID);
CREATE INDEX IDXTagTablePhoneBookID ON dbo.TagTable(PhoneBookID);
CREATE INDEX IDXTagTableTagBaseID ON dbo.TagTable(TagBaseID);
CREATE INDEX IDXTagBaseTableTagBaseID ON dbo.TagBaseTable(TagBaseID);