I'm having a problem with an update operation in a stored procedure. It
runs so slowly that it is unusable, unless I comment a part out in which
case it is very fast. However, I need the whole thing :). I have a
table of email addresses of people who want to get invited to parties.
Each row contains information like email address, city, state, country,
and preferences for what types of events are of interest.
The primary key is an EMAILID, and has a unique constraint on the email
field. The stored procedure receives the field data as arguments, and
inserts the record if the email address passed is not in the database.
This works perfectly. However, if the stored procedure is called for an
email address that already exists, it updates the existing row instead
of doing an insert. This way I can build a web page that lets people
modify their preferences, opt in and out of the list and so on.
If I am doing an update, the stored procedure runs SUPER SLOW (and the
page times out) unless I comment out the part of the update statement
for city, state, country and zipcode. However, I really need to be able
to update this!
My database has 29 million rows.
Thank you for telling me anything about how I can speed up this update!
Here is the SQL statement to run the stored procedure:
declare @now datetime;set @now = GetUTCDate();
EXEC usp_EMAIL_Subscribe @Email='dberman@sen.us', @OptOutDate=@now,
@Opt_GenInterest=1, @Opt_DatePeople=0, @Opt_NewFriends=1,
@Opt_OldFriends=0, @Opt_Business=1, @Opt_Couples=0, @OptOut=0,
@Opt_Events=0, @City='Boston', @State='MA', @ZCode='02215',
@Country='United States'
Here is the stored procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [usp_EMAIL_Subscribe]
(@Email [varchar](50),
@Opt_GenInterest [tinyint],
@Opt_DatePeople [tinyint],
@Opt_NewFriends [tinyint],
@Opt_OldFriends [tinyint],
@Opt_Business [tinyint],
@Opt_Couples [tinyint],
@OptOut [tinyint],
@OptOutDate datetime,
@Opt_Events [tinyint],
@City [varchar](30), @State [varchar](20), @ZCode [varchar](10),
@Country [varchar](20)
)
AS
BEGIN
declare @EmailID int
set @EmailID = NULL
-- Get the EmailID matching the provided email address
set @EmailID = (select EmailID from v_SENWEB_EMAIL_SUBSCRIBERS where
EmailAddress = @Email)
-- If the address is new, insert the address and settings. Otherwise,
UPDATE existing email profile
if @EmailID is null or @EmailID = -1
Begin
INSERT INTO v_SENWEB_Email_Subscribers
(EmailAddress, OptInDate, OptedInBy, City, StateProvinceUS, Country,
ZipCode,
GeneralInterest, MeetDate, MeetFriends, KeepInTouch, MeetContacts,
MeetOtherCouples, MeetAtEvents
)
VALUES
(@Email, GetUTCDate(), 'Subscriber', @City, @State, @Country, @ZCode,
@Opt_GenInterest, @Opt_DatePeople,
@Opt_NewFriends, @Opt_OldFriends, @Opt_Business, @Opt_Couples,
@Opt_Events
)
End
Else
BEGIN
UPDATE v_SENWEB_EMAIL_SUBSCRIBERS
SET
--City = @City,
--StateProvinceUS = @State,
--Country = @Country,
--ZipCode = @ZCode,
GeneralInterest = @Opt_GenInterest,
MeetDate = @Opt_DatePeople,
MeetFriends = @Opt_NewFriends,
KeepInTouch = @Opt_OldFriends,
MeetContacts = @Opt_Business,
MeetOtherCouples = @Opt_Couples,
MeetAtEvents = @Opt_Events,
OptedOut = @OptOut,
OptOutDate = CASE
WHEN(@OptOut = 1)
THEN @OptOutDate
WHEN(@OptOut = 0)
THEN 0
END
WHERE EmailID = @EmailID
END
return @@Error
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Finally, here is the database schema for the table courtesy of
enterprise manager:
CREATE TABLE [dbo].[EMAIL_SUBSCRIBERS] (
[EmailID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OptinDate] [smalldatetime] NULL ,
[OptedinBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[WorkPhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[HomePhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AddressLine1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AddressLine2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AddressLine3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateProvinceUS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StateProvinceOther] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SubZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[GeneralInterest] [tinyint] NULL ,
[MeetDate] [tinyint] NULL ,
[MeetFriends] [tinyint] NULL ,
[KeepInTouch] [tinyint] NULL ,
[MeetContacts] [tinyint] NULL ,
[MeetOtherCouples] [tinyint] NULL ,
[MeetAtEvents] [tinyint] NULL ,
[OptOutDate] [datetime] NULL ,
[OptedOut] [tinyint] NOT NULL ,
[WhenLastMailed] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_EMAIL_SUBSCRIBERS_ADDR] ON
[dbo].[EMAIL_SUBSCRIBERS]([EmailAddress]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
ALTER TABLE [dbo].[EMAIL_SUBSCRIBERS] WITH NOCHECK ADD
CONSTRAINT [DF_EMAIL_SUBSCRIBERS_OptedOut] DEFAULT (0) FOR [OptedOut],
CONSTRAINT [DF_EMAIL_SUBSCRIBERS_WhenLastMailed] DEFAULT (null) FOR
[WhenLastMailed],
CONSTRAINT [PK_EMAIL_SUBSCRIBERS] PRIMARY KEY NONCLUSTERED
(
[EmailID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_EMAIL_SUBSCRIBERS_WhenLastMailed] ON
[dbo].[EMAIL_SUBSCRIBERS]([WhenLastMailed] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptOutDate] ON
[dbo].[EMAIL_SUBSCRIBERS]([OptOutDate] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptInDate] ON
[dbo].[EMAIL_SUBSCRIBERS]([OptinDate] DESC ) ON [PRIMARY]
GO
CREATE INDEX [IX_EMAIL_SUBSCRIBERS_ZipCode] ON
[dbo].[EMAIL_SUBSCRIBERS]([ZipCode]) ON [PRIMARY]
GO
CREATE INDEX [IX_EMAIL_SUBSCRIBERS_STATEPROVINCEUS] ON
[dbo].[EMAIL_SUBSCRIBERS]([StateProvinceUS]) ON [PRIMARY]
GO
Meet people for friendship, contacts,
or romance using free instant messaging software! See a picture you
like? Click once for a private conversation with that person!
<a href="http://www.sen.us"><img
src="http://www.sen.us/mirror/SENLogo_62_31.jpg">
</a>
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
