Stored Procedure taking very long 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)

Stored Procedure taking very long time

Author

Topic

esti
Starting Member

12 Posts

Posted - 2006-05-01 : 11:14:38

I am running a stored procedure. It takes an hour to run and is making me crazy. Can anyone tell me why this would be. One of the tables has 9 million records. Would this cause it? Or is it something to do with indexing (which I'm a little fuzzy on)? Thanks in advance!

CREATE PROCEDURE dbo.sp_qryCombo AS
Insert into combo
SELECT DeptWorkNew.Dept,
DeptWorkNew.item_name,
DeptWorkNew.PanelDept, DeptWorkNew.Workstation,tblMonthlyBill.*
FROM DeptWorkNew
RIGHT JOIN tblMonthlyBill ON (DeptWorkNew.chg_type = tblMonthlyBill.ChargeType)
AND (DeptWorkNew.item_code = tblMonthlyBill.ItemCode)
AND (DeptWorkNew.schedule = tblMonthlyBill.ScheduleCode)
GO

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-01 : 11:31:53

First, drop the "SELECT *". This is considered very poor programming practice for production code. Always enumerate your column names.
Second, what indexes are currently on the tables? All three column should be indexed from each of the tables, perhaps as single composite indexes. Also, does each table have a defined primary key?

esti
Starting Member

12 Posts

Posted - 2006-05-01 : 13:35:03

Yes each table has a primary key, and I am in the process of adding the indexes. I redesigned one of the tables and forgot to add the indexes back in! Thank you!
As far as the SELECT, if I drop the SELECT, how would the syntax work?
This is incorrect:

Insert into combo
DeptWorkNew.Dept .... etc

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-01 : 15:28:16

Just drop the "*" part, and list the column names instead.

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 05:31:07

I don't think the query plan for your Sproc will be cached unless you explicitly state the table owner, and if so that will be costing you a bit each time the SProc is run - e.g.


CREATE PROCEDURE dbo.sp_qryCombo AS
Insert into dbo.combo
SELECT DeptWorkNew.Dept, 
DeptWorkNew.item_name, 
DeptWorkNew.PanelDept, DeptWorkNew.Workstation,tblMonthlyBill.Column1 ...
FROM dbo.DeptWorkNew 
RIGHT JOIN dbo.tblMonthlyBill ON (DeptWorkNew.chg_type = tblMonthlyBill.ChargeType)
AND (DeptWorkNew.item_code = tblMonthlyBill.ItemCode) 
AND (DeptWorkNew.schedule = tblMonthlyBill.ScheduleCode)
GO

Kristen

pootle_flump

1064 Posts

Posted - 2006-05-02 : 07:02:26

quote:
Originally posted by Kristen

I don't think the query plan for your Sproc will be cached unless you explicitly state the table owner, and if so that will be costing you a bit each time the SProc is run - e.g.

Hi Kristen

I thought this was so interesting (and feared that I might have to plough through some legacy code) that I decided to test.

SET NOCOUNT ON

CREATE TABLE MyTable_123 (MyCol Int)

DECLARE @i AS TinyInt
SELECT @i = 0

WHILE @i < 100 BEGIN
	
	SELECT 	@i = @i + 1

	INSERT	INTO MyTable_123
	SELECT 	@i
	
END
GO

CREATE PROC dbo.NoOwner
AS

SELECT	MyCol
FROM	MyTable_123
WHERE	MyCol < 50

GO

CREATE PROC dbo.Owner
AS

SELECT	MyCol
FROM	dbo.MyTable_123
WHERE	MyCol < 50

GO

EXEC dbo.Owner

EXEC dbo.NoOwner

SELECT *
FROM master..syscacheobjects
WHERE sql IN ('Owner', 'NoOwner')

DROP TABLE MyTable_123
DROP PROC dbo.NoOwner
DROP PROC dbo.Owner

Assuming I understand you correctly, it seems it is cached. I don't mean, of course, that prefixing isn't important. But at least it seems I can sort out legacy code at a measured pace after the scare you gave me

pootle_flump

1064 Posts

Posted - 2006-05-02 : 07:13:14

Ok - just stumbled across something on the web that implied that SQL Server may sometimes not use the cached plan if the tables aren't qualified with owner.

Ho hum.

Regarding the question - how long does the SELECT (without the INSERT bit) take to run and what sort of indexes\ triggers etc are on the combo table?

Also - as Blindman states using SELECT * is bad practice but it is doubly so when inserting as a change to either tables schema will break your code. Worth bearing in mind in future.

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 07:41:10

I can't see any difference in Profiler either. However, in trying to also create a MyTable_123 table owned by ME, in addition to DBO, I can't get it to behave the way I want.

I created MyUser.MyTable_123 and put rows 50-100 in it (i.e. it will display nothing if I ask for < 10)

SELECT MyCol
FROM MyTable_123
WHERE MyCol < 10

displays 1...10 whereas

SELECT MyCol
FROM MyUser.MyTable_123
WHERE MyCol < 10

finds no rows.

What elementary mistake have I made?

(I was trying to find out how the NoOwner Sproc behaved if an owner-owned table was created after the SProc was cached ...)

Kristen

pootle_flump

1064 Posts

Posted - 2006-05-02 : 07:45:49

quote:
Originally posted by Kristen

.....I created MyUser.MyTable_123 and put rows 50-100 in it (i.e. it will display nothing if I ask for < 10)
.....
SELECT MyCol
FROM MyUser.MyTable_123
WHERE MyCol < 10

finds no rows.

Isn't the query working just fine?

pootle_flump

1064 Posts

Posted - 2006-05-02 : 07:48:38

quote:
Originally posted by pootle_flump

Ok - just stumbled across something on the web that implied that SQL Server may sometimes not use the cached plan if the tables aren't qualified with owner.

BTW - this was posted by Satya on another board (informit I think). I can't find mention of it anywhere else. I think he also posts here and DBForums.

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 08:25:51

"Isn't the query working just fine?"

Yup, but if I log in to QA using the UserID "MyUser" and type:

SELECT MyCol
FROM MyTable_123
WHERE MyCol < 10

I was expecting to get no results (i.e. SQL to use MyUser.MyTable_123 and not dbo.MyTable_123)

Kristen

pootle_flump

1064 Posts

Posted - 2006-05-02 : 08:45:51

Coo - I dunno. To see if I understand I used:

SET NOCOUNT ON

CREATE TABLE dbo.MyTable_123 (MyCol Int)

GRANT SELECT ON dbo.MyTable_123 TO MyUser

CREATE TABLE MyUser.MyTable_123 (MyCol Int)

DECLARE @i AS TinyInt
SELECT @i = 0

WHILE @i < 100 BEGIN
	
	SELECT 	@i = @i + 1
	
	INSERT	INTO dbo.MyTable_123
	SELECT 	@i
	
	IF @i > 50 BEGIN

		INSERT	INTO MyUser.MyTable_123
		SELECT 	@i

	END
END
GO

using NT authentification.

Then logged in as MyUser (SQL user) to QA and ran:

SELECT 	*
FROM	dbo.MyTable_123
WHERE	MyCol < 10

SELECT	*
FROM	MyTable_123
WHERE	MyCol < 10

SELECT	*
FROM	MyUser.MyTable_123
WHERE	MyCol < 10

Results:

(9 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

That's as I would expect. But you aren't getting that - is that right?

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 09:18:41

"That's as I would expect"

Me to

"But you aren't getting that - is that right?"

Correct

OK, my cock-up, sorry. The "Test User" I was logging on as must have silently had SA somewhere along the way ....

So, working as expected now.

If I do EXEC dbo.NoOwner (logged on as "MyUser") then I *do* see 1...10 - so something has implicitly parsed "MyTable_123" as "dbo.MyTable_123" in the NoOwner Sproc.

If I create the NoOwner Sproc logged in as MyUser then, by default, the table used is the one owned by MyUser and not the "dbo" one.

Makes sense, and I suppose this is therefore a "Compile Time" performance issue (trivial) rather than a Run Time / Cache Hit issue.

Kristen

pootle_flump

1064 Posts

Posted - 2006-05-03 : 10:32:02

OT I know (apols) but just found:

"Reducing COMPILE Locks

It is considered a good practice to reference all objects in stored procedure code with the owner's name. While this will not stop recompiles, it will stop SQL Server from placing a COMPILE lock on the procedure while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan. "

from
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp

Kristen
Test

22859 Posts

Posted - 2006-05-03 : 14:29:46

Is there a way to see COMPILE LOCKs in SQL profiler?

Kristen

pootle_flump

1064 Posts

Posted - 2006-05-03 : 14:51:53

Indirectly it appears:
http://support.microsoft.com/?id=263889

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources