Hi all - first post here; i usually like to figure out my issues with trial and error but this one totally has me beat!Basically ive got some SQL that is used for paging through a set of 2000 properties that compares against a whole range of features etc. If you look at the code below you will see that theres about 20lines commented out. When i run the code with these lines disabled the execution is instant. HOWEVER, when i enable those lines (1 of them or all of them it makes no difference at all) the execution time jumps to over 40seconds!!I have absolutely no idea why its doing this, or why this should happen? Ive used the same code for paging many times in the past and whilst it may not be the most effecient it has never taken so long to run.Please take a look at my code - any help would be hugely welcomed!Thanks in advance,Carl.Select top (@param5) [Name],Address,PostCode,p.AccommodationCode,AccommodationDescription,Occupancy,MinPrice,MaxPrice,Facilities,Features,AdultSleeps,ChildrenSleeps,InfantSleeps,Longitude,Latitude--,(Select top 1 url from propertyImages as pri where pri.accommodationCode=p.AccommodationCode) as url from properties as p Where RegionCode=coalesce(@param1,RegionCode,Null) and SubRegionCode=coalesce(@param2,SubRegionCode,Null) and SubSubRegionCode=coalesce(@param3,SubSubRegionCode) and AdultSleeps>=@Crit1 and ChildrenSleeps>=@Crit2 and InfantSleeps>=@Crit3 --and ( -- Feature1=ISNULL(@Feature1,Feature1) -- and Feature2=Coalesce(@Feature2,Feature2) -- and Feature3=Coalesce(@Feature3,Feature3) -- and Feature4=Coalesce(@Feature4,Feature4) -- and Feature5=Coalesce(@Feature5,Feature5) -- and Feature6=Coalesce(@Feature6,Feature6) -- and Feature7=Coalesce(@Feature7,Feature7) -- and Feature8=Coalesce(@Feature8,Feature8) -- and Feature9=Coalesce(@Feature9,Feature9) -- and Feature10=Coalesce(@Feature10,Feature10) -- and Feature11=Coalesce(@Feature11,Feature11) -- and Feature12=Coalesce(@Feature12,Feature12) -- and Feature13=Coalesce(@Feature13,Feature13) -- and Feature14=Coalesce(@Feature14,Feature14) -- and Feature15=Coalesce(@Feature15,Feature15) -- and Feature16=Coalesce(@Feature16,Feature16) -- and Feature17=Coalesce(@Feature17,Feature17) -- and Feature18=Coalesce(@Feature18,Feature18) -- and Feature19=Coalesce(@Feature19,Feature19) -- and Feature20=Coalesce(@Feature20,Feature20) --) and [id] not in( select top (@param5*(@param4-1)) id from properties as p Where RegionCode=coalesce(@param1,RegionCode,Null) and SubRegionCode=coalesce(@param2,SubRegionCode,Null) and SubSubRegionCode=coalesce(@param3,SubSubRegionCode) and AdultSleeps>=@Crit1 and ChildrenSleeps>=@Crit2 and InfantSleeps>=@Crit3 and ( Feature1=Coalesce(@Feature1,Feature1) and Feature2=Coalesce(@Feature2,Feature2) and Feature3=Coalesce(@Feature3,Feature3) and Feature4=Coalesce(@Feature4,Feature4) and Feature5=Coalesce(@Feature5,Feature5) and Feature6=Coalesce(@Feature6,Feature6) and Feature7=Coalesce(@Feature7,Feature7) and Feature8=Coalesce(@Feature8,Feature8) and Feature9=Coalesce(@Feature9,Feature9) and Feature10=Coalesce(@Feature10,Feature10) and Feature11=Coalesce(@Feature11,Feature11) and Feature12=Coalesce(@Feature12,Feature12) and Feature13=Coalesce(@Feature13,Feature13) and Feature14=Coalesce(@Feature14,Feature14) and Feature15=Coalesce(@Feature15,Feature15) and Feature16=Coalesce(@Feature16,Feature16) and Feature17=Coalesce(@Feature17,Feature17) and Feature18=Coalesce(@Feature18,Feature18) and Feature19=Coalesce(@Feature19,Feature19) and Feature20=Coalesce(@Feature20,Feature20) ) order by [name] asc ) order by [name] asc return
Coalesce dramatically increasing execution speed?
Posted - 2009-09-09 : 11:54:12
