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.TagBaseIDIt is very slow while executing. It takes around 3mins to execute 354 rows. Why is it so? Kindly assist me on this
Javed Ahmed
-
Javed
Please post the actual (not estimated) execution plan. How many rows are in each table?
John
-
I executed the view now and it took 2mins 5sec to execute 354 rows. Last time it had taken little more (I think 2:24). Phonebook table has 66 rows, TagBase table has 43 rows and TagTable has 349 rows.
-
Not that many rows, then. But not much we can do without the execution plan. Have you used sp_whoisactive to check for blocking while the query is running?
John
-
No, I am not using sp_whoisactive. The execution plan is to display all the rows in asp.net MVC view page.
-
I don't think you understand. Please have a look through this. You may find that it points you in the right direction to solve the problem yourself. If it doesn't, it'll at least show you how to post the execution plan so that we can help you.
Why aren't you using sp_whoisactive, by the way?
John
-
I tried in both SSMS and MVC View. The view is taking time in both. I tried executing the view after creating the index but still, it took 2:37 to execute. I have chosen image datatype for the field TagImage in TagBase Table and I am storing images in it. Is that the cause for slow execution of the view?
- javedhakim 53256 - Thursday, August 9, 2018 4:34 AM
Don't use the image data type - it's deprecated. Use varbinary instead. But the answer to your question is Maybe. What size are the images? If you're using SSMS, go to your query and choose Query Options from the Query menu. Click on Results and tick Discard results after execution. Run the query again. Does it take the same amount of time?
I won't ask for the execution plan again. If I've nothing more to offer, I'll just stop answering.
John
-
You could try adding some indexes. See if any of these makes a difference:
CREATE INDEX IX_PhoneBook_PhoneBookID ON dbo.PhoneBook(PhoneBookID);
CREATE INDEX IX_TagTable_PhoneBookID ON dbo.TagTable(PhoneBookID);
CREATE INDEX IX_TagTable_TagBaseID ON dbo.TagTable(TagBaseID);
CREATE INDEX IX_TagBaseTable_TagBaseID ON dbo.TagBaseTable(TagBaseID);
Or maybe your computer looks like this?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply