Execution time running stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execution time running stored procedure

Author

Topic

sindrem
Starting Member

2 Posts

Posted - 2004-04-21 : 17:19:58

Hey people.
I am about to deploy a huge web-application, but ran into a strange problem today. Here comes a little description:

When a user logs in (uses a very simple stored procedure) for the first time, the execution time makes my application time out. Then I watched the query that was executing in the Profiler. When running this query in Query Analyzer, it takes about 30 seconds. This is only happening when I write correct username and password. When I write a wrong combination of usernames and passord, it executes fine.

But here comes the strange part. The next time I log in to the application with the same username/password running the same procedure, it takes less than a second!

The stored procedure takes a few input variables, and returns a GUID. All tables have about 50 usernames and password, with appropriate indexes.

Does anyone have a clue why this is happening?

Here is the execution command taken from the Profiler:
---------------------------
declare @P1 varchar(255)
set @P1=NULL
exec AccessUpdate @brukernavn = 'martin', @pass = 'helios', @leverandor_Nr_ = ' ', @Kunde_Nr = ' ', @bru_id = '4 ', @numTitlesOUT = @P1 output
select @P1
--------------------------
Here is the procedure:
--------------------------
CREATE PROCEDURE AccessUpdate
@brukernavn varchar(255),
@pass varchar(255),
@leverandor_Nr_ char(21),
@Kunde_Nr char(11) = null,
@bru_id int,
@numTitlesOUT varchar(255) output

AS

SET @numTitlesOUT = NEWID()

UPDATE [Helios imp].[dbo].[Access]
SET
[brukernavn] = @brukernavn,
[pass] = @pass,
[session_id] = @numTitlesOUT,
[Kunde_Nr] = @Kunde_Nr,
[bru_id] = @bru_id,
[leverandor_Nr_] = @leverandor_Nr_
WHERE
[brukernavn] = @brukernavn AND
[pass] = @pass

The web-application is written in C#.NET. Have anyone any idea why this strange problem occurs or how I can debug and find out?

Cheers

Sindre M

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-21 : 23:11:12

With your tables this small still, you might be seeing a lot of execution plan changes and recompiles. Have you searched for recompiles in profiler when you are doing this? Also, what does the execution plan show? Is it showing table scans or index usage??

Do this also. Run DBCC PROCCACHE and run it. Then run several more times to see the time difference. Repeat this a couple times. Are you seeing the issue you're experiencing in your application repeated here?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sindrem
Starting Member

2 Posts

Posted - 2004-04-22 : 02:28:57

Hmm.. I think I have identified the problem. In my stored procedure I have two optional parameters, but only one of them is set to null by default in the procedure. When I corrected this problem, the script did not time out.

But anyway; thanks for the information. I didn't know of the DBCC PROCCACHE. It is always great to learn new stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources