Nested store proc woes
-
We are having issues with this We've several generic code components that have been built into stored procs. So far so good. Now, on calling one of these stored procs from another stored proc takes an extremely long time when compared to calling just the generic stored procedure. Are there some settings that we can change to cut down the timing or is this a SQL anomaly? Your advise is much appreciated.
Example
CREATE TABLE #ultimate_parent_sector( ac_symbol VARCHAR(50)
, issuer_id INT
, ultimate_sector INT
)
INSERT INTO #ultimate_parent_sector
EXEC ultimate_parent_sector_override_set @BusinessDate
Running the above takes around ~ 10 min
However just running
"EXEC ultimate_parent_sector_override_set @BusinessDate" takes ~1 minBTW, it takes the same amount of time if you try and insert in a permanent table as opposed to a temporary table
Any help is appreciated
Thanks
-
We may need more information. Is this processing one record at a time when you call it from another stored procedure and that may be your bottleneck.
Maybe post a bit of the code which call this and is running so slow....
I wasn't born stupid - I had to study.
-
One thing though you should write the insert as:
INSERT INTO #ultimate_parent_sector(ac_symbol, issuer_id, ultimate_sector)
EXEC dbo.ultimate_parent_sector_override_set @BusinessDate
The Field List should be specified!
The 'dbo' is a little extra help
* Noel -
Avoid using INSERT INTO EXEC because this can trigger locking the tempdb.
Check this out
http://www.sql-server-performance.com/reducing_locks.asp
Thanks,
Abhinav
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply