• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Why is my stored procedure so slow?

Hi friends!

I have a stored procedure that creates a temporary table with many columns, inserts a line into this table by using INSERT SELECT statement, executes some updates and finally select all columns of this table.

When I execute step-by-step it's OK, but when I execute the stored procedure it's very very slow. It doesn't happen in the development server.

Do you have any idea?

Thanks by advance!

Regards,
Ricky

0
katahira
Asked:
katahira
1 Solution
 
amitpagarwalCommented:
creating a temp table locks the sysobjects/syscolumns tables of the tempdb database.

so if other processes are using those tables, your temp table creation would get slowed down and overall slowdown your proc.

also - just verify the queryplan using
SET SHOWPLAN ON
SET STATISTICS IO ON
0
 
sanbingoCommented:
Calling stored procedure more than once and when you use table (same or temp) could result in a lock. So, you should think of multiuser calls while you wrote this. DEBUG with some PRINT to see what's going on when you call it.
0
 
vka3Commented:
Are you inserting one row at a time or multiple rows using
insert <table> select ....
If the result set is large it may hold tempdb as long running transaction.
You may insert a fixed number of rows at a time and commit.
set rowcount <num>

may help.

or Cursor may help which inserts one row at a time.

Look at the size of tempdb on dev and prod servers also number of users logged in both servers.
Try avoiding creating tables in tempdb if you can make a proper join with subquery to satisfy what you want without saving intermediate results in tempdb.
0
 
katahiraAuthor Commented:
Hi friends!

I figured out that the datatype of parameter I was using in a select statement didn't match with the column datatype.

So the procedure wasn't using the indexes. That's why the query was so slow.

Anyway, thanks for the help. I appreciate so much!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now