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 ]

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

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

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

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

--mk