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
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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!
Sybase Database
Sybase, a subsidiary of SAP, builds a client/server relational database management system. Products include Adaptive Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase Unwired Platform (SUP) for mobile applications, Afaria for enterprise mobile device management and IQ for data warehouse and big data applications.
5K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
