A stored procedure runs very slowly the first time

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)

A stored procedure runs very slowly the first time

Author

Topic

cherlin
Starting Member

3 Posts

Posted - 2006-10-23 : 00:46:54

I created a stored procedure. But unfortunately, it runs for almost 30 seconds to retrieve records for the first time after the SQL Server is restarted. And the following executions are just need no more than 1 second. That's too strange, isn't it?

Can anybody there tell me the reason? I'll much appreciate that!

PS: The main schema of the used tables:
CREATE TABLE dbo.Users
(
UserID int identity(1,1) NOT NULL, -- PK, CLUSTERED INDEX
Username nvarchar(256) NOT NULL,
CardIndexID int NOT NULL, -- FK(UserCards.CardIndexID)
TimeCreated datetime NOT NULL,
... -- other fields
)

CREATE TABLE dbo.UserCards
(
CardIndexID int NOT NULL, -- PK, CLUSTERED INDEX
CardID nvarchar(64) NOT NULL,
CardCategoryID int NOT NULL, -- FK(CardCategories.CardCategoryID)
TimeCreated datetime NOT NULL
)

The main database query statements are:
-- Creates a temp table to store the selected records
CREATE TABLE #Cards
(
IndexID int IDENTITY(1,1) NOT NULL,
CardIndexID int NOT NULL
)

INSERT INTO #Cards
SELECT c.CardIndexID
FROM [dbo].UserCards c LEFT OUTER JOIN [dbo].user_Users u ON c.CardIndexID = u.CardIndexID
WHERE c.CardCategoryID = 1

SELECT
u.*,
c.CardID,
c.CardCategoryID,
c.TimeCreated AS CardTimeCreated
FROM
#Cards p
INNER JOIN [dbo].UserCards c ON p.CardIndexID = c.CardIndexID
LEFT OUTER JOIN [dbo].Users u ON p.CardIndexID = u.CardIndexID
WHERE
p.IndexID > 200 AND p.IndexID < 400
ORDER BY
p.IndexID ASC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 02:54:47

No, I don't think so.
First time, SQL Server has to create a query plan and some statistics regarding the query.
The second time, most probably the query plan is cached in memory and hence run much faster.

Peter Larsson
Helsingborg, Sweden

cherlin
Starting Member

3 Posts

Posted - 2006-10-23 : 05:25:15

quote:
Originally posted by Peso

No, I don't think so.
First time, SQL Server has to create a query plan and some statistics regarding the query.
The second time, most probably the query plan is cached in memory and hence run much faster.

Peter Larsson
Helsingborg, Sweden

But the 30 seconds is still too long for SQL Server 2000 to execute the stored procedure, as there are just 50,000 records in the UserCards table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 05:32:20

But there query doesn't take 30 seconds, right?
It takes just 1 second.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-23 : 08:46:35

re-read St. Peter's answer.

quote:
Originally posted by Peso

No, I don't think so.
First time, SQL Server has to create a query plan and some statistics regarding the query.
The second time, most probably the query plan is cached in memory and hence run much faster.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-10-24 : 01:10:12

Seems like you're making a lot of work (although not that much it should take 30 seconds).

Why do you use a temp table?
Why do you use outer joins when you have fk relationships?

Maybe your code has just freaked out the optimiser trying to work out WTF you are trying to do!

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-24 : 18:01:32

I agree with Lozt, very confusing what you are trying to get, and a round about way to get there. It looks like the following query would return the same data, without the temp table and the exra query:


SELECT top 199 u.*, c.CardID, c.CardCategoryID, c.TimeCreated AS CardTimeCreated
FROM Users u
	INNER JOIN UserCards c
		ON u.CardIndexID = c.CardIndexID
WHERE c.CardCategoryID = 1

Your order by clause isn't helpful, because the data being inserted isn't ordered. This also makes the where clause meaningless except as a limiter to 199 records (of unordered data)

Am I missing something? anyone?

oh.. I did miss something... it would return 199 records as long as there were at least 399 records... so maybe that's what the intent was... hmmm.. <thinks more>

well, back again...
Still assuming the intent of the poster, you could do this:


DECLARE @Top int, @Count int

SELECT @Count = Count(c.CardIndexID) 
FROM [dbo].UserCards c 
	INNER JOIN [dbo].user_Users u 
		ON c.CardIndexID = u.CardIndexID 
WHERE c.CardCategoryID = 1

SELECT @Top =	CASE 	WHEN @Count <= 200 THEN 0
			WHEN @Count < 400 THEN @Count-200
			ELSE 199
		END

SELECT top (@Top) u.*, c.CardID, c.CardCategoryID, c.TimeCreated AS CardTimeCreated
FROM Users u
	INNER JOIN UserCards c
		ON u.CardIndexID = c.CardIndexID
WHERE c.CardCategoryID = 1

That's nice and ugly

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-24 : 18:04:32

This is why you should avoid restarting SQL Server as much as possible. You receive a pretty big performance hit since there's nothing in the cache after the restart.

Tara Kizer

cherlin
Starting Member

3 Posts

Posted - 2006-10-27 : 01:53:37

I did't make myself clear. Sorry about that.

There are two tables as I desribed in my post, dbo.Users and dbo.UserCards. As the name suggests, the dbo.Users table is used to store the information about all the users. And all the card information is stored in dbo.UserCards.

But please notice that a User may not has one card. To keep the foreign reference I created a fake card which CardIndexID field is zero. Anyone who has no card, his CardIndexID is zero.

Sample data:
SET IDENTITY_INSERT dbo.UserCards ON
INSERT INTO dbo.UserCards( CardIndexID, CardID, CardCategoryID, TimeCreated) VALUES ( 0, 'A fake card', /* A fake card category */0, getdate())
INSERT INTO dbo.UserCards( CardIndexID, CardID, CardCategoryID, TimeCreated) VALUES ( 1, 'Card - 1', 1, getdate())
INSERT INTO dbo.UserCards( CardIndexID, CardID, CardCategoryID, TimeCreated) VALUES ( 2, 'Card - 2', 1, getdate())
...
INSERT INTO dbo.UserCards( CardIndexID, CardID, CardCategoryID, TimeCreated) VALUES ( 50000, 'Card - 50000', 1, getdate())
SET IDENTITY_INSERT dbo.UserCards OFF

INSERT INTO dbo.Users(Username, CardIndexID, TimeCreated) VALUES('user-1', 1, getdate())
INSERT INTO dbo.Users(Username, CardIndexID, TimeCreated) VALUES('user-2', 2, getdate())
INSERT INTO dbo.Users(Username, CardIndexID, TimeCreated) VALUES('user-nocard-1', 0, getdate())
INSERT INTO dbo.Users(Username, CardIndexID, TimeCreated) VALUES('user-nocard-2', 0, getdate())

The procedure is designed to finish two major tasks:
1) Selects all the cards with its owner
2) Pages the selected results using the specified page index and page size.
I hope I have make myself clear.

The stored precedure I wrote is as following:

CREATE PROCEDURE [dbo].sp_cs_GetPagedCards
@CardCategoryID int,
@PageIndex int,
@PageSize int
AS
BEGIN
SET NOCOUNT ON

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
DECLARE @TotalPages int

-- Sets the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize - 1

-- Creates a temp table to store the selected records
CREATE TABLE #Cards
(
IndexID int IDENTITY(1,1) NOT NULL,
CardIndexID int NOT NULL
)

-- Inserts data into our temp table
IF ( @CardCategoryID <> 0 )
BEGIN
INSERT INTO
#Cards ( CardIndexID )
SELECT
c.CardIndexID
FROM
[dbo].UserCards c LEFT OUTER JOIN [dbo].Users su ON c.CardIndexID = u.CardIndexID
WHERE
c.CardCategoryID = @CardCategoryID
END

-- Gets the total number of selected records
SELECT @TotalRecords = COUNT(*) FROM #Cards

-- Adjusts the page index
IF ( @PageLowerBound >= @TotalRecords )
BEGIN
IF ( @TotalRecords > 0 )
SET @TotalPages = CEILING( CAST(@TotalRecords AS float(2)) / @PageSize )
ELSE
SET @TotalPages = 1
IF ( @PageIndex + 1 > @TotalPages )
BEGIN
SET @PageIndex = @TotalPages - 1
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize - 1
END
END

-- Returns the selected records
SELECT
u.*,
c.CardID,
c.CardCategoryID,
c.TimeCreated AS CardTimeCreated
FROM
#Cards p
INNER JOIN [dbo].UserCards c ON p.CardIndexID = c.CardIndexID
LEFT OUTER JOIN [dbo].Users u ON p.CardIndexID = u.CardIndexID
WHERE
p.IndexID >= @PageLowerBound AND
p.IndexID <= @PageUpperBound
ORDER BY
p.IndexID ASC

-- Returns the total number of records
SELECT @TotalRecords AS TotalRecords

RETURN 0
END
GO

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-27 : 08:48:16

quote:
I created a stored procedure. But unfortunately, it runs for almost 30 seconds to retrieve records for the first time after the SQL Server is restarted. And the following executions are just need no more than 1 second. That's too strange, isn't it?

Answer: No, that is not too strange. It is normal since there is no cache for the proc to draw a plan from.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-27 : 12:40:41

Apart from the speed issue on first run (which was explained already), are you sure you are getting the proper results?

some coding problems:
1) You have a join in the temp table insert query that isn't needed, just adds to the time.
2) In your insert query for filling the temp table, you are not ordering your data. Without an order by in this select query, it is not guaranteed to be the same every time you run the query.
3) When you query against the temp table, you are grabbing a record set based on their position in the table, which mean you are assuming the data in the temp table is going to be ordered the same every time (which it might not, see point 2).
4) returning recordset with a select, then using a select to ruturn TotalRecords (hmmm)

And as mentioned previously, you could rewrite it without the temp table, but this is secondary to getting the proper results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources