SQLTeam.com | Weblogs | Forums

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


#1

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.

Regards,
Javed Ahmed


#2

Are the columns involved in joins indexed?


#3

No. How do I index them?


#4

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.


#5
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);