By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,866 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,866 IT Pros & Developers. It's quick & easy.

Update operation takes forever! How can I speed it up?

P: n/a
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 @Em************@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!
Jul 20 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Is the stored procedure updating a view? "v_SENWEB_EMAIL_SUBSCRIBERS"
How is the view defined? Which version of MSSQL are you running 7 or
2000?
Jul 20 '05 #2

P: n/a
Yes, the stored procedure is updating a view. Could that be the cause?
Is that like running a query to scan the whole table before updating?

The view is defined as a select statement to select each field by name
(not using *) from the table on a linked server on the lan. The table
is actually on a different server so its like: select emailaddres,
optindate, ...
from otherdb.mail.dbo.EMAIL_SUBSCRIBERS

That's all I did to create the view.
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!
Jul 20 '05 #3

P: n/a
Yes, the stored procedure is updating a view. Could that be the cause?
Is that like running a query to scan the whole table before updating?

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!
Jul 20 '05 #4

P: n/a
I'm using SQL 2000 and I just created the view with the view designer in
enterprise manager by selecting each column (not *) from a linked sql
database. The linked database is also sql 2000. If I change the stored
procedure to access the table directly instead of using a view it also
takes a loooooong time.

David


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!
Jul 20 '05 #5

P: n/a
Hi David,
Yes, the stored procedure is updating a view. Could that be the cause?
Is that like running a query to scan the whole table before updating?


The answer is...MAYBE. Your update is complicated by the fact that
you're using a view and the view is using a linked server. If the
view is straightforward as you describe and you're using MSSQL2000 --
MSSQL2000 can use underlying indexes on the base table. Older
versions can't and you probably wind up with table scans. I think
your problem is with the linked server. By default, MSSQL will
"import" the entire contents of a linked table to your server then
parse it. You should ask the DBA who owns that server if he notices
any long-running SPIDS out there. If that's the case (a big if), you
should simply move your stored procedure to the other server and call
it from your own server.
Jul 20 '05 #6

P: n/a
Bas
Maybe you're writing a spam sending tool and some victims are already
DOS-attacking you? ;)

Bas

"David Berman" <da**********@bose.com> wrote in message
news:3f*********************@news.frii.net...
Yes, the stored procedure is updating a view. Could that be the cause?
Is that like running a query to scan the whole table before updating?

The view is defined as a select statement to select each field by name
(not using *) from the table on a linked server on the lan. The table
is actually on a different server so its like: select emailaddres,
optindate, ...
from otherdb.mail.dbo.EMAIL_SUBSCRIBERS

That's all I did to create the view.
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!

Jul 20 '05 #7

P: n/a
Try looking at the execution plan with both statements. It may give
you some clues as to where the problem lies and what is taking the
time.

David Berman <da**********@bose.com> wrote in message news:<3f*********************@news.frii.net>...
I'm using SQL 2000 and I just created the view with the view designer in
enterprise manager by selecting each column (not *) from a linked sql
database. The linked database is also sql 2000. If I change the stored
procedure to access the table directly instead of using a view it also
takes a loooooong time.

David


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!

Jul 20 '05 #8

P: n/a
if its on a linked server, then the chances are good that it will
return the full dataset from teh linked server and then be doing the
'select' restriction locally. I have been caught out by this before
and it sucks. I would suggest redesigning it so that you do it a
different way - possibly putting the update procedure on the linked
server and calling that from where you are. Oh - and all that
nvarchar(255) crap? Sort it out, over any decent size table you will
appreciate having the smallest fields you need.

David Berman <da**********@bose.com> wrote in message news:<3f*********************@news.frii.net>...
I'm using SQL 2000 and I just created the view with the view designer in
enterprise manager by selecting each column (not *) from a linked sql
database. The linked database is also sql 2000. If I change the stored
procedure to access the table directly instead of using a view it also
takes a loooooong time.

David


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!

Jul 20 '05 #9

P: n/a
Ok problem solved! You guys were right. The problem was to move the
stored procedure onto the database where the table resides and have it
execute there. Now the update works instantaniously. Probably the
problem was that the data was being transfered for the select to execute
locally. Thank you very much for your help!

And rest assured, this email database is not for spam! :) It is a list
of people who have signed up to get invited to parties to meet people
and the update stored procedure is so they can change their mind to get
off the list, or move, or change their preferences as to what parties
they get invited to. http://www.sen.us/mailinglist.asp to see it in
action. Thanks again for your help.

David

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!
Jul 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.