June 12, 2009 at 3:00 am
Hi,
I have a stored procedure that is running very slow considering the rows, indexes and what its actually doing.
Firstly, some background... The 2 main tables are Properties and PropertyImages:
CREATE TABLE [dbo].[Properties](
[PropertyID] [int] IDENTITY(1,1) NOT NULL,
[SellerID] [int] NOT NULL,
[RegionID] [int] NOT NULL,
[PriceBracketID] [int] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[DetailedDescription] [nvarchar](max) NOT NULL,
[Postcode] [nvarchar](20) NULL,
[DateListed] [datetime] NOT NULL,
[ExpiryDate] [datetime] NULL,
[NumberOfViews] [int] NOT NULL,
[NumberOfEnquiries] [int] NOT NULL,
[IsVisible] [bit] NOT NULL,
[Price] [money] NOT NULL,
PRIMARY KEY CLUSTERED
(
[PropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[PropertyImages](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[PropertyID] [int] NULL,
[ImageURL] [nvarchar](100) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[IsVisible] [bit] NOT NULL,
[IsPrimary] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Properties has: 98007 rows
PropertyImages has: 588014 rows
Properties Indexes:
IX_Properties nonclustered located on PRIMARY RegionID, PriceBracketID, SellerID, DateListed
PK__Properties__22AA2996 clustered, unique, primary key located on PRIMARY PropertyID
PropertyImages Indexes:
IX_PI nonclustered located on PRIMARY PropertyID, IsPrimary
PK__PropertyImages__267ABA7A clustered, unique, primary key located on PRIMARY ImageID
The stored procedure that is running is:
ALTER PROCEDURE [dbo].[CorePropertySearch]
@RegionID INT,
@PriceBracketID INT
AS
IF @RegionID = -1
BEGIN
SET @RegionID = NULL
END
IF @PriceBracketID = -1
BEGIN
SET @PriceBracketID = NULL
END
SELECT TOP 1000
p.PropertyID,
ISNULL(sp.CompanyName, aspu.UserName) AS 'Seller',
r.RegionName,
p.Title,
p.Price,
p.DetailedDescription,
pi.ImageURL
FROM
Properties p
INNER JOIN
Regions r ON p.RegionID = r.RegionID
INNER JOIN
PriceBrackets pb ON p.PriceBracketID = pb.PriceBracketID
INNER JOIN
UserMapping u ON p.SellerID = u.UserID
INNER JOIN
aspnet_Users aspu ON u.MembershipUserID = aspu.UserID
LEFT OUTER JOIN
SellerProfiles sp ON p.SellerID = sp.UserID
INNER JOIN
PropertyImages pi ON p.PropertyID = pi.PropertyID AND pi.IsPrimary = 1
WHERE
p.RegionID = COALESCE(@RegionID, p.RegionID)
AND
p.PriceBracketID = COALESCE(@PriceBracketID, p.PriceBracketID)
AND
p.ExpiryDate > GETDATE()
AND
p.IsVisible = 1
ORDER BY
p.DateListed DESC
The query takes around 52 seconds to return 1000 rows. This is a search functionality for a property site, so query time is crucial.
I'd be grateful if anyone can point me in the right direction. All of the other tables referenced in the stored procedure contain 10 rows max.
Sorry the execution plan is a little blurry, couldn't find a way to make it more clear.
I hope this is enough information.
Thanks
June 12, 2009 at 3:21 am
WOW Great post.
Most people don't take the time to give as much info as you have thanks for that.
For the execution plan, could you post the actual plan as a zip file.
Check the bottom of this post on how to do that.
I'll start looking at the code while I wait for the plan:
http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
From what I can see, there are a few Key lookups so it looks like you covering indexes don't cover everything. (Look ups are normally very expensive)
I also see that this is a "catch -all" query. you might be interested in reading this article as well.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
When I get the actual plan I can give you index advice.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 3:29 am
Thanks Christopher,
Execution plan is attached.
Regards,
June 12, 2009 at 3:34 am
What type of field is ImageUrl?
I think this should be added to your Property image covering index that should get rid of the Key Loopup for that table.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 3:41 am
ImageURL just stores the file name of the image that is contained in the file system. It doesn't store any binary data.
I've modified the index to include: PropertyID, ImageURL, IsPrimary but execution time is still > 40 seconds but as you rightly pointed out, that "Key Lookup" is no longer there. I still get one on Properties though. Should I have just 1 index that covers all of the columns retrieved in the query?
I've attached the new execution plan after adding the column to the index.
June 12, 2009 at 3:42 am
Ok the other thing is are your stats up to date?
I've noticed most of your estimated vs actual row counts vary largely, I'm not if this is stats out of date issue of if it's cause of the "catch-all" query type!?!
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 3:46 am
It's an option for the Property index I'm just worried about the impact of creating a LARGE index that is so wide just for this query...
Might be worth trying to add the output columns as an include into your NCI.
but not 100% sure that is the correct way to go.
Would you be willing to make this query dynamic?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 3:46 am
I'm just reading that article you posted. For me, dynamic SQL has always been the root of all evil, however if it improves this sort of procedure I may re-write the query using it.
June 12, 2009 at 3:53 am
In addition to Gail's page have a quick look at my blog http://sqlandthelike.blogspot.com/2009/06/catch-all-queries-and-indexing.html
June 12, 2009 at 3:55 am
could you also confirm that the stats are up to date?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 3:59 am
I've just updated the statistics, and the query time is now down to 26 seconds.
June 12, 2009 at 4:02 am
Paul8112 (6/12/2009)
I've just updated the statistics, and the query time is now down to 26 seconds.
nice one.
Ok could we get a new plan please.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 4:05 am
Attached...
June 12, 2009 at 4:09 am
Thanks.
Ok so now it looks like we down to the Properties table. I did notice another lookup popout there, I'm sure you know how to remove that now, but it may not be worth adding an index just for that as the cost seems pretty small at this point.
So give us a shout when you have tried the dynamic or one of the other alternatives to "catch-all" queries.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 4:10 am
Better , but your non clustered index is not being used (probably due to the 'catch-all query').
Try a query with hardcoded values to see what the 'optimal' search should be
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply