Re: VB3 and Oracle ODBC SQL Pass-through - speed quirk

Greg Torrance wrote:
>
> Hi all,
>
> I was wondering if anyone could help me. I have been working on a
> project in VB4, using SQL pass-through through the Oracle 71 ODBC
> driver. Due to it being unbearable slow on our 386 (yes, 386)
> machines, I've downgraded it to run on VB3. It's now a lot faster
> overall, but I'm having a problem with action queries. I have a
> procedure to delete records. In it, I call the ExecuteSQL method in a
> similar way to the following:
>
> TmpCount = MyDatabase.ExecuteSQL(" <insert sql statement below> ")
>
> DELETE synergy.trtime
> WHERE synergite = 'GREG'
> AND TO_CHAR(trdate, 'YYYY MM DD') = '1996 07 19'
> AND TO_CHAR(starttime, 'HH24 MI') IN ('07 30', '07 45', '08 00')
>
> The first time this statement is executed, it takes about 26 seconds
> (on a Pentium 75). Subsequent to that, it takes only about 1 second
> every time it is executed. _Prior_ to this, the database has been
> opened, and a number of snapshots have been created using SQL
> pass-through (e.g. CreateSnapshot ("select * ...",
> DB_SQLPASSTHROUGH)), with no apparent (sp?) speed problem.
>
> I also have the same problem with my other ExecuteSQL statements. The
> first of any of these statements that executes, takes a long time.
> After that, any of them can execute, with no speed problems (even if
> they have not executed before).
>
> Oh BTW - every time I re-load the program, this problem occurs over
> again. [I haven't left it long enought to see if happens again in the
> same instance of the program - it might do.] Caching problems???
>
> Sorry if I got myself in a bit of a tangle trying to explain it all -
> it's Friday.
>
> Any help would be greatly appreciated.
>
> Thanks muchly,
> Greg
>
> P.S. We're using Oracle 7.2 (on NT Server 3.51), and I'm connected
> through SQL*Net TCP/IP 1.1.9.0A - if that helps.
Hi,

I'm using VB3 with Oracle 7.1 on NT and I use the following syntax for UPDATE, INSERT & DELETE queries :

DB.Execute query_name, DB_SQLPASSTHROUGH

(my VB hungs if I use ExecuteSQL instead of Execute :-(.

Also try switching to, at least, SQL*Net 2.1 (2.2 and 2.3 are available), it's a lot faster.

Hope it helps,
Alex.

The opinions expressed are my own, not my employer's. Received on Wed Jul 24 1996 - 00:00:00 CEST