April 17, 2006 at 11:22 am
Hi,
I have this sproc for searching clients in our database (used internally by our company staff). I am constructing dynamic sql based on the input parameters. It was working fine until I included the email subscription search option. We have a list of email newsletters and search can be performed based on the newsletters users are (or not) subscribed for. Ever since I added this, the sproc is running real slow. Last week I started getting timeout expired error from this page. I had to increase the timeout value to 45 secs. Can any one help me with this sproc.
Thanks.
April 17, 2006 at 11:25 am
Here is the stored procedure.
CREATE PROCEDURE [dbo].[proc_searchusers]
@userstatus CHAR(1),-- active or inactive
@usertype CHAR(1), -- free or pay1 or pay2
@emailflagged CHAR(1),-- yes or no
@landmailflagged CHAR(1),-- yes or no
@name VARCHAR(100),-- replaced * with %
@city VARCHAR(100),-- replaced * with %
@state CHAR(2),
@email VARCHAR(100),-- replaced * with %
@subscriptionids VARCHAR(500),-- comma seperated id of all the email subscriptions selected
@subscriptioncount INT,-- number of email subscriptions selected
@subscriptionflag INT,-- search for 'subscribed for' or 'not subscribed for'
@orderby VARCHAR(50)
AS
DECLARE @sql VARCHAR(5000)
SET @sql = 'SELECT userid, name, email, city, state, CONVERT(VARCHAR, registerdate, 101) as regdate FROM users usr WHERE usr.userid IS NOT NULL'
IF @clientstatus '' SET @sql = @sql + ' AND usr.isactive = ''' + @userstatus + ''''
IF @clienttype '' SET @sql = @sql + ' AND usr.type = ''' + @usertype + ''''
IF @landmailflag '' SET @sql = @sql + ' AND usr.landmailflagged = ''' + @landmailflagged + ''''
IF @emailflag '' SET @sql = @sql + ' AND usr.emailflagged = ''' + @emailflagged + ''''
IF @name '' SET @sql = @sql + ' AND usr.name LIKE ''' + @name + ''''
IF @city '' SET @sql = @sql + ' AND usr.city LIKE ''' + @city + ''''
IF @state '' SET @sql = @sql + ' AND usr.state = ''' + @state + ''''
IF @email '' SET @sql = @sql + ' AND usr.email LIKE ''' + @email + ''''
--usersubscriptions table has two columns - userid, subscriptionid
IF @subscriptioncount > 0
BEGIN
IF @subscriptionflag = 1
SET @sql = @sql + ' and EXISTS (SELECT * FROM usersubscriptions usub WHERE usr.userid = usub.userid and usub.subscriptionid IN (' + @subscriptionids + ') GROUP BY usub.userid HAVING count(*) = ' + CONVERT(VARCHAR, @subscriptioncount) + ')'
ELSE IF @subscriptionflag = 2
SET @sql = @sql + ' and NOT EXISTS (SELECT * FROM usersubscriptions usub WHERE usr.userid = usub.userid and usub.subscriptionid IN (' + @subscriptionids + '))'
END
SET @sql = @sql + ' ORDER BY ''' + @orderby + ''''
EXEC @sql
GO
April 17, 2006 at 1:53 pm
Can you not accomplish this with an inner join, rather than the exists statement....maybe against a pre-aggregated table of subscription counts?
April 17, 2006 at 2:08 pm
Thanks for the tip. I will try it out.
April 17, 2006 at 3:04 pm
Arghhh...Can any one tell me what am I doing wrong. Shouldn't both the queries return the same result?
SELECT userid, name, email
FROM users usr
WHERE NOT EXISTS (SELECT * FROM usersubscriptions usub WHERE usr.userid = usub.userid AND usub.subscriptionid IN (1254, 1167))
SELECT userid, name, email
FROM users usr INNER JOIN usersubscriptions usub ON usr.userid = usub.userid
WHERE usub.subscriptionid NOT IN (1254, 1167)
April 18, 2006 at 1:23 pm
they wont be the same if all users dont have subscriptions. doing a left join should be the same i think.
April 18, 2006 at 1:29 pm
Try this:
SELECT userid, name, email
FROM users usr
LEFT OUTER JOIN usersubscriptions usub ON usr.userid = usub.userid
and
usub.subscriptionid IN (1254, 1167)
where usub.subscriptionid is null
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply