Re: Oracle vs. Sybase

From: Lee Doty <iarld_at_connectnet.com>
Date: 1997/06/13
Message-ID: <33a188b6.1085060@news.connectnet.com>#1/1

Jon Winchester <jon_at_wh.net> wrote:

>We tried to port out application to Sybase and experienced some strange
>problems. The one killer that we were never able to clear up, even after
>bringing in a Sybase expert consultant was that it appeared to convert
>every SQL that we sent to it from our app (VB via ODBC calls direct through
>ODBC API) into system stored procedures and then execute them.
>
>While the performance was good, it would build up these stored procedured
>and would begin to slow down. The only fix that we found was to shut down
>the server daily to clear them out.
>
>I am sure there is a way to work with the server so that this does not
>happen, but we (admitted Sybase non-experts) were unable to find it as
>was our supposed Sybase expert.
>
>Jon

We had the same problem with MS SQL Server. The solution is in the ODBC driver-specific connect options.

Basicly the problem is that tempdb gets erroded because everytime a new query is prepared (prepared, _not_ SQLExecDirect) the driver would make a temporary stored procedure.

The problem can be cleared up by:

dropping the connection more frequently.
setting a driver-specific connect option (SQL_PROC_ON_PREPARE (or some such thingie) set to SQL_UP_OFF (to never generate the temp stored procs) or SQL_UP_ON_DROP (to drop them when SQLFreeStmt is called) ) Consult your driver-specific ODBC help on this one.

This probably won't help you, but you never know who else may be helped by this. I know we could have used the help about 8 months ago.

cheers!

-Lee

"It can't rain all the time" Received on Fri Jun 13 1997 - 00:00:00 CDT