Looking for help with a complex search query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looking for help with a complex search query

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2008-02-06 : 15:05:53

Hello,

I have a search page that uses a variety of differnet criteria which brings back users who suit that criteria. My stored procedure is built, but I need to add a few things to it.

1. The ability to search a specific date, or a span of two dates

I'm unsure of how to implement this in order to get the best performance. Also, what else would I need to post other than the SP in order to get help? Thanks

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE procedure [dbo].[c_actorSearch_select]
	(@Page tinyint,
	@RecsPerPage tinyint,
	@Rows smallint = null output ,
	@TotalPages tinyint = null output,
	@GenderID tinyint = null,
	@sexualityID tinyint = null,
	@minAge tinyint = 1,
	@maxAge tinyint = 99,
	@PostalCode varchar(7) = null,
	@City varchar(25) = null,
	@StateProvID tinyint = null,
	@CountryID tinyint = null,
	@actorType tinyint = null,
	@bodyTypeIDS varchar(100) = null,
	@hairColorIDS varchar(100) = null,
	@eyeColorIDS varchar(100) = null,
	@hairLengthIDS varchar(100) = null,
	@HeightSmall tinyint = null,
	@HeightBig tinyint = null,
	@weightSmall smallint = null,
	@weightBig smallint = null,
	@waistSmall tinyint = null,
	@waistBig tinyint = null,
	@neckSmall tinyint = null,
	@neckBig tinyint = null,
	@inseamSmall tinyint = null,
	@inseamBig tinyint = null,
	@sleeveSmall tinyint = null,
	@sleeveBig tinyint = null,
	@shoeSmall tinyint = null,
	@shoeBig tinyint = null,
	@hipsBig tinyint = null,
	@hipssmall tinyint = null,
	@dresssmall tinyint = null,
	@dressbig tinyint = null,
	@unionStatusIDS varchar(50) = null,
	@bustSmall tinyint = null,
	@bustLarge tinyint = null,
	@jacketSmall tinyint = null,
	@jacketLarge tinyint = null,
	@gloveSmall tinyint = null,
	@gloveLarge tinyint = null,
	@hatIDS varchar(50) = null,
	@wardrobeIDS varchar(200) = null,
	@AccentIDS varchar(100) = null,
	@languageIDS varchar(100) = null,
	@performanceIDS varchar(100) = null,
	@AthleticIDS varchar(200) = null,
	@challengeIDS varchar(200) = null,
	@miscIDS varchar(200) = null,
	@ethnicityIDS varchar(100) = null,
	@agencyID int = null,
	@dateStart datetime,
	@dateEnd datetime,
	@orderBy tinyint = 1
)
as
	Set Nocount on

	Create Table #Paging (
	--Declare @Paging Table (
		RowID int IDENTITY,
		userID int,
		nameFirst varchar(50),
		nameLast varchar(50),
		nameMiddle varchar(50),
		email varchar(100),
		cityID smallint,
		stateProvID smallint,
		homePhone varchar(50),
		mobilePhone varchar(50),
		alternatePhone varchar(20),
		lastLoggedIn dateTime,
		gender tinyint,
		actorType tinyint,
		birthday datetime,
		isActive bit,
		photoID tinyint,
		heightID tinyint null,
		weight varchar(5) null,
		eyeColorID tinyInt null,
		hairColorID tinyint,
		agencyID int null,
		ethnicityID tinyint,
		sexualityID tinyint
	)


	INSERT INTO #Paging (
		userID,nameFirst,nameMiddle,nameLast,email,cityID,stateProvID,homePhone,mobilePhone,alternatePhone,
		lastLoggedIn,gender,accountType,birthday,isActive,photoID, heightID,weight,eyeColorID,
		hairColorID,agencyID,ethnicityID,sexualityID)
	SELECT
		TOP 200
		cac.userID, nameFirst,nameMiddle,NameLast,email,cityID,stateProvID,homePhone,mobilePhone,alternatePhone,
		lastLoggedIn,gender,actorType,birthday,isActive,photoID, cad.heightID,weight,cad.eyeColorID,
		cad.hairColorID,agencyID,ethnicityID,sexualityID
	FROM
		tblUsers cu
	LEFT  JOIN
		 tblActorDetails
	cad ON cad.userID = cu.userID
	LEFT  JOIN
(
		SELECT DISTINCT userID,photoid FROM
		tblActorPhotos
		WHERE
		displayStatus = 1
	) 	
	 tep ON tep.userID = cu.userID
	
	WHERE
		cu.isActive = 1 and
		( @GenderID IS NULL OR gender = @GenderID) and
		( @sexualityID IS NULL or sexualityID = @sexualityID) and
		( @minAge IS NULL OR ageYoung >= @minAge ) and
		( @maxAge IS NULL OR ageOld <= @maxAge ) and
		( @weightSmall IS NULL OR weight >= @weightsmall) and
		( @weightBig IS NULL OR weight <= @weightBig) AND
		( @heightSmall IS NULL OR heightID >= @heightSmall) and
		( @heightBig IS NULL OR heightID <= @heightBig) and
		( @waistSmall IS NULL OR waistID >= @waistSmall) AND
		( @waistBig IS NULL OR waistID <= @waistBig) and
		( @hipsSmall IS NULL OR hipsID >= @hipsSmall) and
	 	( @hipsBig IS NULL OR hipsID <= @hipsBig) and
		( @shoeSmall IS NULL OR shoeID >= @shoesmall) and
		( @shoeBig IS NULL OR shoeID <= @shoeBig) and
		( @sleeveSmall IS NULL OR sleeveID >= @sleeveSmall) and
		( @sleeveBig IS NULL OR sleeveID <= @sleeveBIG) and
		( @inseamSmall IS NULL OR inseamID >= @inseamSmall) and
		( @inseamBig IS NULL OR inseamID <= @inseamBIG) and
		( @dressSmall IS NULL OR dressID >= @dressSmall) and
		( @dressBig IS NULL OR dressID <= @dressBig) and
		( @neckSmall IS NULL OR neckID >= @neckSmall) and
		( @neckBig IS NULL OR neckID <= @neckBig) and
		( @bustSmall IS NULL OR bustID <= @bustSmall) and
		( @bustLarge IS NULL OR bustID >= @bustLarge) and
		( @jacketSmall IS NULL OR jacketID <= @jacketSmall) and
		( @jacketLarge IS NULL OR jacketID >= @jacketLarge) and
		( @gloveSmall IS NULL OR gloveID <= @gloveSmall) and
		( @gloveLarge IS NULL OR gloveID >= @gloveLarge) and
		( @City IS NULL OR CityID = @city) and
		( @StateProvID IS NULL OR StateProvID = @StateProvID ) and
		( @PostalCode IS NULL OR PostalCode like '%' + @postalCode + '%') and
		( @bodyTypeIDS is null or ',' + @bodyTypeIDS + ',' like '%,' + cast(cad.bodyTypeID as varchar(3)) + ',%') and
		( @eyeColorIDS is null or ',' + @eyeColorIDS + ',' like '%,'+ cast(cad.eyeColorID as varchar(3)) + ',%') and
 		( @hairColorIDS is null or ',' + @hairColorIDS + ',' like '%,'+ cast(cad.hairColorID as varchar(3)) + ',%') and
		( @hairLengthIDS is null or ',' + @hairlengthIDS + ',' like '%,' + cast(cad.hairLengthID as varchar(3)) + ',%') and
		( @athleticIDs is null or exists(select * from tblActorAthletic aa where aa.userID = cu.userID and ',' + @athleticIDS + ',' like '%' + cast(aa.athleticID as varchar(3)) + '%')) and
		( @accentIDS is null or exists(select * from tblActorAccents tblaa where tblaa.userID = cu.userID and ',' + @accentIDS + ',' like '%' + cast(tblaa.accentID as varchar(3)) + '%')) and
		( @wardrobeIDS is null or exists(select * from tblActorWardrobe aw WHERE aw.userID = cu.userID and ',' + @wardrobeIDS + ',' like '%' + cast(aw.wardrobeID as varchar(3)) + '%')) and
		( @performanceIDS is null or exists(select * from tblActorPerformance ap WHERE ap.userID = cu.userID and ',' + @performanceIDS + ',' like '%' + cast(ap.performanceID as varchar(3)) + '%')) and
		( @languageIDS is null or exists(select * from tblActorLanguage al where al.userID = cu.userID and ',' + @languageIDS + ',' like '%' + cast(al.languageID as varchar(3)) + '%'))
		

SET @rows = @@rowcount

	Declare @FirstRow int,
		@LastRow int

	SET @FirstRow = ((@Page - 1) * @RecsPerPage) + 1
	SET @LastRow = @FirstRow + (@RecsPerPage - 1)
	SET @TotalPages = ceiling(Cast(@rows as decimal)/ @RecsPerPage )

	SELECT 
		p.RowID, p.userID,birthday, nameFirst,nameMiddle,nameLast,gender,photoID,
		h.height, weight,eyeColor,hairColor,[cag].[Name] as agencyName,city,province,ethnicity,homePhone,mobilePhone,alternatePhone,sexualityID
	FROM #Paging p
	left join dbo.cHeight h on p.heightID = h.heightID
	left join cEyeColor ec on p.eyeColorID = ec.eyeColorID
	left join cHairColor hc on p.hairColorID = hc.hairColorID
	left join tblAgency cag on p.agencyID = cag.agencyID
	left join tblCity cc on p.cityID = cc.cityID
	left join tblProvince st on p.stateprovID = st.ProvinceID
	left join cEthnicity et on p.ethnicityID = et.ethnicityID
	left join tblSexuality csx on p.sexualityID = csx.sexualityID
	WHERE rowid BETWEEN @FirstRow and @LastRow


	Drop Table #Paging

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-06 : 15:31:06

The most efficient way of searching by a date or daterange is:


where    <DateColumn> >= isNull(@fromDate, <dateColumn>)
and      <DateColumn> <= isNull(@ToDate, <dateColumn>)

You may want to try using that "isNull" technique for your other parameters to avoid all the "OR" conditions. The "OR"s may force a table scan when an index could otherwise be used. Its worth a try for comparison.

Also, if you have potential for a lot of pages, you may consider having your #paging table just hold the IDs then do all your JOINs just for the page of data you will actually return in the final SELECT statement.

Be One with the Optimizer
TG

robc
Yak Posting Veteran

60 Posts

Posted - 2008-02-07 : 13:23:54

Thanks for your help TG. Those look like some great tips.

I've rewritten the procedure as per your recommendations. I'm unsure how to use the ISNULL technique on lines like.

( @agencyIDS is null or ',' + @agencyIDS + ',' like '%,' + cast(cu.agencyID as varchar(3)) + ',%')

and

( @athleticIDs is null or exists(select athleticID from tblActorAthletic aa where aa.userID = cu.userID and ',' + @athleticIDS + ',' like '%' + cast(aa.athleticID as varchar(3)) + ',%'))

Here is the full procedure. Is there anything I could improve? I will add the date searching later.



	(@Page tinyint,
	@RecsPerPage tinyint,
	@Rows smallint = null output ,
	@TotalPages tinyint = null output,
	@GenderID tinyint = null,
	@sexualityID tinyint = null,
	@minAge tinyint = 1,
	@maxAge tinyint = 99,
	@CityID smallint = 0,
	@StateProvID tinyint = null,
	@CountryID tinyint = null,
	@actorType tinyint = null,
	@bodyTypeIDS varchar(100) = null,
	@hairColorIDS varchar(100) = null,
	@eyeColorIDS varchar(100) = null,
	@hairLengthIDS varchar(100) = null,
	@HeightSmall tinyint = null,
	@HeightBig tinyint = null,
	@weightSmall smallint = null,
	@weightBig smallint = null,
	@waistSmall tinyint = null,
	@waistBig tinyint = null,
	@neckSmall tinyint = null,
	@neckBig tinyint = null,
	@inseamSmall tinyint = null,
	@inseamBig tinyint = null,
	@sleeveSmall tinyint = null,
	@sleeveBig tinyint = null,
	@shoeSmall tinyint = null,
	@shoeBig tinyint = null,
	@hipsBig tinyint = null,
	@hipssmall tinyint = null,
	@dresssmall tinyint = null,
	@dressbig tinyint = null,
	@unionStatusIDS varchar(50) = null,
	@bustSmall tinyint = null,
	@bustLarge tinyint = null,
	@jacketSmall tinyint = null,
	@jacketLarge tinyint = null,
	@gloveSmall tinyint = null,
	@gloveLarge tinyint = null,
	@hatIDS varchar(50) = null,
	@wardrobeIDS varchar(200) = null,
	@AccentIDS varchar(100) = null,
	@languageIDS varchar(100) = null,
	@performanceIDS varchar(100) = null,
	@AthleticIDS varchar(200) = null,
	@challengeIDS varchar(200) = null,
	@miscIDS varchar(200) = null,
	@ethnicityIDS varchar(100) = null,
	@agencyIDS varchar(100) = null,
	@dateStart datetime,
	@dateEnd datetime,
	@orderBy tinyint = 1
)
as
	Set Nocount on

	Create Table #Paging (
	--Declare @Paging Table (
		RowID int IDENTITY,
		userID int
		
	)
	
    INSERT INTO #Paging (userID)
	SELECT
		TOP 200
		cu.userID
	FROM
		tblUsers cu
	LEFT  JOIN
		 tblActorDetails
	cad ON cad.userID = cu.userID
	LEFT  JOIN
(
		SELECT DISTINCT userID,photoid FROM
		tblActorPhotos
		WHERE
		displayStatus = 1
	) 	
	 tep ON tep.userID = cu.userID
	
	WHERE
		 isActive = 1 and
		 gender = isNULL(@GenderID,gender) and
		 sexualityID = isNull(@sexualityID,sexualityID) and
		 /*ageYoung >= isNull(@minAge,ageYoung) and
		 ageOld <= isNull(@maxAge,ageOld) and*/
		 weight >= isNull(@weightsmall,weight) and
		 weight <= isNull(@weightBig,weight) and
		 heightID >= isNull(@heightSmall,heightID) and
		 heightID <= isNull(@heightBig,heightID) and
		 waistID >= isNull(@waistSmall,waistID) and
		 waistID <= isNull(@waistBig,waistID) and
		 hipsID >= isNull(@hipsSmall,hipsID) and
	 	 hipsID <= isNull(@hipsBig,hipsID) and
		 shoeID >= isNull(@shoesmall,shoeID) and
		 shoeID <= isNull(@shoeBig,shoeID) and
		 sleeveID >= isNull(@sleeveSmall,sleeveID) and
		 sleeveID <= isNull(@sleeveBIG,sleeveID) and
		 inseamID >= isNull(@inseamSmall,inseamID) and
		 inseamID <= isNull(@inseamBIG,inseamID) and
		 dressID >= isNull(@dressSmall,dressID) and
		 dressID <= isNull(@dressBig,dressID) and
		 neckID >= isNull(@neckSmall,neckID) and
		 neckID <= isNull(@neckBig,neckID) and
		 bustID <= isNull(@bustSmall,bustID) and
		 bustID >= isNull(@bustLarge,bustID) and
		 jacketID <= isNull(@jacketSmall,jacketID) and
		 jacketID >= isNull(@jacketLarge,jacketID) and
		 gloveID <= isNull(@gloveSmall,gloveID) and
		 gloveID >= isNull(@gloveLarge,gloveID) and
		 cityID = isNull(@cityID,cityID) and
		 stateProvID = isNull(@StateProvID,stateProvID) and
		( @agencyIDS is null or ',' + @agencyIDS + ',' like '%,' + cast(cu.agencyID as varchar(3)) + ',%') and
		( @bodyTypeIDS is null or ',' + @bodyTypeIDS + ',' like '%,' + cast(cad.bodyTypeID as varchar(3)) + ',%') and
		( @unionStatusIDS is null or ',' + @unionStatusIDS + ',' like '%,' + cast(cad.unionID as varchar(3)) + ',%') and
		( @eyeColorIDS is null or ',' + @eyeColorIDS + ',' like '%,'+ cast(cad.eyeColorID as varchar(3)) + ',%') and
 		( @hairColorIDS is null or ',' + @hairColorIDS + ',' like '%,'+ cast(cad.hairColorID as varchar(3)) + ',%') and
		( @hairLengthIDS is null or ',' + @hairlengthIDS + ',' like '%,' + cast(cad.hairLengthID as varchar(3)) + ',%') and
		( @ethnicityIDS is null or ',' + @ethnicityIDS + ',' like '%' + cast(cad.ethnicityID as varchar(3)) + ',%') and
	    ( @athleticIDs is null or exists(select athleticID from tblActorAthletic aa where aa.userID = cu.userID and ',' + @athleticIDS + ',' like '%' + cast(aa.athleticID as varchar(3)) + ',%')) and
		( @accentIDS is null or exists(select accentID from tblActorAccents tblaa where tblaa.userID = cu.userID and ',' + @accentIDS + ',' like '%' + cast(tblaa.accentID as varchar(3)) + ',%')) and
		( @wardrobeIDS is null or exists(select wardrobeID from tblActorWardrobe aw WHERE aw.userID = cu.userID and ',' + @wardrobeIDS + ',' like '%' + cast(aw.wardrobeID as varchar(3)) + ',%')) and
		( @performanceIDS is null or exists(select performanceID from tblActorPerformance ap WHERE ap.userID = cu.userID and ',' + @performanceIDS + ',' like '%' + cast(ap.performanceID as varchar(3)) + ',%')) and
		( @languageIDS is null or exists(select languageID from tblActorLanguages al where al.userID = cu.userID and ',' + @languageIDS + ',' like '%' + cast(al.languageID as varchar(3)) + ',%'))
			
SET @rows = @@rowcount

	Declare @FirstRow int,
		@LastRow int

	SET @FirstRow = ((@Page - 1) * @RecsPerPage) + 1
	SET @LastRow = @FirstRow + (@RecsPerPage - 1)
	SET @TotalPages = ceiling(Cast(@rows as decimal)/ @RecsPerPage )

	SELECT 
		p.RowID, p.userID,birthday, nameFirst,nameMiddle,nameLast,gender,photoID,
		h.height, weight,eyeColor,hairColor,[cag].[Name] as agencyName,city,province,ethnicity,homePhone,mobilePhone,alternatePhone,sexuality
	FROM #Paging p
	left join tblUsers cus on p.userID = cus.userID
	left join tblActorDetails cau on p.userID = cau.userID
	left join cHeight h on cau.heightID = h.heightID
	left join cEyeColor ec on cau.eyeColorID = ec.eyeColorID
	left join cHairColor hc on cau.hairColorID = hc.hairColorID
	left join tblAgency cag on cus.agencyID = cag.agencyID
	left join
	(
		SELECT DISTINCT userID,photoid FROM
		tblActorPhotos
		WHERE
		displayStatus = 1
	) 	
	tep ON tep.userID = cus.userID
	left join tblCity cc on cus.cityID = cc.cityID
	left join tblProvince st on cus.stateprovID = st.ProvinceID
	left join cEthnicity et on cau.ethnicityID = et.ethnicityID
	left join tblSexuality csx on cau.sexualityID = csx.sexualityID
	WHERE rowid BETWEEN @FirstRow and @LastRow


	Drop Table #Paging

	Set Nocount off

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-07 : 13:31:55

@agencyIDS is null and
isnull(@agencyIDS,'something') are not same

is null is equivalent to =null(you're evaluating whether value is null)
whereas isnull(@agencyIDS,'something') you are telling it to replace occurances of null values in @agencyIDS to value 'something'.

You want is null to be used for above case but while in comparisons you need isnull() on left side and right side if you want to include nulls also (otherwise they will be filtered as null<> null).Remember that null is not a value stored in db but its a bit that represents Unknown value condition.

robc
Yak Posting Veteran

60 Posts

Posted - 2008-02-07 : 13:41:16

quote:
Originally posted by visakh16

@agencyIDS is null and
isnull(@agencyIDS,'something') are not same

is null is equivalent to =null(you're evaluating whether value is null)
whereas isnull(@agencyIDS,'something') you are telling it to replace occurances of null values in @agencyIDS to value 'something'.

You want is null to be used for above case but while in comparisons you need isnull() on left side and right side if you want to include nulls also (otherwise they will be filtered as null<> null).Remember that null is not a value stored in db but its a bit that represents Unknown value condition.

Ok thank you for the clarification. What I have there works fine when I test it. I was just unsure if there was a better way to write it. Because if @agencyIDS is empty I set that param value to NULL in my code.

Secondly, what are the significance of the brackets around where clauses. I removed them where I changed the syntax to ISNULL. Should I be keeping them for performance purposes?

robc
Yak Posting Veteran

60 Posts

Posted - 2008-02-08 : 18:39:34

I'm stuck on the date searching portion of this query.
I forgot to mention that I have a table which contains a list of userIDS and thier available dates. The structure is as follows;

tblActorAvailability
USERID YEAR DAY
1 2008 345
1 2008 344
1 2008 343
2 2008 45
2 2008 46

so given the suggestion of

where <DateColumn> >= isNull(@fromDate, <dateColumn>)
and <DateColumn> <= isNull(@ToDate, <dateColumn>)

i'm still unsure as how to make that happen.

rc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources