Member login - Timeout Expired error

Hi,

I get this timeout error from the member login page. It does not happenalways. Sometimes when I enter the email and password and try to login,it will take forever and then it throws a timeout error. "Timeoutexpired. The timeout period elapsed prior to completion of theoperation or the server is not responding."

I went into our server, started the sql profiler and tried to run thelogin sproc manually from the query analyzer. It took 46 secs to finishrunning the query. I checked the profiler, there was nothing much goingon, I don't think there is any resource bottleneck.

I went back to the website and I tried to login, it worked fine. Ichecked the query analyzer again, this time it took 0 secs (or fractionof a sec?) to execute the sproc.

It looks like it takes a long time to run the sproc the "first" time,from then on it only takes less than a sec to execute the sproc. How doI solve this problem?

The users table has only 50,000 users. The sproc is only a couple of lines. The user table is indexed on userid and email.

----------
CREATE PROCEDURE [dbo].[proc_userverifylogin]
@.email varchar(100), @.pass varchar(50)
AS

IF EXISTS (SELECT * FROM users WHERE email = @.email AND pass = @.pass)
BEGIN
UPDATE users SET lastlogin = GETDATE() WHERE email = @.email
SELECT userid, fname, state FROM users WHERE email = @.email
END
----------

Thanks.

new2aspdotnet:

Hi,

It looks like it takes a long time to run the sproc the "first" time, from then on it only takes less than a sec to execute the sproc.

SQL Server will generate a execution plan for the stored procedure when it runs the first time, so it will cause a long duration. Then next times SQL can reuse the exection plan so the duration becomes shorter. However a duration of 46secs is abnormal, you can try to create a clustered index on the table, in order to speed up query.

|||Thats what everyone tells me, 46 secs is really really long. Even inthe development server it takes that long. I made sure nothing else isgoing on in the server and no one is accessing it, it still takes along time.

There are two indexes on the user table - userid and email. userid isthe primary key and created as clustered index. Do you suggest I removethe clustered index on userid and create a clustered index on email?

Thanks.|||

new2aspdotnet:

Do you suggest I remove the clustered index on userid and create a clustered index on email?

No need As your query uses "pass" and "email" as searching condition, how about create a index that covers both the 2 columns? Or you can select your query in Query Analyzer, then run Index Tuning Wizard for it.

However SQL does not always uses index(es) for query even index(es) does exist on the columns(s). If the "first" execution of the stored procedure still last a long time after tuning indexes on the table, you can take a look at the generated execution plan by set this option on:

SET SHOWPLAN_TEXT ON

If the exection plan shows SQL choose "table scan" other than "index scan", it indicates SQL generates "inefficient" plan based on current statistics of the index(es). So try to update the statistics on the index:

UPDATESTATISTICS users

For more information about statistics used by query optimization, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_72r9.asp