weve followed all the known methods to improve performance; and now are
ready to start denormalizing the tables. Can anyone see anything obvious
that i can do to improve the perofmrance of this sp under heavy load?


CREATE procedure sprListingSearch(
@Culture nchar(5)
,@MinRentPrice int
,@MaxRentPrice int
,@RentUnit int
,@MinSalePrice int
,@MaxSalePrice int
,@SaleUnit int
,@MinBath int
,@MaxBath int
,@MinBed int
,@MaxBed int
,@CompanyName nvarchar(100)
,@Address nvarchar(100)
,@ProvinceID int
,@City nvarchar(1024)
,@Community nvarchar(1024)
,@PostalCode nvarchar(7)
,@FullName nvarchar(100)
,@AreaId nvarchar(512)
,@Features nvarchar(1000)
,@AllFeatures bit
,@CobrandID int
,@PropertyType nvarchar(1000)
,@MinDateBuilt int
,@MaxDateBuilt int
,@AgeClassID int
,@NumberOfDays int
,@ReferenceNumber nVarchar(25)
,@LinkbackID varchar(512)
,@LinkbackType INT
,@BusinessTypeID varchar(512)
,@BuildingStyleID varchar(512)
,@MinFloorSpace INT
,@MaxFloorSpace INT
,@MinLotSize INT
,@MaxLotSize INT
,@LotSizeUnit INT
,@BoardID varchar(512)
,@ListingType varchar(512)
,@ListingStatus varchar(512)
,@PermitShowPublicICX INT
,@ApplicationID INT
,@OrderDirection CHAR(1)
,@CurrentPage int
,@RecordsPerPage int
,@MaxRecords int
,@OrderBy INT
,@RecordsFound int out
,@Debug BIT = 0
)AS

SET NOCOUNT ON

DECLARE @CultureID INT
EXEC sprGetCultureID @Culture, @CultureID OUT
IF @ApplicationID = 0 BEGIN
EXEC sprGetApplicationID @CobrandID, @ApplicationID OUT
END


IF @CobrandID = 1 AND @LinkbackID = '' BEGIN
exec sprlistingSearchFast @Culture, @MinRentPrice , @MaxRentPrice ,
@RentUnit , @MinSalePrice , @MaxSalePrice , @SaleUnit , @MinBath , @MaxBath
, @MinBed , @MaxBed , @CompanyName, @Address , @ProvinceID , @City ,
@Community , @PostalCode , @FullName , @AreaId , @Features , @AllFeatures,
@CobrandID , @PropertyType , @MinDateBuilt , @MaxDateBuilt , @AgeClassID ,
@NumberOfDays , @ReferenceNumber , @LinkbackID, @LinkbackType ,
@BusinessTypeID, @BuildingStyleID, @MinFloorSpace , @MaxFloorSpace ,
@MinLotSize , @MaxLotSize , @LotSizeUnit , @BoardID, @ListingType,
@ListingStatus, @PermitShowPublicICX , @ApplicationID , @OrderDirection,
@CurrentPage , @RecordsPerPage , @MaxRecords , @OrderBy , @RecordsFound
OUT, @Debug
RETURN
END

DECLARE @SQL AS nvarchar(4000),
@SQLSelect AS nvarchar(4000),
@SQLWhere AS nvarchar(4000),
@SQLFrom AS nVarChar(4000),
@SQLOrderBy as nvarchar(4000),
@ReqAddress AS BIT,
@ReqLand AS BIT,
@ReqBuilding AS BIT,
@ReqBuildingAge AS BIT,
@ReqPropertyFeatures AS BIT,
@ReqListingIndividual AS BIT,
@ReqAreaSearch AS BIT,
@ReqPropertyTypeSearch AS BIT,
@ReqIndividual AS BIT,
@ReqOrganization AS BIT,
@ReqPropertyType AS BIT,
@cvs varchar(512),
@pos INT,
@val2 char(50),
@val char(1),
@IsFirst BIT

SET @ReqLand = 0
SET @ReqAddress = 0
SET @ReqBuilding = 0
SET @ReqBuildingAge = 0
SET @ReqPropertyFeatures = 0
SET @ReqListingIndividual = 0
SET @ReqIndividual = 0
SET @ReqOrganization = 0
SET @ReqAreaSearch = 0
SET @ReqPropertyTypeSearch = 0
SET @ReqPropertyType = 0

SET @SQLWhere = ' WHERE 1 = 1'

IF @PermitShowPublicICX <> -1 BEGIN
SET @SQLWhere = @SQLWhere + ' AND L.PermitShowPublicICX =
@PermitShowPublicICX'
END

IF @Address <> '' BEGIN
SET @ReqAddress = 1
SET @SQLWhere = @SQLWhere + ' AND (L.permitShowAddress = 1 AND
A.AddressLine1 LIKE ''%'' + @Address + ''%'' OR A.AddressLine2 LIKE ''%'' +
@Address + ''%'' OR A.AddressLine3 LIKE ''%'' + @Address + ''%'')'
END
IF @ProvinceID <> 0 BEGIN
SET @ReqAddress = 1
SET @SQLWhere = @SQLWhere + ' AND A.ProvinceID = @ProvinceID'
END
IF @PostalCode <> '' BEGIN
SET @ReqAddress = 1
SET @SQLWhere = @SQLWhere + ' AND A.postalCode = @PostalCode'
END

DECLARE @PriceAndRent as BIT
IF @MinSalePrice >= 0 AND @MinRentPrice >= 0 BEGIN
SET @PriceAndRent = 1
END ELSE BEGIN
SET @PriceAndRent = 0
END

IF @MinSalePrice >= 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND '
IF @PriceAndRent = 1 SET @SQLWhere = @SQLWhere + ' ('
SET @SQLWhere = @SQLWhere + '(P.Price >= @MinSalePrice'
IF @MaxSalePrice <> 0 SET @SQLWhere = @SQLWhere + ' AND P.Price <=
@MaxSalePrice)'
ELSE SET @SQLWhere = @SQLWhere + ')'
--END ELSE BEGIN
-- SET @SQLWhere = @SQLWhere + ' AND P.Price IS NULL'
END


IF @minRentPrice >= 0 BEGIN
IF @PriceAndRent = 1 BEGIN
SET @SQLWhere = @SQLWhere + ' OR '
END ELSE BEGIN
SET @SQLWhere = @SQLWhere + ' AND '
END
SET @SQLWhere = @SQLWhere + '(P.LeaseRent >= @minRentPrice'
IF @MaxRentPrice <> 0 SET @SQLWhere = @SQLWhere + ' AND P.LeaseRent <=
@MaxRentPrice)'
ELSE SET @SQLWhere = @SQLWhere + ')'
IF @PriceAndRent = 1 SET @SQLWhere = @SQLWhere + ')'
--END ELSE BEGIN
-- SET @SQLWhere = @SQLWhere + ' AND P.LeaseRent IS NULL'
END


IF @MinBed <> 0 BEGIN
SET @ReqLand = 1
SET @ReqBuilding = 1
SET @SQLWhere = @SQLWhere + ' AND B.BedroomsAboveGroundTotal >= @MinBed'
END
IF @MaxBed <> 0 BEGIN
SET @ReqLand = 1
SET @ReqBuilding = 1
SET @SQLWhere = @SQLWhere + ' AND B.BedroomsAboveGroundTotal <= @MaxBed'
END
IF @MinBath <> 0 BEGIN
SET @ReqLand = 1
SET @ReqBuilding = 1
SET @SQLWhere = @SQLWhere + ' AND (B.BathroomsAboveGroundTotal +
(IsNull(B.HalfBathsTotal,0) * 0.5)) >= @MinBath'
END
IF @MaxBath <> 0 BEGIN
SET @ReqLand = 1
SET @ReqBuilding = 1
SET @SQLWhere = @SQLWhere + ' AND (B.BathroomsAboveGroundTotal +
(isNull(B.HalfBathsTotal,0) * 0.5)) <= @MaxBath'
END
IF @CobrandID <> 0 AND @CobrandID <> 1 AND @CobrandID <> 2 BEGIN
SET @ReqListingIndividual = 1
SET @SQLWhere = @SQLWhere + ' AND LI.IndividualID IN (SELECT ID from
cobrand_id_join where cobrandID =@COBRANDID AND type = ''I'' UNION ALL
SELECT INDIVIDUALID AS ID from individual_organization WHERE ORGANIZATIONID
IN (SELECT id from cobrand_id_join where cobrandid = @COBRANDID AND type =
''O''))'
END
IF @ReferenceNumber <> '' BEGIN
SET @SQLWhere = @SQLWhere + ' AND L.ReferenceNumber = @ReferenceNumber'
END
IF @NumberOfDays <> 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND L.InsertedDate >= (getdate() -
@NumberOfDays)'
END
IF @MinLotSize <> 0 OR @MaxLotSize <> 0 BEGIN
SET @ReqLand = 1
SET @SQLWhere = @SQLWhere + ' AND NOT LA.sizeTotal IS NULL'
END
IF @MinLotSize <> 0 BEGIN
SET @ReqLand = 1
SET @SQLWhere = @SQLWhere + ' AND dbo.MeasureConvert(LA.sizeTotal,
LA.SizeTotalMeasureUnitID, @LotSizeUnit) >= @MinLotSize'
END
IF @MaxLotSize <> 0 BEGIN
SET @SQLWhere = @SQLWhere + ' AND dbo.MeasureConvert(LA.sizeTotal,
LA.SizeTotalMeasureUnitID, @LotSizeUnit) <= @MaxLotSize'
END
IF @MinFloorSpace <> 0 OR @MaxFloorSpace <> 0 BEGIN
SET @ReqLand = 1
SET @ReqBuilding = 1

Hello,

Thanks for your post. If I understand correctly that you want more
information to help you improve the performance of this stored procedure in
SQL Server. I have found the following information for your reference.

110352 INF: Optimizing Microsoft SQL Server Performance
http://support.microsoft.com/?id=110352

243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589

For more information regarding this issue, please refer to the following
articles on SQL Server Books Online.

Topic: "Optimizing Application Performance Using Efficient Data Retrieval"
Topic: "Query Tuning"
Topic: "Query Tuning Recommendations"
Topic: "Transact-SQL Tips"

Thanks for posting in community.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Just a couple of clarifications:

[quoted text, click to view]
I think Erland means "indexes on these search columns are NOT likely to
help you."

[quoted text, click to view]
And you can look at the article on Arrays in SQL on Erland's site for
some possibilities: www.sommarskog.se.

SK

sviau (sviau@crea.ca) writes:
[quoted text, click to view]

I'm afraid that posting a long stored procedure and not providing any
information about the underlying tables is not likely to give you a
very precise answer. As a minimum, one would need the CREATE TABLE
statements for the involved tables, the indexes, information about sizes of
the tables, and finally which search criterias that are always/usually
provided.

But it may prove that even with that information, that the task is too
complex for some quick newsgroup help, and in the end you may have to find
someone with available consulting hours.

Nevertheless, a few observations that I made.

There are several places where you have "LIKE '%' + @expr + '%'". Be
aware that indexes on these search columns are likely to help you. Of
course, if you have business reasons that calls for these searches,
then you cannot just change it. I don't know, but it is possible that
full-text search could help you here.

I also got the impression that you might be composing lists which you
then use in IN expression (but I am not sure). Beware of that for something
like "SELECT * FROM tbl IN (1, 2, ....)" the compilation grows radically
if the list becomes very long (say over 200 elements). It may be better
put that data in a table and join with it.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

ok, I've had a chance to look at this now too & have some thoughts to add..

I think the performance bottleneck is *likely* in either of these two
places:
(a) Insufficient indexes to support the dynamic where clause on the select
that populates the #List table or wildcards in search params rendering
indexes useless.
or
(b) Overhead from the massive number of tables in the final join.

The first thing I'd be doing is confirming which section of the sp is
actually performing poorly. You could simply load the sp into the QA &
insert a few print getdate() statements interleaved between key code
branches to see which is performing poorly. If you posted back your actual
findings from this, it might focus our effort to help you - if we knew
precisely where in that massive stored proc things aren't performing. You
should probably do this in a dev environment, but you might even get away
with prod given that this stored proc only searches - as long as the .aspx
could handle the print statements.

Most likely, either
(a) the #List population is slow due to insufficient indexing on the source
table/s
or
(b) the massive join operations at the end are dragging the thing to a
hault.

I'll address (b) first. If this is the case, you could break the massive
join down into smaller statements, perhaps with work tables. That many
tables joined into a single select is just asking for trouble. Always
question why you'd put more than 7 tables into a join & break it down if
possible - especially in big trawling, search style stored procs like this.

If you're proble is (a) you might find things more difficult. The problem
here will be that the statements are dynamic & therefore the best you will
probably be able to do will be add various indexes, hoping to service MOST
queries. As Erland's already pointed out, there are wildcard searches in
your dynamic query design & indexes can't solve these (in any RDBMS). At
least, given that dynamic select only returns one column, you might find
that indexing the table heavily might work out as you'll have many narrow
indexes (much cheaper than wide indexes). If you do index heavily,
performance might slow down - but in a system like yours, you might actually
value searching speed over listing speed..

Hopefully there's something in this for you.

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]