January 20, 2011 at 7:29 pm
mister.magoo (1/20/2011)
I have to disagree about the root problem.There are two problems that I see.
1) The table variable with 200 rows is being SCANNED 390,000 times because of the IN.
Change your code like this:
...
EXISTS
(
SELECT 1
FROM [dbo].Permission AS PERM
WHERE FileId = vw.FileId AND EXISTS (SELECT 1 FROM @UserTableType AS UT WHERE UT.UniqueidentiferID = PERM.UniqueidentiferID )
)
Hopefully that will cut the 76M rows read from there down to about 390,000.
2) 88% Cost : The index seek on - [DatabaseName].[dbo].[Property].[IX_Property_TypeID_Name_FileStatus_INCLD] is estimating 24K rows but actually retrieving 74K rows - this is in turn causing the optimizer to think that it will need to seek on [DatabaseName].[dbo].[Ticket].[IX_Ticket_TicketID_INCLD] 24K times instead of 74K times.
This is made up of the following:
Problems inside the view vwProperty : There is a predicate performing a NOT EQUAL to 2 on column FileStatus - see if you can make a more positive selection here - it may help.
The optimiser is converting your @Name variable into a varchar(8000) before performing a LIKE on it - what is the definition of column Name in table Property? What is the definition of @Name? DO you really need a LIKE? Are you pattern matching?
Finally, just for fun, try adding OPTION(RECOMPILE) to the end of the SELECT query to see if the plan changes.
Thank you for your reply, i've tried to respond to your points in order below.
1. I did try rewriting it the way you suggested earlier, and to make sure I did exactly the same thinkg I copied and pasted your code again. The execution plan still generates the 76million records just like before.
2. I agree with your point 2, I saw those estimates, and if you look at one of the others it estimates 1 row but returns many more. Not sure why since the stats are up to date. I'm going to run DBCC Show_STATISTICS on a few to look at the data distribution.
3. The column (Name) is defined as varchar(50).
4. Yes, unfortunatly the LIKE is needed this supports a name LIKE search part in the application. I tried different values and it behaves the same way.
5. In testing I've been running DBCC FREEPROCCACHE before each test run to make sure I get a new query execution plan, but I can add the OPTION RECOMPILE but it should give me the same results but less intrusive 🙂
6. I did try modifing the view to = and list of ID's rather than exclude just the one to see if that changed anything, however the execution plan remains the same...
7. The @Name paramater is defined in the stored procedure as varchar(max) so that if the column length changes developer would not need to modify the stored procedures just the table column. I've never had an issue with this, but I tried changing it to match the column definition of varchar(50) , free the proc cache and it remains the same execution plan.
The only time I've seen the plan change and perform quickly is when I take the code and run it outside a stored procedure it behaves differntly then.
January 21, 2011 at 10:55 am
I've tried to create some filtered statistics on the predicate columns to see if that made a difference since the estimates are way off.. No luck..
Here is a small query from the main stored procedure that I tried out.
SELECT *
FROM [dbo].Property
WHERE
TypeId = 10
AND
Name = 'MyNameHere'
I created filtered statistics as follows:
CREATE STATISTICS
TestStatistics
ON
dbo.Property (TypeID, Name)
WHERE TypeId = 10 AND name = 'MyNameHere'
SELECT *
FROM [dbo].Property
WHERE
TypeId = 10
AND
Name LIKE 'WB%'
CREATE STATISTICS
TEST
ON
dbo.Property (NAME, TypeID)
WHERE TypeId = 10 AND name >= 'WA'
AND Name <= 'WC'
I ran a full scan update of the statistics, and recompiled the query, estimates are still off significantly..
Any other ideas??
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply