katahira
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
ASKER
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!
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!