Stored proc works with one set of paramaters, but not another

Stored proc works with one set of paramaters, but not another

Post by Gar » Fri, 28 Aug 1998 04:00:00



Hi all,

I have a problem with one of my SQL 6.5 (SP3) stored procs that is
absolutely numbing my mind, so maybe one of you gurus can help me out.

The sp has three parameters (StartDate, EndDate, CenterID).  It is fairly
complex in that it performs about 10 or 11 SELECTs on various tables and
joins (mostly inner joins, a few outer joins here and there, *= style,
not LEFT OUTER JOIN style), and UNIONing all the results (e.g. SELECT ...
UNION ALL SELECT ... UNION ALL SELECT ... UNION ALL ... ORDER BY 1, 3.)
to return a single set of data.

Each filter in the where clauses of every SELECT is on a field that has
an index, so it should be using indexes mostly and maybe a few
tablescans.

The database is a 2GB device with about 600MB used, and a 300MB log
device.  TempDB is about 120MB.

Now, there are three possible CenterIDs (1,2,3).  When I run the sp in
the query window using a valid 15 day date range, and CenterID = 1, the
query return my results in about 10 seconds.  This is great, fantastic,
super!  When I run the sp with the same date range and either CenterID =
2 or 3, the query hangs forever or until I stop the query.  The globe
spins on 2 or 3, but no results (and I have let it run for more than 10
minutes).

I have run dbcc dbreindex, dropped and recreated the sp, updated
statistics (several times!), and no matter what I do, 1 works, but 2 or 3
do not work.

If I pull the big SELECT...UNION statement out of the sp, and run it as a
query, it works for 1, 2 and 3 (all the same speed).

To me, this sounds like the query optimizer is having a lot of trouble
creating the query plans for CenterIDs 2 and 3, when the query is run
from within the sp.  I don't know what more I can do to help it out.

I have generated SHOWPLANs for the following scenarios:
1 CenterID = 1 outside of sp
2 CenterID = 2 outside of sp
3 CenterID = 1 using sp
4 CenterID = 2 using sp

The SHOWPLANs for 1 and 2 are identical.  3 is a little different than 1.  
4 can not be generated because when I run the sp with centerid = 2 after
setting showplan and statistics io on, Enterprise Manager hangs
completely and has to be shut down from the Task Manager.

PLEASE, if anbody has any ideas, let me know ASAP.  I need a solution
quickly.

Thanks very much for any help you can give!

Gary

(email is fine if you prefer.)

 
 
 

Stored proc works with one set of paramaters, but not another

Post by Tony Rogerso » Sat, 29 Aug 1998 04:00:00


If you haven't already done so, try creating the stored procedure with the
recompile option.

--
Hope the above helps.

Tony Rogerson
Torver Computer Consultants Ltd.

 
 
 

Stored proc works with one set of paramaters, but not another

Post by Gar » Sat, 29 Aug 1998 04:00:00




Quote:> If you haven't already done so, try creating the stored procedure with the
> recompile option.

> --
> Hope the above helps.

> Tony Rogerson
> Torver Computer Consultants Ltd.

I haven't tried creating the proc with recompile, but I have tried
executing it with recompile...still fails.

Thanks for the suggestion.

Gary

 
 
 

Stored proc works with one set of paramaters, but not another

Post by Isaac Bla » Sat, 29 Aug 1998 04:00:00


Gary,

        If your queries run fine as batches and are slow when put into
a stored procedure then try executing them dynamically.  Create a
varchar(255) variable ( or maybe several variables if your SELECT is
longer), populate it with your statament and execute it via EXEC
(string_expression) statement.  Performance penalty will be minimal
since these are relatively long queries anyways.

        Unfortunately, things like this happen in stored procedures,
most often when you use local variables in your WHERE clause because
in this case the optimizer can not use statistics effectively.  So
another possible workaround would be encapsulating the final SELECT
into a separate stored procedure which you call from the main one.
Something like this:


AS



GO


WITH RECOMPILE
AS
SELECT *
FROM myTable

GO

        And of course you can try what I call arm-twisting: forcing
indexes with optimizer hint, forcing join order with SET FORCEPLAN ON.
Just take the indexes and join orders from the showplan output and try
to enforce it somehow.

HTH