Stored Procedure Problems... 
Author Message
 Stored Procedure Problems...

George>
George>
George> Except - the sp runs in about 10 -15 minutes while the SQL run
George> sequentially, takes about 2 minutes.   As this is part of our overnight
George> processing, any time is crutial.
George>

Try doing:

set fmtonly on
go
exec my_stored_proc
go

I think you'll see what it's doing wrong...  okay, okay,
okay.... what I think is going on is that the optimizer,
when it compiles your stored proc, doesn't know the size of
the temp tables so it assumes 100 rows (or is it 10
pages?)... anyway, a small amount... and then builds the
access plan that way.  You might have to force some index
usuage...
--
Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

-------------------------------------------------------------------------------
I am accountable for my actions.   http://www.***.com/ [ /Sybase_FAQ ]



Mon, 10 Apr 2000 03:00:00 GMT
 Stored Procedure Problems...

Hi all,

First post to this group so please excuse any discretions.

We have a new sp that continas 4 select ... into #tmp statements and
then a final select ... into to create the final table.   Nothing too
strange.

Except - the sp runs in about 10 -15 minutes while the SQL run
sequentially, takes about 2 minutes.   As this is part of our overnight
processing, any time is crutial.

I am mistaken in believing that sp's are menat to run faster as they a
compiled??
Is there anything that I am missing that would mean that the sp runs
*considerably* slower that the SQL??

Any replies will be helpful, direct email to the address at the bottom
of this message.

Thanks for any help on this wierdness..

Rgds,

George Nattey

Development Team Leader
FOXTEL Management P/L
Sydney, Australia

==============================

==============================



Tue, 11 Apr 2000 03:00:00 GMT
 Stored Procedure Problems...

also 'set showplan on' to see the query plan.

--mk

Quote:


> George>
> George>
> George> Except - the sp runs in about 10 -15 minutes while the SQL run
> George> sequentially, takes about 2 minutes.   As this is part of our overnight
> George> processing, any time is crutial.
> George>

> Try doing:

> set fmtonly on
> go
> exec my_stored_proc
> go

> I think you'll see what it's doing wrong...  okay, okay,
> okay.... what I think is going on is that the optimizer,
> when it compiles your stored proc, doesn't know the size of
> the temp tables so it assumes 100 rows (or is it 10
> pages?)... anyway, a small amount... and then builds the
> access plan that way.  You might have to force some index
> usuage...
> --
> Pablo Sanchez              | Ph # (650) 933.3812          Fax # (650) 933.2821

> -------------------------------------------------------------------------------
> I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]



Sun, 16 Apr 2000 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Stored procedures problem using DB2 Stored Procedure Builder

2. SQL Stored Procedure Problem

3. Stored procedure problem.

4. Stored Procedure problem (7.0)

5. Extended Stored Procedure Problem

6. ADOX and Stored Procedure Problem

7. HELP! Stored Procedure Problem - Works then Breaks

8. Stored procedure problem

9. Stored Procedure Problems

10. Delphi 3 and MS SQL Stored Procedure Problem

11. Stored procedure problem

12. Store Procedure Problem


 
Powered by phpBB® Forum Software