I've put in a lot of work in the following stored proc only to find
that it's slower then the old project's dynamic built query(been
running since 1994, I think). Can anyone see any obvious flaw, I know
it's big, but downsizing it might take away some part that contains
some error that someone might find:) What it retrieves is apartments
based on the user's preferences. Anyways here it is, thanx for any
help:
SELECT
Member.l_MemberID as mem_MemberID,
Member.l_PinCode as mem_PinCode,
Member.s_FirstName as mem_FirstName,
Member.s_LastName as mem_LastName,
Member.s_PersNr as mem_SocSec,
Member.s_Co_Nr as mem_CareOf,
Member.s_Address as mem_Address,
Member.l_ZipCode as mem_ZipCode,
Member.s_City as mem_City,
Member.s_WorkPhone as mem_WorkPhone,
Member.s_Phone as mem_Phone,
Member.s_Cell as mem_Cell,
Member.s_Email as mem_Email,
Member.s_Notes as mem_Notes,
Member.b_Rent_Acknowledge as mem_RentAcknowledge,
estates.L_ESTATEID as est_EstateID,
estates.s_HolderName as est_HolderName,
estates.s_HolderNr as est_HolderNr,
estates.s_HolderPhone as est_HolderPhone,
isnull(estates.n_type,0) as est_Type,
estates.B_ONLYCOMPANIES as est_OnlyCompanies,
Convert(nvarchar, estates.d_rooms) as est_Rooms,
estates.n_BedRooms as est_Bedrooms,
RTRIM(estates.S_KITCHEN) as est_Kitchen,
Estates.D_Area as est_Size,
Convert(nvarchar, estates.d_area) as est_Size,
estates.D_BiArea as est_BiArea,
estates.D_LotSize as est_LotSize,
Estates.N_YearBuilt as est_YearBuilt,
Estates.N_YearRestored as est_YearRestored,
Estates.S_Area as est_Area,
Estates.S_Address as est_Address,
RTRIM(Estates.S_AddressNr) as est_AddressNr,
Estates.L_ZipCode as est_ZipCode,
Estates.S_City as est_City,
Estates.N_Floor as est_Floor,
Estates.N_TotFloors as est_TotalFloors,
isnull(Estates.B_Elevator,0) as est_Elevator,
RTRIM(Estates.S_Furnitures) as est_Furnished,
RTRIM(Estates.S_Terrace) as est_Terrace,
isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
isnull(estates.B_SHOWER,0) as est_Shower,
isnull(estates.B_BATHTUB,0) as est_BathTub,
isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
isnull(estates.B_PARABOL,0) as est_Parabol,
isnull(estates.B_TV,0) as est_TV,
isnull(estates.B_TILESTOVE,0) as est_TileStove,
isnull(estates.B_VIDEO,0) as est_Video,
isnull(estates.B_COMPUTER,0) as est_Computer,
isnull(estates.B_MICRO,0) as est_Micro,
isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
isnull(estates.B_BATHROOM,0) as est_Bathroom,
isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
isnull(estates.B_INCHEAT,0) as est_IncHeat,
isnull(estates.B_CABLETV,0) as est_CableTV,
isnull(estates.B_INCWATER,0) as est_IncWater,
isnull(estates.B_BROADBAND,0) as est_Broadband,
isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
isnull(estates.B_PHONE,0) as est_Phone,
isnull(estates.B_INCELEC,0) as est_IncElec,
isnull(estates.B_GAS,0) as est_Gas,
isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
isnull(estates.B_QUIETAREA,0) as est_QuietArea,
isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
isnull(estates.B_SEAVIEW,0) as est_SeaView,
isnull(estates.B_NOPETS,0) as est_NoPets,
isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
isnull(estates.B_NOKIDS,0) as est_NoKids,
isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyW
Days,isnull(estates.B_MAN,0) as est_Man,
isnull(estates.B_WOMAN,0) as est_Woman,
Estates.S_RentAdvance as est_RentAdvance,
Estates.L_Rent as est_Rent,
isnull(Estates.B_Permit,0) as est_Permit,
Estates.S_Landlord as est_Landlord,
Estates.S_Landl_Phone as est_LandlordPhone,
Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
Estates.S_Description as est_Description,
isnull(estates.N_STATE,0) as est_State,
isnull(Estates.B_Printed,0) as est_Printed,
isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
Estates.L_RegisteredID as est_RegisteredID,
Estates.Dat_Registered as est_DatRegistered,
Estates.L_UpdatedID as est_UpdatedID,
Estates.Dat_Updated as est_DatUpdated,
Estates.Dat_Commit_Printed as est_DatCommitPrinted,
Estates.S_TypeBekr as est_TypeCommit,
Estates.S_Comments as est_Comments,
Estates.L_AuthorizedID as est_AuthorizedID,
Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
estates.L_AgentID as est_AgentID
FROM
estates
left join multimedia on multimedia.l_estateid=estates.l_estateid and
isnull(N_INDEX,1)=1
left join member with (nolock) on estates.l_memberid=member.l_memberid
WHERE
isnull(estates.B_FIRSTHAND,0) = 0 AND
isnull(dat_show,getdate()) <= getdate() AND
(ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
10000 else datediff(dd, isnull(case when dat_from<getdate() then
getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
end)>= @.PeriodMin)) AND
((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
10000 else datediff(dd, isnull(case when dat_from<getdate() then
getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
end)<= @.PeriodMax)) AND
((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
(((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
estates.l_zipcode BETWEEN 76100 AND 76295))OR
((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
estates.l_zipcode BETWEEN 66010 AND 66899))OR
((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
estates.l_zipcode BETWEEN 55000 AND 66090 OR
estates.l_zipcode BETWEEN 66900 AND 76099 OR
estates.l_zipcode BETWEEN 76296 AND 99999))OR
((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
estates.l_zipcode BETWEEN 11744 AND 11759 OR
estates.l_zipcode BETWEEN 11600 AND 11899))OR
((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
estates.l_zipcode BETWEEN 11743 AND 11743 OR
estates.l_zipcode BETWEEN 11760 AND 11799))OR
((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
estates.l_zipcode BETWEEN 13500 AND 13699 OR
estates.l_zipcode BETWEEN 13000 AND 13099))OR
((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
estates.l_zipcode BETWEEN 11744 AND 11759 OR
estates.l_zipcode BETWEEN 11600 AND 11899))OR
((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
((@.State = -1)OR (estates.N_STATE = @.State)) AND
((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
getdate())<=@.PublDaysMax)) AND
((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
@.PrivateCompany)) AND
((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
(UPPER(@.Street)+'%')))
ORDER BY
CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
DESC,
CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL ENDtonicvodka wrote:
> Hi all!
> I've put in a lot of work in the following stored proc only to find
> that it's slower then the old project's dynamic built query(been
> running since 1994, I think). Can anyone see any obvious flaw, I know
> it's big, but downsizing it might take away some part that contains
> some error that someone might find:) What it retrieves is apartments
> based on the user's preferences. Anyways here it is, thanx for any
> help:
>
> SELECT
> Member.l_MemberID as mem_MemberID,
> Member.l_PinCode as mem_PinCode,
> Member.s_FirstName as mem_FirstName,
> Member.s_LastName as mem_LastName,
> Member.s_PersNr as mem_SocSec,
> Member.s_Co_Nr as mem_CareOf,
> Member.s_Address as mem_Address,
> Member.l_ZipCode as mem_ZipCode,
> Member.s_City as mem_City,
> Member.s_WorkPhone as mem_WorkPhone,
> Member.s_Phone as mem_Phone,
> Member.s_Cell as mem_Cell,
> Member.s_Email as mem_Email,
> Member.s_Notes as mem_Notes,
> Member.b_Rent_Acknowledge as mem_RentAcknowledge,
> estates.L_ESTATEID as est_EstateID,
> estates.s_HolderName as est_HolderName,
> estates.s_HolderNr as est_HolderNr,
> estates.s_HolderPhone as est_HolderPhone,
> isnull(estates.n_type,0) as est_Type,
> estates.B_ONLYCOMPANIES as est_OnlyCompanies,
> Convert(nvarchar, estates.d_rooms) as est_Rooms,
> estates.n_BedRooms as est_Bedrooms,
> RTRIM(estates.S_KITCHEN) as est_Kitchen,
> Estates.D_Area as est_Size,
> Convert(nvarchar, estates.d_area) as est_Size,
> estates.D_BiArea as est_BiArea,
> estates.D_LotSize as est_LotSize,
> Estates.N_YearBuilt as est_YearBuilt,
> Estates.N_YearRestored as est_YearRestored,
> Estates.S_Area as est_Area,
> Estates.S_Address as est_Address,
> RTRIM(Estates.S_AddressNr) as est_AddressNr,
> Estates.L_ZipCode as est_ZipCode,
> Estates.S_City as est_City,
> Estates.N_Floor as est_Floor,
> Estates.N_TotFloors as est_TotalFloors,
> isnull(Estates.B_Elevator,0) as est_Elevator,
> RTRIM(Estates.S_Furnitures) as est_Furnished,
> RTRIM(Estates.S_Terrace) as est_Terrace,
> isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
> isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
> isnull(estates.B_SHOWER,0) as est_Shower,
> isnull(estates.B_BATHTUB,0) as est_BathTub,
> isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
> isnull(estates.B_PARABOL,0) as est_Parabol,
> isnull(estates.B_TV,0) as est_TV,
> isnull(estates.B_TILESTOVE,0) as est_TileStove,
> isnull(estates.B_VIDEO,0) as est_Video,
> isnull(estates.B_COMPUTER,0) as est_Computer,
> isnull(estates.B_MICRO,0) as est_Micro,
> isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
> isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
> isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
> isnull(estates.B_BATHROOM,0) as est_Bathroom,
> isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
> isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
> isnull(estates.B_INCHEAT,0) as est_IncHeat,
> isnull(estates.B_CABLETV,0) as est_CableTV,
> isnull(estates.B_INCWATER,0) as est_IncWater,
> isnull(estates.B_BROADBAND,0) as est_Broadband,
> isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
> isnull(estates.B_PHONE,0) as est_Phone,
> isnull(estates.B_INCELEC,0) as est_IncElec,
> isnull(estates.B_GAS,0) as est_Gas,
> isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
> isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
> isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
> isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
> isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
> isnull(estates.B_QUIETAREA,0) as est_QuietArea,
> isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
> isnull(estates.B_SEAVIEW,0) as est_SeaView,
> isnull(estates.B_NOPETS,0) as est_NoPets,
> isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
> isnull(estates.B_NOKIDS,0) as est_NoKids,
> isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyW
Days,> isnull(estates.B_MAN,0) as est_Man,
> isnull(estates.B_WOMAN,0) as est_Woman,
> Estates.S_RentAdvance as est_RentAdvance,
> Estates.L_Rent as est_Rent,
> isnull(Estates.B_Permit,0) as est_Permit,
> Estates.S_Landlord as est_Landlord,
> Estates.S_Landl_Phone as est_LandlordPhone,
> Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
> Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
> isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
> Estates.S_Description as est_Description,
> isnull(estates.N_STATE,0) as est_State,
> isnull(Estates.B_Printed,0) as est_Printed,
> isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
> Estates.L_RegisteredID as est_RegisteredID,
> Estates.Dat_Registered as est_DatRegistered,
> Estates.L_UpdatedID as est_UpdatedID,
> Estates.Dat_Updated as est_DatUpdated,
> Estates.Dat_Commit_Printed as est_DatCommitPrinted,
> Estates.S_TypeBekr as est_TypeCommit,
> Estates.S_Comments as est_Comments,
> Estates.L_AuthorizedID as est_AuthorizedID,
> Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
> estates.L_AgentID as est_AgentID
> FROM
> estates
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
> isnull(N_INDEX,1)=1
> left join member with (nolock) on estates.l_memberid=member.l_memberid
> WHERE
> isnull(estates.B_FIRSTHAND,0) = 0 AND
> isnull(dat_show,getdate()) <= getdate() AND
> (ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
> ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
> ((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
> ((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
> ((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
> ((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
> ((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
> ((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
> ((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
> 10000 else datediff(dd, isnull(case when dat_from<getdate() then
> getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
> end)>= @.PeriodMin)) AND
> ((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
> 10000 else datediff(dd, isnull(case when dat_from<getdate() then
> getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
> end)<= @.PeriodMax)) AND
> ((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
> (((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
> estates.l_zipcode BETWEEN 76100 AND 76295))OR
> ((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
> estates.l_zipcode BETWEEN 66010 AND 66899))OR
> ((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
> ((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
> estates.l_zipcode BETWEEN 55000 AND 66090 OR
> estates.l_zipcode BETWEEN 66900 AND 76099 OR
> estates.l_zipcode BETWEEN 76296 AND 99999))OR
> ((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
> estates.l_zipcode BETWEEN 11743 AND 11743 OR
> estates.l_zipcode BETWEEN 11760 AND 11799))OR
> ((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
> estates.l_zipcode BETWEEN 13500 AND 13699 OR
> estates.l_zipcode BETWEEN 13000 AND 13099))OR
> ((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
> ((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
> ((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
> ((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
> ((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
> ((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
> ((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
> ((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
> ((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
> ((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
> ((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
> ((@.State = -1)OR (estates.N_STATE = @.State)) AND
> ((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
> ((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
> getdate())<=@.PublDaysMax)) AND
> ((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
> @.PrivateCompany)) AND
> ((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
> ((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
> (UPPER(@.Street)+'%')))
> ORDER BY
> CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
> DESC,
> CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
> CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
> CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL END
Here are some suggestions.
1. Get rid of all the ORs. For example if you set a default value for
@.roomsmax of 10000 instead of -1 you should be able to just D_ROOMS
BETWEEN @.roomsmin AND @.roomsmax. Same for the other variables.
2. Create a table of zipcodes to join with instead of using CASE
expressions.
3. Pass in date From and To ranges instead of doing the date arithmetic
in the query. You are presently forcing those DATEDIFFs to be performed
for every row.
4. Make some columns non-nullable to eliminate the ISNULLs.
The basic idea is to get expressions in your WHERE clause that are
sargable - that means columns being compared to constants or to other
columns without complex expressions on the columns themselves. Once
you've achieved that you can take best advantage of any indexes you
create.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||Dynamic SQL may be faster for something like this, despite
all efforts, but it's worth doing a bit more to see. You need
to balance the security and other risks against the performance.
If you want to improve the performance of the non-dynamic
query, below is another suggestion beyond what David offered.
(You should first look at the query plans and profile
both compilation and execution time to see where the
problems are, then focus on the bottlenecks.)
Precalculate what you can when the data is entered in the first
place. The big one I see is zip code area. Every property
is in some zip code area, so why not store that value in a table?
It would be much better to use estates.l_ziparea = @.ZipArea
than the big mess you have, even if you have to maintain a ziparea
column in a separate table and join to it.
Steve Kass
Drew University
tonicvodka wrote:
>Hi all!
>I've put in a lot of work in the following stored proc only to find
>that it's slower then the old project's dynamic built query(been
>running since 1994, I think). Can anyone see any obvious flaw, I know
>it's big, but downsizing it might take away some part that contains
>some error that someone might find:) What it retrieves is apartments
>based on the user's preferences. Anyways here it is, thanx for any
>help:
>
>SELECT
> Member.l_MemberID as mem_MemberID,
> Member.l_PinCode as mem_PinCode,
> Member.s_FirstName as mem_FirstName,
> Member.s_LastName as mem_LastName,
> Member.s_PersNr as mem_SocSec,
> Member.s_Co_Nr as mem_CareOf,
> Member.s_Address as mem_Address,
> Member.l_ZipCode as mem_ZipCode,
> Member.s_City as mem_City,
> Member.s_WorkPhone as mem_WorkPhone,
> Member.s_Phone as mem_Phone,
> Member.s_Cell as mem_Cell,
> Member.s_Email as mem_Email,
> Member.s_Notes as mem_Notes,
> Member.b_Rent_Acknowledge as mem_RentAcknowledge,
> estates.L_ESTATEID as est_EstateID,
> estates.s_HolderName as est_HolderName,
> estates.s_HolderNr as est_HolderNr,
> estates.s_HolderPhone as est_HolderPhone,
> isnull(estates.n_type,0) as est_Type,
> estates.B_ONLYCOMPANIES as est_OnlyCompanies,
> Convert(nvarchar, estates.d_rooms) as est_Rooms,
> estates.n_BedRooms as est_Bedrooms,
> RTRIM(estates.S_KITCHEN) as est_Kitchen,
> Estates.D_Area as est_Size,
> Convert(nvarchar, estates.d_area) as est_Size,
> estates.D_BiArea as est_BiArea,
> estates.D_LotSize as est_LotSize,
> Estates.N_YearBuilt as est_YearBuilt,
> Estates.N_YearRestored as est_YearRestored,
> Estates.S_Area as est_Area,
> Estates.S_Address as est_Address,
> RTRIM(Estates.S_AddressNr) as est_AddressNr,
> Estates.L_ZipCode as est_ZipCode,
> Estates.S_City as est_City,
> Estates.N_Floor as est_Floor,
> Estates.N_TotFloors as est_TotalFloors,
> isnull(Estates.B_Elevator,0) as est_Elevator,
> RTRIM(Estates.S_Furnitures) as est_Furnished,
> RTRIM(Estates.S_Terrace) as est_Terrace,
> isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
> isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
> isnull(estates.B_SHOWER,0) as est_Shower,
> isnull(estates.B_BATHTUB,0) as est_BathTub,
> isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
> isnull(estates.B_PARABOL,0) as est_Parabol,
> isnull(estates.B_TV,0) as est_TV,
> isnull(estates.B_TILESTOVE,0) as est_TileStove,
> isnull(estates.B_VIDEO,0) as est_Video,
> isnull(estates.B_COMPUTER,0) as est_Computer,
> isnull(estates.B_MICRO,0) as est_Micro,
> isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
> isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
> isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
> isnull(estates.B_BATHROOM,0) as est_Bathroom,
> isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
> isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
> isnull(estates.B_INCHEAT,0) as est_IncHeat,
> isnull(estates.B_CABLETV,0) as est_CableTV,
> isnull(estates.B_INCWATER,0) as est_IncWater,
> isnull(estates.B_BROADBAND,0) as est_Broadband,
> isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
> isnull(estates.B_PHONE,0) as est_Phone,
> isnull(estates.B_INCELEC,0) as est_IncElec,
> isnull(estates.B_GAS,0) as est_Gas,
> isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
> isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
> isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
> isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
> isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
> isnull(estates.B_QUIETAREA,0) as est_QuietArea,
> isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
> isnull(estates.B_SEAVIEW,0) as est_SeaView,
> isnull(estates.B_NOPETS,0) as est_NoPets,
> isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
> isnull(estates.B_NOKIDS,0) as est_NoKids,
> isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyW
Days,> isnull(estates.B_MAN,0) as est_Man,
> isnull(estates.B_WOMAN,0) as est_Woman,
> Estates.S_RentAdvance as est_RentAdvance,
> Estates.L_Rent as est_Rent,
> isnull(Estates.B_Permit,0) as est_Permit,
> Estates.S_Landlord as est_Landlord,
> Estates.S_Landl_Phone as est_LandlordPhone,
> Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
> Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
> isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
> Estates.S_Description as est_Description,
> isnull(estates.N_STATE,0) as est_State,
> isnull(Estates.B_Printed,0) as est_Printed,
> isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
> Estates.L_RegisteredID as est_RegisteredID,
> Estates.Dat_Registered as est_DatRegistered,
> Estates.L_UpdatedID as est_UpdatedID,
> Estates.Dat_Updated as est_DatUpdated,
> Estates.Dat_Commit_Printed as est_DatCommitPrinted,
> Estates.S_TypeBekr as est_TypeCommit,
> Estates.S_Comments as est_Comments,
> Estates.L_AuthorizedID as est_AuthorizedID,
> Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
> estates.L_AgentID as est_AgentID
>FROM
> estates
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
>isnull(N_INDEX,1)=1
> left join member with (nolock) on estates.l_memberid=member.l_memberid
>WHERE
> isnull(estates.B_FIRSTHAND,0) = 0 AND
> isnull(dat_show,getdate()) <= getdate() AND
> (ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
> ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
> ((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
> ((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
> ((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
> ((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
> ((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
> ((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
> ((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
>10000 else datediff(dd, isnull(case when dat_from<getdate() then
>getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
>end)>= @.PeriodMin)) AND
> ((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
>10000 else datediff(dd, isnull(case when dat_from<getdate() then
>getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
>end)<= @.PeriodMax)) AND
> ((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
> (((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
> estates.l_zipcode BETWEEN 76100 AND 76295))OR
> ((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
> estates.l_zipcode BETWEEN 66010 AND 66899))OR
> ((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
> ((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
> estates.l_zipcode BETWEEN 55000 AND 66090 OR
> estates.l_zipcode BETWEEN 66900 AND 76099 OR
> estates.l_zipcode BETWEEN 76296 AND 99999))OR
> ((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
> estates.l_zipcode BETWEEN 11743 AND 11743 OR
> estates.l_zipcode BETWEEN 11760 AND 11799))OR
> ((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
> estates.l_zipcode BETWEEN 13500 AND 13699 OR
> estates.l_zipcode BETWEEN 13000 AND 13099))OR
> ((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
> ((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
> ((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
> ((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
> ((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
> ((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
> ((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
> ((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
> ((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
> ((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
> ((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
> ((@.State = -1)OR (estates.N_STATE = @.State)) AND
> ((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
> ((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
>getdate())<=@.PublDaysMax)) AND
> ((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
>@.PrivateCompany)) AND
> ((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
> ((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
>(UPPER(@.Street)+'%')))
>ORDER BY
> CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
>DESC,
> CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
> CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
> CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL END
>
>|||As an example, all the zip code logic could be replaced with something like:
inner join ZipCodeAreas on ESTATES.L_ZIPCODE = ZipCodeAreas.zipcode
and ZipCodeAreas.ZipArea = @.ZipArea
If you use default parameters of -1 for min and 10000 (or 999999999) for
max
AND ((@.RoomsMin = -1)
OR (ESTATES.D_ROOMS >= @.RoomsMin))
AND ((@.RoomsMax = -1)
OR (ESTATES.D_ROOMS <= @.RoomsMax))
AND ((@.RentMin = -1)
OR (ESTATES.L_RENT >= @.RentMin))
AND ((@.RentMax = -1)
OR (ESTATES.L_RENT <= @.RentMax))
AND ((@.SizeMin = -1)
OR (ESTATES.D_AREA >= @.SizeMin))
AND ((@.SizeMax = -1)
OR (ESTATES.D_AREA <= @.SizeMax))
AND ((@.EstateType = -1)
OR (ESTATES.N_TYPE = @.EstateType))
can be rewritten to:
AND ESTATES.D_ROOMS >= @.RoomsMin
AND ESTATES.D_ROOMS <= @.RoomsMax
AND ESTATES.L_RENT >= @.RentMin
AND ESTATES.L_RENT <= @.RentMax
AND ESTATES.D_AREA >= @.SizeMin
AND ESTATES.D_AREA <= @.SizeMax
and if you you use date ranges
AND ((@.PeriodMin = -1)
OR ((CASE
WHEN ESTATES.DAT_UNTIL IS NULL THEN 10000
ELSE DATEDIFF(DD,ISNULL(CASE
WHEN DAT_FROM < GETDATE()
THEN GETDATE()
ELSE DAT_FROM
END,GETDATE()),ISNULL(DAT_UNTIL,'2999010
1'))
END) >= @.PeriodMin))
AND ((@.PeriodMax = -1)
OR ((CASE
WHEN ESTATES.DAT_UNTIL IS NULL THEN 10000
ELSE DATEDIFF(DD,ISNULL(CASE
WHEN DAT_FROM < GETDATE()
THEN GETDATE()
ELSE DAT_FROM
END,GETDATE()),ISNULL(DAT_UNTIL,'2999010
1'))
END) <= @.PeriodMax))
might be rewritten to:
DAT_FROM >= @.DateMin
DAT_UNTIL <= @.DateMax
Honestly, I'm not sure about the last one as I don't know exactly what date
logic you need regarding nulls and dates <= today or >= today, but you
should be able to simplify it. IF you dont allow nulls in these fields and
you use '29990101' for dat_until instead of a null it may make your life
easier.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1144164113.587997.300040@.z34g2000cwc.googlegroups.com...
> tonicvodka wrote:
> Here are some suggestions.
> 1. Get rid of all the ORs. For example if you set a default value for
> @.roomsmax of 10000 instead of -1 you should be able to just D_ROOMS
> BETWEEN @.roomsmin AND @.roomsmax. Same for the other variables.
> 2. Create a table of zipcodes to join with instead of using CASE
> expressions.
> 3. Pass in date From and To ranges instead of doing the date arithmetic
> in the query. You are presently forcing those DATEDIFFs to be performed
> for every row.
> 4. Make some columns non-nullable to eliminate the ISNULLs.
> The basic idea is to get expressions in your WHERE clause that are
> sargable - that means columns being compared to constants or to other
> columns without complex expressions on the columns themselves. Once
> you've achieved that you can take best advantage of any indexes you
> create.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks a million for all the suggestions!!!
I'm a bit unsure regarding the table ZipArea; would the best design
just be making it two columns? One for ZipArea and one for ZipCode? It
would get extremly tall then, seeing ZipArea 1's span is ZipCodes 10000
to19999 and 76100 to 76295.|||Thanks a million for all the suggestions!!!
They'll definitely come to use, I won't be able to set default
values to the null-columns right now though since it'll screw up the
running pages, but that's high priority.
I'm a bit unsure regarding the table ZipArea; would the best design
just be making it two columns? One for ZipArea and one for ZipCode? It
would get extremly tall then, being ZipArea 1's span is ZipCodes 10000
to 19999 and 76100 to 76295.
Thanks again for the help!|||tonicvodka (tonicvodka@.hotmail.com) writes:
> They'll definitely come to use, I won't be able to set default
> values to the null-columns right now though since it'll screw up the
> running pages, but that's high priority.
> I'm a bit unsure regarding the table ZipArea; would the best design
> just be making it two columns? One for ZipArea and one for ZipCode? It
> would get extremly tall then, being ZipArea 1's span is ZipCodes 10000
> to 19999 and 76100 to 76295.
Yes, I would make it a two-column table, so with ZipCode as key and
ZipArea as output. Of course, that beast would be a nightmare to maintain,
so I might consider another table for input that has ranges, and which
feeds the first table through the trigger. But I would try to avoid
that range table in the query, as it's probably not good for performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Then 2-column it is, thanks!|||Apart of removing the allow-null-columns I've implemented all of the
above suggestions except the date range and the difference is big.
Earlier response times on about 1.2 sec now lie on 0.3 sec when same
data is posted, excellent.
The date range I think is difficult to do anything about, since it's a
range the customer is requesting, e.g. I'm looking to rent an apartment
for the duration of at least 3 monthes and the most 6 monthes, while
the table holds data from and to which date the apartment is available.
Any sudden brainwave is welcome! Thanks all.|||If a customer is looking for a duration from 3 to 6 months, what is the
start of that duration?
Can you associate the duration with a specific date relative to today?
i.e. Customer one is looking for an apartment for 3 to 6 months, starting
today. If today happens to be 2006-01-01 then the customer wants the
apartment no later than today, at least until 2006-03-31 and no time after
2006-05-31. Now, your business rules will be different, but the same
general approach should work, no?
"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1144325221.351430.293750@.g10g2000cwb.googlegroups.com...
> Apart of removing the allow-null-columns I've implemented all of the
> above suggestions except the date range and the difference is big.
> Earlier response times on about 1.2 sec now lie on 0.3 sec when same
> data is posted, excellent.
> The date range I think is difficult to do anything about, since it's a
> range the customer is requesting, e.g. I'm looking to rent an apartment
> for the duration of at least 3 monthes and the most 6 monthes, while
> the table holds data from and to which date the apartment is available.
> Any sudden brainwave is welcome! Thanks all.
>
No comments:
Post a Comment