FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

Hi Norman, 

Unless your query winds up using different indexes depending on the
parameters it's provided, there's no advantage to using "with recompile".  

Indexes are updated in real time, with the data, but index statistics are
static.  If the distribution of your keys is highly dynamic but your
prefered index is consistent, using "with recompile" could easily cause the
query optimizer to misconstrue the situation and not employ your index.  

But you probably already know that.   

--jkl

-----Original Message-----
From: Norman Palardy [mailto:norm AT kirais.com]
Subject: [freetds] Re: SQL Server Emulation

We have very large very active tables (something like 20 million rows in one
of them) and stored procs are not great in this situation because of how
they cache query plans. This is on Sybase 11.9.x and Sybase 12.x

The only way to get around this is to create the proc with recompile
option...