Date:         Fri, 10 Jan 1997 17:47:53 -0600
Reply-To:     marso@spss.com
Sender:       "SPSSX(r) Discussion" <SPSSX-L@UGA.CC.UGA.EDU>
From:         David Marso <marso@SPSS.COM>
Organization: SPSS Inc
Subject:      Re: Slow access in 7.5 to SQL Server
Content-Type: text/plain; charset=us-ascii

I have privately emailed this to Chris.  I would have posted sooner
but my access to the list has been sporatic.  This is probably of
general
interest to those using ODBC with big data bases with many users.
Regards, David Marso
 
 
Chris Keran wrote:
>
> Has anyone else tried to access a database using the ODBC drivers in
> SPSS 7.5 for 95/NT?  I let it run for an hour without success.  I let it
> run overnight, and it worked!  In 7.0, it took just a few seconds.
>
> I know 7.5 allows access to multiple tables (which is why it's now
> slower), but 8 hours just to view some tables?  I hope this 'bug' is
> soon eradicated.
 
    A discussion with our developers revealed that the way Microsoft has
privilege checking implemented
makes it necessary to first determine the names of the tables and then
make a request for the fields within
each of the tables. Every user that has privileges to a field generates
a record indicating the kind of privileges
they have (SELECT or DELETE).   This means that if many users have
privileges to a database and it
has lots of fields then there is a lot of information to move across the
network and search through to determine
if the current login id has SELECT privileges. It is this process that
takes time. It has been decided to
disable this access checking as the default setting in 7.51.  Meanwhile,
for those using version 7.5 there
is a fix available on the SPSS Web Page
 
 New SPSSODBC.DLL for SPSS 7.5 for Windows to correct a problem with
freezing while accessing data.
 
        http://www.spss.com/Support/11page.html#odbc
 
For those who wish to maintain the previous default privilege settings
(have privilege checking enabled ) one
can modify the registry value "CheckPrivileges"  to "1" in  the
\\HKEY_CURRENT_USER\Software\SPSS\SPSS For Windows\7.0\ODBC section of
the registry. However,
because of it's implementation in ODBC it will slow down immensly.