DECLARE @strSQL varchar(8000)

SELECT @strSQL = 'SELECT ...
FROM	tblUserDetails 
WHERE tblUserDetails.active = ''1'''

IF @GenderID IS NOT NULL
    SELECT @strSQL = @strSQL + ' AND GenderID = @GenderID'

IF @GenderID IS NOT NULL
    SELECT @strSQL = @strSQL + ' AND NameOnline like ''%'' + @NameOnline + ''%'' '

...

EXEC sp_excuteSQL @strSQL, @GenderID tinyint, @NameOnline varchar(15), ...

IF @NameOnline IS NOT NULL
   DELETE #Paging
   WHERE NameOnline NOT like '%' + @NameOnline + '%'

		(
			select count(userID) as tblthumb_count 
			from tblthumbs where status = 1 AND 
			tblUserDetails.userid = tblthumbs.userid
		) as thumb_count, 
		
		(select counterID FROM tblExtraPhotos where photoID = '0' AND tblUserDetails.userid = tblExtraPhotos.userid) as mainPhotoID, 

		case 
			when exists (
				select userID 
				from tblvideo 
				where tblUserDetails.userid = tblvideo.userid AND active ='1' ) 
			then 'Y' Else 'N' end as vidExist, 
		case 
			when exists (
					select userID from tblExtraPhotos where tblUserDetails.userid = tblExtraPhotos.userID AND active ='1' AND photoID ='99'
					--select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' 
				) then 'Y' Else 'N' end as SuperPhoto

quote:

---

Originally posted by Kristen

"What do you think of the general idea of having different SPROCS"

As an off-hand-remark I'd say you have too many parameters to pull that trick.

Kristen

---

quote:

---

Originally posted by hgorijal

try this, ..

1. Remove these Subqueries in SELECT clause and use regular JOINS instead.

		(
			select count(userID) as tblthumb_count 
			from tblthumbs where status = 1 AND 
			tblUserDetails.userid = tblthumbs.userid
		) as thumb_count, 
		
		(select counterID FROM tblExtraPhotos where photoID = '0' AND tblUserDetails.userid = tblExtraPhotos.userid) as mainPhotoID, 

		case 
			when exists (
				select userID 
				from tblvideo 
				where tblUserDetails.userid = tblvideo.userid AND active ='1' ) 
			then 'Y' Else 'N' end as vidExist, 
		case 
			when exists (
					select userID from tblExtraPhotos where tblUserDetails.userid = tblExtraPhotos.userID AND active ='1' AND photoID ='99'
					--select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' 
				) then 'Y' Else 'N' end as SuperPhoto

2. Split the Query into multiple queries...
[code]
(
@qsVideo != 'on' OR
EXISTS (
select userID from
tblvideo where userId = tblUserDetails.userID and tblvideo.active ='1')
) and
(
@qsOnline != 'on' OR
EXISTS (
select userID from
tblActive_Users where userId = tblUserDetails.userID)
) and
(
@qsSuperPhoto != 'on' OR
EXISTS (
select userID from
tblExtraPhotos where userId = tblUserDetails.userID AND photoID ='99')
)
[code]

Take the above code out of this query. Then individually check the variables (@qsVideo, @qsOnline...) and delete from #Paging if required.


Also, seems to me that you are using this proc to browse, calling it one time for each page. If performance is really the highest priority, you might want to look into incorporating caching techniques, so that the first time you run, you load the data into a permanent or permanent-temporary(##) table and you read this table for the subsequent pages, instead of rerunning the entire proc.

hope it helps...



Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

---