FreeTDS Development Group

[Michael Peppler <mpeppler AT peppler.org>]

Index statistics can *really* screw you up if your data changes
significantly.

We had that happen yesterday - the system that I work on (a large
website) has been purchased by another company, but they want us to
remove the old user data and only leave the data for the users from
the new company.

So we're running a script to delete user data, a user at a time.

The script is chugging along, and suddenly the server (a Sun E4500
with 10 Sybase engines running) is running at *full* load - why?
because some of the queries in some procs have started to table scan
due to index skew...

So most of the day yesterday was spent doing update statistics
(actually running the REORG command), and the load is now down to
something more manageable.

However - using procs or ad-hoc queries would not have made any
difference here - the optimizer would have made the same (incorrect)
choice because of the skewed statistics.

Michael


Lowden, James K writes:
 > 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...
 > 
 > ---
 > You are currently subscribed to freetds as: [mpeppler AT peppler.org]
 > To unsubscribe, forward this message to $subst('Email.Unsub')

-- 
Michael Peppler - Data Migrations Inc. - mpeppler AT peppler.org
http://www.mbay.net/~mpeppler - mpeppler AT mbay.net
International Sybase User Group - http://www.isug.com
*Looking for new project to tackle starting 8/1/01*