|
|
|
|
| Re: Statement execution speed depends on single statement or stored procedure? [message #403486 is a reply to message #403335] |
Fri, 15 May 2009 16:21  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I expect you will provide the details BlackSwan has asked for.
In the mean time, lets start with three reasons why this can happen.
1a) when you execute a sql statement in toad that returns data, it does not normally return all data, but instead returns a subset of the data (say 20 rows) then stops and waits for you to ask for the next set of data.
1b) when you execute the same sql statement in a procedure it usually fetches all rows not just the first set. So... you may be comparing the retrieval of 20 rows against 200,000 rows (you get the idea).
2a) when you execute a sql statment from toad the optimizer uses the default settings of our connection meaning that if your database is set for first_rows, that is how the query plan is created.
2b) when you execute the same sql from a procedure, the optimizer ingores that database setting and nnormally optimizes the sql using all_rows hint (at least it does for the releases I use). Why? read#1b above.
3a) when you execute a sql statement from toad it is one statement so fetching is done by array size aka. set processing is done (this is related to 1a above)
3b) when you execute the same sql statement from a procedure, programmers get stupid for some reason and the put the statement in a cursor or for loop and loop through the data one row at a time. As Tom Kyte says, row by row = slow by slow.
OK so that is some food for thought.
First get BlackSwan what was asked for.
Second, think about the above and tell us if one of these three applies to you, or if it is something else.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
