ODBC overseas performance problem

I am using Dolphin 4 trial version to do a concept proof before
converting over a production Visual Works Smalltalk and several VB
apps.

I am using Dolphin ODBC classes reading a Microsoft SQL Server
database over an intraanet connection from Denver to Amsterdam. But
ODBC Performance in Dolphin is much slower than other applications. To
iterate through result set of 4000 records takes 860 seconds (about 4
records/second, rows are just ints and a couple ttimestamp).
Non-Dolphin applications, including the visual works using same ODBC
have about 5x better performance.

I read notes of 2001-03-12 and wonder if Dolphin is fetching one row
at a time so I'm taking a serious network latency hit. Is that
possible, if so, how can I set the blocking factor higher?

By the way, none of the sources for the ODBC classes are visible --
I'm assuming because it's the trial version?

-Alan Wostenberg
Baan, Golden Colorado

Alan

You wrote in message news:[hidden email]...
Yes that is true. Dolphin's DB connection was not designed with remote
access in mind, and it does request only a single record at a time. If the
underlying driver is not doing its own batching (I'm surprised it doesn't),
then that is likely to result in very poor performance against a remote
database (and personally I would call 4 recs/sec very poor performance). You
could verify that by comparing performance against a local database.

Oddly this has (as far as I recall) only come up that once before (in a
report from Dmitry Zamotkin), and so I while we have an enhancement entry in
our bug-tracking database to add block fetch support, at the moment there is
no set timescale for it to be actioned. The priority of any enhancement is
mainly a commercial decision based on demand, etc, but we are open to
lobbying and/or specific contractual arrangements :-).

> By the way, none of the sources for the ODBC classes are visible --
> I'm assuming because it's the trial version?

That is right. There is full source for absolutely all the Smalltalk code in
purchased versions. Our VM is purely an execution engine, and we keep its
functionality to a bare minimum, we certainly don't put any database access
code in there, so it would be quite possible (though non-trivial) for you to
add block fetch support yourself. Another option you have in Dolphin would
be to use ADO - you can use the Active-X Component Wizard to generate
wrapper classes for the ADODB component and then use them much like you
would use ADO in VB. With a little manual tweaking it is possible to add
some more idiomatic Smalltalk support (e.g. some collection enumerators)
resulting in a more fluid Smalltalk integration. From my experiments using
ADO in this way is slower than Dolphin's ODBC package for local database
access, but it may be better for remote access.

Regards

Blair

Alan,

> Another option you have in Dolphin would
> be to use ADO - you can use the Active-X Component Wizard to generate
> wrapper classes for the ADODB component and then use them much like you
> would use ADO in VB. With a little manual tweaking it is possible to add
> some more idiomatic Smalltalk support (e.g. some collection enumerators)
> resulting in a more fluid Smalltalk integration. From my experiments using
> ADO in this way is slower than Dolphin's ODBC package for local database
> access, but it may be better for remote access.

To Blair's reply I'll add that if you go the ADO route (you might want to
check Ian's archives to see if somebody else has already done it), be sure
to take the methods you add/modify out of the autogenerated category.  At
least ask for clarification of the issues before you do a lot of work and
then want to regenerate classes.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Blair,

> Yes that is true. Dolphin's DB connection was not designed with remote
> access in mind, and it does request only a single record at a time. If the
> underlying driver is not doing its own batching (I'm surprised it
doesn't),
> then that is likely to result in very poor performance against a remote
> database (and personally I would call 4 recs/sec very poor performance).
You
> could verify that by comparing performance against a local database.
>
> Oddly this has (as far as I recall) only come up that once before (in a
> report from Dmitry Zamotkin), and so I while we have an enhancement entry
in
> our bug-tracking database to add block fetch support, at the moment there
is
> no set timescale for it to be actioned. The priority of any enhancement is
> mainly a commercial decision based on demand, etc, but we are open to
> lobbying and/or specific contractual arrangements :-).

I also reported finding problems with the Database Connection (I mentioned
them in the original thread).

Since then, I've investigated the problem a little more (but not tried to
fix it); here are some numbers.

My set-up was that Dolphin was talking ODBC to a MS SQLServer7 server
running on the same dual CPU box running NT4.

Using Dolphin I tried (names changed to protect the innocent):

    sql := 'SELECT www, xxx, yyy, zzz  FROM Table WITH (nolock)'.
    conn := (DBConnection new)
                    dsn: 'DBNAME';
                    uid: 'UNAME';
                    pwd: 'PASSWD';
                    connect.
    query := conn query: sql.
    rows := 0.
    Time millisecondsToRun: [query rawDo: [rows := 1 + rows]]. "--> 42719"

This fetches 121,744 rows in around 43 seconds.  The four selected columns
are all 32-bit integers.

Note that I'm using #rawDo: to try to get a picture of where the time is
really going.

I tried an "equivalent" in Java, running the same query against the same
ODBC datasource, accessed via the ODBC/JDBC bridge (with try/catche blocks
elided):

    Connection conn = DriverManager.getConnection(
        "jdbc:odbc:DBNAME",
        "UNAME",
        "PASSWD");
    Statement query = conn.createStatement();
    String sql = "SELECT www, xxx, yyy, zzz  FROM Table WITH (nolock)";
    ResultSet rs = query.executeQuery(sql);
    long start = System.currentTimeMillis();
    int rows = 0;
    while (rs.next())
     {
        //  int f1 = rs.getInt(1);
        //  int f2 = rs.getInt(2);
        //  int f3 = rs.getInt(3);
        //  int f4 = rs.getInt(4);
        rows++;
     }
     long end = System.currentTimeMillis();
    System.out.println(rows
       + " rows fetched in "
       + ((end - start) / 1000.0)
       + " secs");

(my apologies to anyone who comes out in hives at the sight of naked Java
code...)

That also fetches 121,744 rows, but takes around 0.6 seconds.  If I force
the JVM into interpreted mode only (which I'd expect to be slower than
Dolphin's VM), the time goes up to 2.4 seconds.

The four lines which are commented out are because I'm trying to make the
loop equivalent to #rawDo:, if I put them back in (and so actually pull the
column data out of the returned row), then the time goes up to 1.2 seconds
(or 5.8 in pure interpreted mode).

I should mention that I'm using Java for this comparison only because
that's the only other way I know to talk ODBC to SQL server.

I traced into Dolphin and the buffers seem to be set up and bound correctly
before the main loop starts.  Running under Ian's profiler shows that the
execution time is almost all (>95%) in ODBCLibrary>>sqlExtendedFetch:...  So
I don't think the time is being lost doing data massaging in Dolphin.

Another anomaly is that during the 40-odd second execution, Windows
TaskManager shows that Dolphin is using around 20% of one CPU, and that SQL
server is using around 40% of the other one.  That's odd because the Java
test shows that it can supply the same data to other programs in less than a
second, so what's it doing with all that CPU time when driven from Dolphin ?

I'm not expert in ODBC, nor -- come to that -- in the Database Connection
APIs, but it looks to me as if *something* in the way Dolphin drives ODBC
(presumably the batching factor) is slowing it down by a factor of at least
20 and perhaps as high as 60-70.  Presumably (but I didn't test it) the
effect would be even worse if I'd been going over our LAN to talk to the
server.

Since the real datasets that I was wanting to work with were of the order of
tens or hundreds of millions of rows, that meant that I couldn't use Dolphin
for the task.  (Which was slightly embarrassing since I play the Smalltalk
advocate at work, and it made my frequent comments -- taunts, really -- to
management and other developers like "that would be much easier if we were
using a grown-up language" sound more than a little hollow...)

> Blair

 -- chris

Chris, Blair,

> (my apologies to anyone who comes out in hives at the sight of naked Java
> code...)

Yes, the itching is unbearable :)


> The four lines which are commented out are because I'm trying to make the
> loop equivalent to #rawDo:, if I put them back in (and so actually pull
the
> column data out of the returned row), then the time goes up to 1.2 seconds
> (or 5.8 in pure interpreted mode).
>
> I should mention that I'm using Java for this comparison only because
> that's the only other way I know to talk ODBC to SQL server.
>
> I traced into Dolphin and the buffers seem to be set up and bound
correctly
> before the main loop starts.  Running under Ian's profiler shows that the
> execution time is almost all (>95%) in ODBCLibrary>>sqlExtendedFetch:...
So
> I don't think the time is being lost doing data massaging in Dolphin.

The simplest explanation would be that Dolphin is requesting one record at a
time rather than many as has been suggested (and confirmed??) before.


> Another anomaly is that during the 40-odd second execution, Windows
> TaskManager shows that Dolphin is using around 20% of one CPU, and that
SQL
> server is using around 40% of the other one.  That's odd because the Java
> test shows that it can supply the same data to other programs in less than
a
> second, so what's it doing with all that CPU time when driven from Dolphin
?

Probably unrelated, but, I'll mention it anyway: I have periodically
struggled with a tendency of my Dolphin apps to "stall".  A few of the
problems turned out to be caused by (long since corrected) problems in
critical sections and weak collections.  Of course, many more were deadlocks
in my own code because I bit off too much in a critical section.  I've also
found it "necessary" to change the idle loop.  It's fairly common for my
code to end up with nothing else to do but wait for a socket read stream to
get some more data.  However, I once had two DCOM apps talking and noticed
long pauses in various batch jobs between them.  I "fired" DCOM for other
reasons, and began using sockets for everything that crosses machine
boundaries.

Interestingly, the pauses persisted with sockets.  Blair concluded that the
message loop went to sleep in good faith, and _then_ the data arrived; I
agree with his analysis.  I finally shortened the idle loop's sleep duration
to something more tolerable given the frequency of the problem.  I had a
similar problem on NT4, and finally ended up using my tweaked 9x idle loop
for it too.

Please note that I do a lot of multi-threaded programming, and could easily
have problems in my code.  Deadlocks tend to show themselves fairly easily
(things stop happening), but, if I have some kind of under-protection
problem, then it could be very difficult to track down - hence the very good
advice in the Education Centre to over- rather than under-protect.  It's
also possible that some of my hacks are no longer necessary due to
improvements in Dolphin over time.

Still, I suspect that are some places in which Dolphin does nothing for a
while.  I'm glad to hear about the new VM's addressing Dolphin's excessive
yeilding to Access 97; that might help with Chris' problem, but, I it.
Chris, have you tried the test with Dolphin 2.x?

Ideally and in no particular rush, I'd like to see some kind of (probably
remote) monitor of the VM's activities.  It would probably have to be
filterable to specific Process names (a wildcard capbility would be useful -
I use a lot of threads).  Some things could be documented by exception (e.g.
Process xyz waiting > n seconds).  This probably could (and should) be
prototyped in Smalltalk but, I suspect that to be useful in a misbehaving
app, it would have to be coded into the VM.  Also, it would really need to
be useable in deployed apps, and on any version of Windows.  The remote
client/GUI would be done all in Dolphin.  It might also be enough for the VM
to open an always-on-top window and write status info there, but, a remote
app with logging capability would probably be more useful.  It goes without
saying that there should be no overhead when it's not in use :)  In tradeoff
for zero overhead, I'd be more than willing to register a different VM or
edit registry settings to toggle this capability.


> I'm not expert in ODBC, nor -- come to that -- in the Database Connection
> APIs, but it looks to me as if *something* in the way Dolphin drives ODBC
> (presumably the batching factor) is slowing it down by a factor of at
least
> 20 and perhaps as high as 60-70.  Presumably (but I didn't test it) the
> effect would be even worse if I'd been going over our LAN to talk to the
> server.

Makes sense.


Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Chris

You wrote in message
news:[hidden email]...
> ...
> I also reported finding problems with the Database Connection (I mentioned
> them in the original thread).
>
> Since then, I've investigated the problem a little more (but not tried to
> fix it); here are some numbers.
> [...big snip of code and stats...]

Thanks for the research. I would think that the big difference between the
Java code and the Dolphin DB connection is the sort of cursor used. Dolphin
uses a "dynamic" cursor type that allows navigation backwards and forwards
back through the result set. This makes for a great deal of flexibility, but
is a big performance hit. I suspect the Java code is using a "forward-only"
cursor (also sometimes call a firehose cursor) by default, which is very
cheap for the DB to implement, especially for a simple select statement with
no joins. Perhaps someone who has the source (and a stronger stomach than
me) could take a look at it and see what the Java library actually does.

If you install the attached package (which is a quick'n'dirty implementation
of forward-only result sets that doesn't modify any existing DB Connection
methods), and evaluate (modifying your original expression):

     sql := 'SELECT www, xxx, yyy, zzz  FROM Table WITH (nolock)'.
     conn := (DBConnection new)
                     dsn: 'DBNAME';
                     uid: 'UNAME';
                     pwd: 'PASSWD';
                     connect.
     query := conn query: sql forwardOnly: true.
     rows := 0.
     Time millisecondsToRun: [query rawDo: [rows := 1 + rows]].

You should find that it is at least an order of magnitude faster  - in my
experiments in Dolphin 5 against a 100,000 row MS SQL Server 7.0 table the
time to enumerate the entire recordset dropped from 52 seconds to just over
2 (in D4 it went down to about 3.7 seconds). You may also notice that the
initial #query:forwardOnly: runs almost instantaneously, whereas there is a
noticeable pause while #query: executes. Presumably this is due to the
database allocating and initialising the data structures it needs to
implement the dynamic/keyset driven cursor.

Fetching the rows in blocks rather than individually would also help, but
apart from in the remote case, I don't think it is going to make such a
marked difference.

A particularly unfortunate downside of the forward-only cursor is that one
has started navigating through it then it ties up the entire connection
(at least for SQL server), and hence one can't use the same connection for
more than one query at a time. In order to make this workable in conjunction
with the DB Connection one would probably have to look at deliberately
cloning connections for each forward-only result set.

I also experiment with ADO, and having generated the interfaces using the
ActiveX Component Wizard I implemented a #do: like this:

!ADODBRecordset15 methodsFor!

do: operation
 "Evaluate the <monadicValuable> argument, operation, for each of the
 records in the receiver."

 | fields |
 self MoveFirst.
 fields := self fields.
 [self eof] whileFalse: [
  operation value: fields.
  self MoveNext]! !
!ADODBRecordset15 categoriesFor: #do:!enumerating!public! !

I then evaluated:

rs := ADODB_Recordset new.
rs open: 'select * from LotsaRows' activeConnection: 'DSN=LocalServer'.
Time millisecondsToRun: [rs do:[:e | ]].

This isn't quite the same as DBResultSet>>rawDo: (in that the ADO itself is
decoding the data even if Dolphin isn't accessing it) and neither is it
quite like DBResultSet>>do:, because it itsn't actually converting the rows
to objects - in fact if one tried to use this to implement #collect: or
similar, then one would end up with a collection of last row since the
'fields' collection is updated in place as one navigates. Anyway, this runs
through the 100,000 row table in just over 4 seconds. Pulling the data out
into objects is a lot slower than with the DB connection, unfortunately,
mainly because everything has to be transmitted as a VARIANT and decoded.

ADO is, in fact, using a forward-only cursor here, and it doesn't permit
navigation backwards in the result set. If we instead evaluate:

rs := ADODB_Recordset new.
rs cursorType: adOpenKeyset.
rs open: 'select * from LotsaRows' activeConnection: 'DSN=LocalServer'.
Time millisecondsToRun: [rs do:[:e | ]].

This took over 80 seconds on my machine, so it seems to suffer even more
than Dolphin does when using a keyset driven cursor. For a dynamic cursor
(adOpenDynamic) the initial query runs more quickly, and the time drops to
just under 60 seconds. The last I heard, though, using dynamic cursors could
cause problems against Oracle DBs because it implements nothing other than
forward-only read-only cursors, and everything else has to be emulated by
the driver/driver manager and/or ADO itself.

ADO has a lot to be said for it and going forward I think it should become
the database connection mechanism of choice. We have considered modifying
the DB Connection package to use ADO, but unfortunately it doesn't really
"fit" from a design perspective and trying to make it do so would probably
compromise both. There is also some work to do to try and improve the
performance of converting to/from Smalltalk objects. It is likely that we
will ship an "official" ADODB package in the near future, but one can get by
pretty well with the output from the wizard.

Regards

Blair


-------------------------------------------------
| package |
package := Package name: 'DBForwardOnly'.
package paxVersion: 0;
 basicComment: 'Quick and dirty add-on to Dolphin Database Connection that
implements forward-only result sets, which are much faster for large
databases.

Blair McGlashan
Object Arts Ltd.
'.

package basicPackageVersion: ''.

"Add the package scripts"

"Add the class names, loose method names, global names, resource names"
package classNames
 add: #DBForwardOnlyCursor;
 add: #DBForwardOnlyResultSet;
 yourself.

package methodNames
 add: #DBConnection -> #exec:forwardOnly:;
 add: #DBConnection -> #query:forwardOnly:;
 yourself.

package globalNames
 yourself.

package resourceNames
 yourself.

"Binary Global Names"
package binaryGlobalNames: (Set new
 yourself).
"Resource Names"
package allResourceNames: (Set new
 yourself).

"Add the prerequisite names"
package setPrerequisites: (IdentitySet new
 add: 'Database Connection';
 yourself).

package!

"Class Definitions"!

DBResultSet subclass: #DBForwardOnlyResultSet
 instanceVariableNames: 'position'
 classVariableNames: ''
 poolDictionaries: ''
 classInstanceVariableNames: ''!
DBStatement subclass: #DBForwardOnlyCursor
 instanceVariableNames: ''
 classVariableNames: ''
 poolDictionaries: ''
 classInstanceVariableNames: ''!
"Loose Methods"!

!DBConnection methodsFor!

exec: aString forwardOnly: aBoolean
 "Execute the SQL in aString, answering a dynamic or forward-only cursor
 depending on the <boolean> argument."

 | statement |
 statement := (aBoolean ifTrue: [DBForwardOnlyCursor] ifFalse:
[DBStatement]) parent: self.
 statement exec: aString.
 ^statement!

query: aString forwardOnly: aBoolean
 "Execute the SQL in aString.
 Answer a DBResultSet representing the resultant result set.
 If the <boolean> argument is true then the result set will
 support forward-only navigation, but will be substantially
 faster in consequence."

 ^(self exec: aString forwardOnly: aBoolean) results! !
!DBConnection categoriesFor: #exec:forwardOnly:!executing!public! !
!DBConnection categoriesFor: #query:forwardOnly:!executing!public! !

"End of package definition"!



DBForwardOnlyResultSet comment: ''!

DBForwardOnlyResultSet guid: (GUID fromString:
'{BDEB3BCD-B383-4BBB-A02A-E63B31338641}')!

!DBForwardOnlyResultSet categoriesForClass!ODBC Interface! !
!DBForwardOnlyResultSet methodsFor!

at: anInteger
 "Answer the row at index anInteger in the receiver's result set"

 position = anInteger ifFalse: [
  anInteger < 1 ifTrue: [^self errorSubscriptBounds: anInteger].
  position > anInteger ifTrue: [self requery].
  [position < anInteger] whileTrue: [self moveNext isNil ifTrue: [^self
errorSubscriptBounds: anInteger]]].
 ^buffer asObject!

basicFirst
 "Private - Answer the first row of the receiver"

 ^self moveFirst isNil
  ifFalse: [buffer asObject]!

basicLast
 "Private - Answer the last row of the receiver's result set."

 [self moveNext notNil] whileTrue.
 ^position > 0 ifTrue: [buffer asObject]!

basicNext
 "Private - Answer the next row of the receiver's result set"

 ^self moveNext isNil
  ifFalse: [buffer asObject]!

basicPrev
 "Private - Answer the previous row of the receiver's result set.
 Implementation Note: This will be very slow in a large record set since
 we have to start from the beginning again."

 ^self at: position - 1!

bind: anArrayOfColNums
 "Private - Create and bind a buffer to hold the
 specified columns (or #() for all columns)
 as they are fetched from the result set"

 super bind: anArrayOfColNums.
 position := 0.!

moveFirst
 "Private - If not at the start of the result set, then re-exec the
statement."

 ^position == 1
  ifTrue: [0]
  ifFalse: [
   position > 0 ifTrue: [self requery].
   self moveNext]!

moveNext
 "Private - Advance to the next row in the result set."

 | status |
 status  := self xFetch: SQLFetchNext at: 0.
 status notNil ifTrue: [position := position + 1].
 ^status!

next
 "Answer the next row of the receiver's result set"

 ^self basicNext!

prev
 "Answer the previous row of the receiver's result set"

 ^self basicPrev!

rawDo: aZeroArgBlock
 | status |
 status := self moveFirst.
 [status isNil] whileFalse: [
  aZeroArgBlock value.
  status := self moveNext]!

requery
 self statement close.
 position := 0! !
!DBForwardOnlyResultSet categoriesFor: #at:!accessing!public! !
!DBForwardOnlyResultSet categoriesFor: #basicFirst!enumerating!private! !
!DBForwardOnlyResultSet categoriesFor: #basicLast!enumerating!private! !
!DBForwardOnlyResultSet categoriesFor: #basicNext!enumerating!private! !
!DBForwardOnlyResultSet categoriesFor: #basicPrev!enumerating!private! !
!DBForwardOnlyResultSet categoriesFor: #bind:!binding!private! !
!DBForwardOnlyResultSet categoriesFor: #moveFirst!operations!private! !
!DBForwardOnlyResultSet categoriesFor: #moveNext!operations!private! !
!DBForwardOnlyResultSet categoriesFor: #next!enumerating!public! !
!DBForwardOnlyResultSet categoriesFor: #prev!enumerating!public! !
!DBForwardOnlyResultSet categoriesFor: #rawDo:!enumerating!private! !
!DBForwardOnlyResultSet categoriesFor: #requery!operations!private! !



DBForwardOnlyCursor comment: ''!

DBForwardOnlyCursor guid: (GUID fromString:
'{EF6CCEE7-ACDE-44BD-A479-849B0CE671AF}')!

!DBForwardOnlyCursor categoriesForClass!ODBC Interface! !
!DBForwardOnlyCursor methodsFor!

allocatedHandle
 "Private - Answer the receiver's ODBC statement handle, which is lazily
 allocated if necessary."

 handle isNil ifTrue: [
  executed := false.
  handle := parent allocStmtHandle: self.
  self beFinalizable.
  "The default cursor type is forward only, so leave as is"].
 ^handle!

results
 "Answer a DBResultSet that manages the results for the receiver.
 The result set will cause the receiver to be lazily executed when
 any attempt is made to access its contents."

 ^DBForwardOnlyResultSet statement: self.! !
!DBForwardOnlyCursor categoriesFor: #allocatedHandle!accessing!private! !
!DBForwardOnlyCursor categoriesFor: #results!accessing!public! !


"Binary Globals"!

"Resources"!

"Blair McGlashan" <[hidden email]> wrote in message news:<9naqcf$6jg75$[hidden email]>...

I compared with VisualWorks Smalltalk ODBCSession, idential query
answering 3600 records from SLQ Server database across the puddle:
Visual Works 5i3 commercial   10 seconds
Dolphin      4.01 trial       790 seconds

VisualWorks ad-hoc query tool lets one set the buffer size to the
driver, I set it at 1 and 10 and observed no difference. So I don't
know what's going on here, except out of the box two orders of
magnitude difference performance.

> Oddly this has (as far as I recall) only come up that once before (in a
> report from Dmitry Zamotkin), and so I while we have an enhancement entry in
> our bug-tracking database to add block fetch support, at the moment there is
> no set timescale for it to be actioned. The priority of any enhancement is
> mainly a commercial decision based on demand, etc, but we are open to
> lobbying and/or specific contractual arrangements :-).

So if Baan funds the work Baan owns it, right<g>. For now I'll locate
the Dolphin image next to the database server and avoid the
Denver-Holland hop. Meanwhile I appeal to your sense of professional
honor.

Match VisualWorks ODBC performance.

-Alan Wostenberg, Baan

> If you install the attached package (which is a quick'n'dirty implementation
> of forward-only result sets that doesn't modify any existing DB Connection
> methods), and evaluate sql..

> You should find that it is at least an order of magnitude faster  - in my
> experiments in Dolphin 5 against a 100,000 row MS SQL Server 7.0 table the
> time to enumerate the entire recordset dropped from 52 seconds to just over
> 2 (in D4 it went down to about 3.7 seconds). You may also notice that the
> initial #query:forwardOnly: runs almost instantaneously, whereas there is a
> noticeable pause while #query: executes. Presumably this is due to the
> database allocating and initialising the data structures it needs to
> implement the dynamic/keyset driven cursor.

I applied and the remote access query dropped answering 3700 rows from
771 seconds to 5.4.
 
You just gave us remoters two orders of magnitude performance boost.
WELL DONE BLAIR!

I'm speculating the forward-only cursor enables some sort of buffering
read-ahead to avoid the network latency, so would not bother with
block fetches.

> ADO is, in fact, using a forward-only cursor here, and it doesn't permit
> navigation backwards in the result set. If we instead evaluate:

ADO is strategic according to Rockford Lhotka in his MS Press book "VB
 6 business objects" p118 :

" In general ADO is the preferred data access technology. All of the
other data technologies (RDO, ODBC or ODBCDirect) continue to be
supported, but Microsoft is putting their development efforts entirely
towards improving and enhancing ADO (and OLE DB, its underlying
technology). "

Greatfully,
-Alan Wostenberg, Baan

"Alan Wostenberg" <[hidden email]> wrote in message
news:[hidden email]...
> I am using Dolphin 4 trial version to do a concept proof before
> converting over a production Visual Works Smalltalk and several VB
> apps.

Alan, I can understand why you might want to replace VB apps with Dolphin
but what caught my eye was that you plan to convert a VW app to Dolphin.
Would you be willing to discuss the factors involved and what led to your
decision?


-- Frank
[hidden email]

Blair,

> I suspect the Java code is using a "forward-only"
> cursor (also sometimes call a firehose cursor) by default, which is very
> cheap for the DB to implement, especially for a simple select statement
with
> no joins. Perhaps someone who has the source (and a stronger stomach than
> me) could take a look at it and see what the Java library actually does.

The code for the JDBC/ODBC bridge doesn't seem to be part of the standard
distribution, but the definition of a java "Connection" object states that:

    Result sets created using the returned Statement object
    will by default have forward-only type and read-only concurrency.

> If you install the attached package [...]
> You should find that it is at least an order of magnitude faster

Brilliant!

Yes, the time dropped to 2.06 seconds (from 43).

BTW, forcing Java to use non-forward only cursors took the Java loop's time
up from 0.6 seconds (1.2 when interpeted) to 25 seconds (30 interpreted).
So that, too, confirms the effect.  There's still a small performance
difference -- not large enough to care about, I'd say -- I suspect it may be
due to the way the Java implementation uses "read-only concurrency" by
default.  I wasn't able to find a way (that worked) to change that, though,
so I can't confirm it.

> A particularly unfortunate downside of the forward-only cursor is that one
> has started navigating through it then it ties up the entire connection
> (at least for SQL server), and hence one can't use the same connection for
> more than one query at a time. In order to make this workable in
conjunction
> with the DB Connection one would probably have to look at deliberately
> cloning connections for each forward-only result set.

Personally, I'd be quite happy just to have the option of using forward-only
cursors and live with (or code around) the restriction.

> ADO has a lot to be said for it and going forward I think it should become
> the database connection mechanism of choice. We have considered modifying
> the DB Connection package to use ADO, but unfortunately it doesn't really
> "fit" from a design perspective and trying to make it do so would probably
> compromise both. There is also some work to do to try and improve the
> performance of converting to/from Smalltalk objects. It is likely that we
> will ship an "official" ADODB package in the near future, but one can get
by
> pretty well with the output from the wizard.

Hmm...  We used ADO originally at my work, but we switched (at considerable
cost in dev time) to ODBC.  I'm not a database man so I'm not sure of the
details, but there were problems with performace (we have databases in the
TerraByte range and up), and thread-friendlyness (I seem to remember being
told that ADO was designed only with VB in mind and doesn't "like" the kind
of unrestricted threading we were doing in C++).   Anyway, I thought OLE-DB
was Microsoft's current recommendation for Real Programmers ?

> Blair

    -- chris

"Frank Sergeant" <[hidden email]> wrote in message news:<UUfo7.6486$[hidden email]>...
Frank, the key factors are lower entry barrier in the Baan shop
because Dolphin's (a) licensing price (b) affinity for MS Windows.

Licensing. I'm looking at our annual VisualWorks renewal due now at
$5,000k/seat the terms of which are renew, or disconnect the live
application. Vs Dolphin under $500 perpetual license. VisualWorks is
great product not ten times the value of Dolphin.

Affinity for MS Windows. Three years ago Baan announced a
Microsoft-centric strategy. Since then any product that is not tightly
tracking relevant Windows technologies suffers a black mark. This is
not only about native widgets look and feel, but also Active-X, ADO,
OLE DB, Visual Source Safe. Plus whatever future Technologies
Microsoft puts forth (like Micorosft.NET). Score for VisualAge and
Dolphin. To your average VB guy Dolphin is less alien than VW which
lets me focus on Smalltalk itself in presentations.

For example, yesterday after Dolphin demos via Netmeeting a technician
asked me "is Smalltalk hard to learn?". I said "no". But years ago I
would have quoted the standard line "it takes about 6 months to come
up to speed but it's worth it". This "It's easy" message I learned not
from Cincom nor IBM but from little Object Arts, and I've come to see
it is true. Smalltalk is as easy as VB only more scalable because of
it's "model first" approach.

If our shop still officially sanctioned Smalltalk and platform
portability, VW price would not be an issue and I would ignored the
others. But one must pick his battles. "Up with Smalltalk" is one I'll
take on at a personal level; "down with Microsoft" is not. Yet.

Baan's Last Smalltalker,
-Alan Wostenberg,

"Alan Wostenberg" <[hidden email]> wrote in message
news:[hidden email]...

> Frank, the key factors are lower entry barrier in the Baan shop
> because Dolphin's (a) licensing price (b) affinity for MS Windows.

Alan, thanks for discussing these issues.

> Licensing. I'm looking at our annual VisualWorks renewal due now at
> $5,000k/seat the terms of which are renew, or disconnect the live
> application.

I myself have had serious reservations about Cincom's licensing (and public
disclosure of the terms thereof) policies.  At first the way I read the
license (the VAR license) was that Cincom could basically unilaterally
renegotiate the license at their pleasure and also that once you quit paying
you couldn't use the product thereafter, as opposed to say damn near
anything else, such as Delphi or Dolphin where I could still use an older
version I had purchased even if I did not elect to purchase upgrades.  But,
I thought James Robertson tried to clarify that in comp.lang.smalltalk
saying that you *could* continue to use your then-current version of
Smalltalk after you stopped paying, you just wouldn't be entitled to upgrade
to the newer versions.  Any chance that line of reasoning might apply to
you?  *One* of the barriers that Cincom seems to present to *me* is the
confusion and uncertainty and complexity of the licensing.

> Vs Dolphin under $500 perpetual license. VisualWorks is
> great product not ten times the value of Dolphin.

Good point.

> Affinity for MS Windows. Three years ago Baan announced a
> Microsoft-centric strategy. Since then any product that is not tightly
> tracking relevant Windows technologies suffers a black mark.

I swing madly back and forth between hating Windows and MS and merely
disliking them a great deal.  Unfortunately, I think my customers tend to
have a policy (perhaps not as strong or as articulated) similar to Baan's.
Thanks (I guess) for helping to keep me aware of which side of my bread is
buttered.

> Baan's Last Smalltalker,


-- Frank

"Frank Sergeant" <[hidden email]> wrote in
news:sigp7.6924$[hidden email]:

> I swing madly back and forth between hating Windows and MS and merely
> disliking them a great deal.  

Which of the 5 phases are you in?
:-) http://www.develop.com/dbox/cxx/fivephases.htm

Alan

You wrote in message news:[hidden email]...
> > If you install the attached package (which is a quick'n'dirty
implementation
> > of forward-only result sets that doesn't modify any existing DB
Connection
> > methods), and evaluate sql..
>
> > You should find that it is at least an order of magnitude faster  - in
my
> > experiments in Dolphin 5 against a 100,000 row MS SQL Server 7.0 table
the
> > time to enumerate the entire recordset dropped from 52 seconds to just
over
> > 2 (in D4 it went down to about 3.7 seconds). You may also notice that
the
> > initial #query:forwardOnly: runs almost instantaneously, whereas there
is a
> > noticeable pause while #query: executes. Presumably this is due to the
> > database allocating and initialising the data structures it needs to
> > implement the dynamic/keyset driven cursor.
>
> I applied and the remote access query dropped answering 3700 rows from
> 771 seconds to 5.4.
>
> You just gave us remoters two orders of magnitude performance boost.
> WELL DONE BLAIR!

I'm glad it helped. A slightly revised implementation of it will appear in
the next release.

With this enhancement, how do you find that performance compares with VB and
VW?

>
> I'm speculating the forward-only cursor enables some sort of buffering
> read-ahead to avoid the network latency, so would not bother with
> block fetches.

Yes, I would think so. Although block fetch would probably buy some
improvement, I doubt it would be as significant.

Yes, that is clear. ADO has a lot going for it, but unfortunately its
orientation toward VB compromises its performance potential in other
languages for which VARIANT is not a (the) native data type. Although OLE DB
doesn't have that problem, it is significantly lower-level abstraction, and
thus way to complex for direct use in a typical application. What would be
nice would be an abstraction like ADO that was more language neutral. That
is not to say that ADO doesn't work perfectly well in Dolphin, its just that
the overhead of the ADO layer converting everything into VARIANTs, and then
Dolphin converting them back again (combined with having to do this
field-by-field) makes the reification of rows much slower than through ODBC.

Regards

Blair

> I'm glad it helped. A slightly revised implementation of it will appear in
> the next release.
>
> With this enhancement, how do you find that performance compares with VB and
> VW?

In my remote query speed Dolphin slightly exceeds VisualWorks.
VisualWorks has a memory leak bug in it's ODBC driver precluding 24x7
operation I'm hopping Dolphin doesn't suffer. I don't have
comparisions with VB.

Some very minor downsides:
 - Dolphin's DBConnection>>tables method crawls and presumably would
benefit from the technique.
 - I assume for production you fixed the bug in DBResultSet>>do: which
throws throws a walkback when zero results.
 - Unlike VW, Dolphin UI is nonresponsive during these tight ODBC
iterators over the WAN - not even a polite hourglass, and tracing to
the Transcript from the iteration loop is kind of herky-jerky.
 - I did find the DBResultSet>>size method takes as long as the query
itself, a characteristic I don't recall from VisualWorks. So progress
bars on long queries, I found it best to do a sql COUNT() to first get
the size to initialize progress bar, then reissue full query to fetch
results. row by row.

These are minor and overall Dolphin works just fine over the WAN with
the fix.

-Alan

"Paul Hudson" <[hidden email]> wrote in message
news:Xns911FBE939FDE7phudsonpoboxcom@127.0.0.1...
> "Frank Sergeant" <[hidden email]> wrote in
> news:sigp7.6924$[hidden email]:
>
> > I swing madly back and forth between hating Windows and MS and merely
> > disliking them a great deal.
>
> :-) http://www.develop.com/dbox/cxx/fivephases.htm

Thanks for the humor.

> Which of the 5 phases are you in?

I certainly recognized some of the aspects of some of the phases but I am
not quite prepared to let the enemy define the matter quite so
simplistically <wink>.


-- Frank
[hidden email]

Alan

"Alan Wostenberg" <[hidden email]> wrote in message
news:[hidden email]...
> > I'm glad it helped. A slightly revised implementation of it will appear
in
> > the next release.
> >
> > With this enhancement, how do you find that performance compares with VB
and
> > VW?
>
> In my remote query speed Dolphin slightly exceeds VisualWorks.
> VisualWorks has a memory leak bug in it's ODBC driver precluding 24x7
> operation I'm hopping Dolphin doesn't suffer. ...

I'd imagine that will depend on whether the leak is in VW code, or the ODBC
driver itself. In the past we have found significant leaks in ODBC drivers,
particularly the Oracle ones. If the ODBC driver (or driver manager) leaks,
then it probably won't matter which client you use, unless it results from
the particular sequence of calls made by that client.

>...I don't have
> comparisions with VB.
>
> Some very minor downsides:
>  - Dolphin's DBConnection>>tables method crawls and presumably would
> benefit from the technique.

Possibly. Its a direct invocation of SQLTables(), so I can't think of any
other thing that Dolphin is doing that would add significant overhead to the
underlying API call. All of the schema statements have been modified to use
forward-only cursors for the next release, but I have no figures as to how
this affects the performance of querying the schema.

>  - I assume for production you fixed the bug in DBResultSet>>do: which
> throws throws a walkback when zero results.

Are we talking about a bug in the original or extended DB Connection
packages?

>  - Unlike VW, Dolphin UI is nonresponsive during these tight ODBC
> iterators over the WAN - not even a polite hourglass, and tracing to
> the Transcript from the iteration loop is kind of herky-jerky.

I don't know how it is implemented in VW, for example whether it has code
built into the VM or whether it is doing calls through DLL-C Connect. Either
way VW might be using "threaded" calls, an option that could be used in
Dolphin but which was not available when the DB Connection package was
originally written. There is a trade off, however, since using overlapped
calls (Dolphin's name for calls that are run on a thread other than the main
VM thread so that they do not block everything) is less efficient, and one
probably wouldn't want to introduce extra overhead for all cases, so the
best approach would probably be to use a bit of polymorphism to allow a
choice between non-blocking or blocking calls when querying and enumerating
a result set.

Dolphin doesn't display a wait cursor automatically, because that isn't done
by the VM, but in Smalltalk. If you are going to perform a long running
operation, you can wrap it up like this:
    Cursor wait showWhile: [...long running op...]

All processing initiated by UI commands is run inside such a block by
default, but at other times .

Output to the Transcript is not visible until you #flush it, or do certain
other operations such as #cr (which itself sends #flush). Also it is
dependent on the UI process being able to process paint messages from
Windows, which may not happen that regularly when one is in a tight loop. If
#flush alone doesn't help, it is possible to synchronously paint views by
sending them a #update message.

>  - I did find the DBResultSet>>size method takes as long as the query
> itself, a characteristic I don't recall from VisualWorks. So progress
> bars on long queries, I found it best to do a sql COUNT() to first get
> the size to initialize progress bar, then reissue full query to fetch
> results. row by row.

That's right. As the method comment mentions, often the driver is not able
to report the size of a query because the database itself does not report it
back. If you do a 'select * from X', then usually the DBMS will report the
number of rows, since presumably it has that information readily to hand. If
however the query is more complex then depending on the method it uses to
build the result set, it may not know the number of results in advance. In
those cases the SQLRowCount() function to return -1, and Dolphin has to
calculate the size by enumerating the entire result set. It is a good idea
to avoid using #size, and instead write one's code using dynamic
collections, etc, which is in fact what DBResultSet itself does in order to
make if function more efficiently as a subclass of SequenceableCollection.

The same really applies to the use of 'SELECT COUNT(...' as well, since the
DBMS itself may need to enumerate the entire result set in order to
calculate the number of rows (though that depends very much on the query,
one can look at the execution plan to see exactly what it will need to do).
Of course this will be faster, since it takes place in the DBMS itself, but
may still involve a great deal of computation, so if you don't really need
the exact size up front it would be better not to request it. On the other
hand you may have no other option for a progress dialog if there is no way
to make a reasonable guess.

> These are minor and overall Dolphin works just fine over the WAN with
> the fix.

Good.

Regards

Blair