ODBC Performance

I am learning Dolphin. I tried part of an app I had built in VB.

In VB I retrieve 6500 Name and Address records from a (SQL Server) Database.
I display it in a DataGrid. It takes < 1 second.

In Dolphin I use ODBC to execute the SQL and Inspect or Display, and it
takes 6-7 seconds!

Am I doing something wrong? Maybe Dolphin is not for this database world (I
hope not since I just bought it)?

Alex

"Alex Peake" <alexpSPAM FREE@waybettersoftwareSPAM FREE.com> wrote in
message news:[hidden email]...
> I am learning Dolphin. I tried part of an app I had built in VB.
>
> In VB I retrieve 6500 Name and Address records from a (SQL Server)
Database.
> I display it in a DataGrid. It takes < 1 second.
>
> In Dolphin I use ODBC to execute the SQL and Inspect or Display, and it
> takes 6-7 seconds!
>
> Am I doing something wrong? Maybe Dolphin is not for this database world
(I
> hope not since I just bought it)?

Do a http://groups.google.com search on this group to find a good bit of
discussion about Dolphin database performance.  I have done a good deal of
database work with Dolphin and I am quite pleased with it.  However certain
situations require doing things a little differently than in VB.

How long does the query take if you do not inspect or display the results,
just do Evaluate (Ctrl-E)?  It is possible that displaying the results is
causing the query to retrieve more results (maybe all) than would normally
be initially populated in a VB virtual DataGrid.

Let me repeat something I previously posted because it may be relevant here:
"I recently wrote a database explorer type tool in Dolphin.  At first I was
putting a DBResultSet directly in a list box.  The performance of that was
slow beyond all belief!  I worked on a caching wrapper to buffer the data.
I still found it to be very slow.  I found that the database was doing some
heavy lifting every time the list box asked for the size (quite often).  I
then simply cached the size of the recordset and the performance improved
massively.  If Powerbuilder is anything like MS Access, and you are used to
throwing query results directly into a list box you may be unimpressed if
you try the same in Dolphin.  It is easy to workaround just by caching the
size (or a better way that someone else should feel free to chime in with).
Of course simply throwing query results into a list box may not be the best
design in Smalltalk anyway."

If for some reason you can not get acceptable performance out of ODBC then
you could try using ADO in Dolphin.  Let us know how this goes for you.

Chris

Thanks for your help Chris.It is quite fast enough on Evaluate.

Problems begin when I try to do something with the data. Inspect or Display
takes 5-6 seconds. Even:

rs collect: [:x | x Last_Name]. "Evaluate"

takes 5.5 seconds!

Can you suggest a better performing technique for doing something with the
recovered data?

Thanks,

Alex


"Christopher J. Demers" <[hidden email]> wrote in
message news:bn2bac$rics5$[hidden email]... certain
> situations require doing things a little differently than in VB.
>
> How long does the query take if you do not inspect or display the results,
> just do Evaluate (Ctrl-E)?  It is possible that displaying the results is
> causing the query to retrieve more results (maybe all) than would normally
> be initially populated in a VB virtual DataGrid.
>
> Let me repeat something I previously posted because it may be relevant
here:
> "I recently wrote a database explorer type tool in Dolphin.  At first I
was
> putting a DBResultSet directly in a list box.  The performance of that was
> slow beyond all belief!  I worked on a caching wrapper to buffer the data.
> I still found it to be very slow.  I found that the database was doing
some
> heavy lifting every time the list box asked for the size (quite often).  I
> then simply cached the size of the recordset and the performance improved
> massively.  If Powerbuilder is anything like MS Access, and you are used
to
> throwing query results directly into a list box you may be unimpressed if
> you try the same in Dolphin.  It is easy to workaround just by caching the
> size (or a better way that someone else should feel free to chime in
with).
> Of course simply throwing query results into a list box may not be the
best
> design in Smalltalk anyway."
>
> If for some reason you can not get acceptable performance out of ODBC then
> you could try using ADO in Dolphin.  Let us know how this goes for you.
>
> Chris
>
>

"Alex Peake" <alexpSPAM FREE@waybettersoftwareSPAM FREE.com> wrote in
message news:[hidden email]...
> Thanks for your help Chris.It is quite fast enough on Evaluate.
>
> Problems begin when I try to do something with the data. Inspect or
Display
> takes 5-6 seconds. Even:
> rs collect: [:x | x Last_Name]. "Evaluate"
> takes 5.5 seconds!
>
> Can you suggest a better performing technique for doing something with the
> recovered data?

Lets make sure we are comparing apples to apples first.  I assume that the
DataBound grid in VB is virtual.  That means it probably only gets and
displays the records that are visible.  So while your query in VB returns
6500 records only the first X records will initially be retrieved and
displayed.  Where X is the number of rows visible in the DataBound grid.  So
perhaps your VB code is getting 50 records in ~ one second, whereas the
Smalltalk collect: is getting all 6500 records in ~6 seconds.  So we are
comparing different things I think.

As a further expiriment try this (obviosuly use your query and field name
here):
=====================
[col := OrderedCollection new.
(odbcCon query: 'SELECT * from MEASURE') from: 1 to: 50 do: [:each | col
add: (each at: 'INST_CLASS_')]] millisecondsToRepeat: 1.
=====================

I expect that the performance may be more in-line with what you see in your
VB DataBound grid.

Now, if you really do need to iterate through all records and want to
improve performance then you might consider using a forward only recordset.
You could experiment with DBConnection<<query:forwardOnly: to see if there
is a performance gain.

If you perform many queries or updates in loops you will likely need to
consider using precompiled queries to get decent performance.  If I have to
do something like this I will generally try to use ADO rather than ODBC for
this reason.

I am not sure what your Smalltalk experience level is, but since you are
coming from VB I will mention this:  One certainly could write a Smalltalk p
rogram like a VB program.  However I suspect one would be missing out on
many of Smalltalk's advantages over VB, namely real OOP.  Consider wrapping
your tables and records in objects.  You could use an object database (
http://www.gorisek.com/homepage/WOB-e9u7Om1BAMZD3hb61GvQSAc0-1-3.html?action=omnibase )
, or a object to relational mapper (
http://www.solutionsoft.co.uk/restore/ ).  I use ReStore, and have been very
pleased with it.  These tools are worth a look.

Let us know how all this goes.

Chris

"Alex Peake" <alexpSPAM FREE@waybettersoftwareSPAM FREE.com> wrote in message news:<[hidden email]>...
Alex,

I think what Chris was trying to get at is that VB very likely doesn't
pull in all 6500 rows when you first display the data grid (unless you
have a very, very large window), but instead pulls them in as needed
when you scroll.

Obviously that #collect: call has to retrieve the data, so it doesn't
suprise me that it's slow.

What if you try just taking the first 100 results or so, as a good UI
would?  I don't know how Dolphin result sets work, but what happens
with

(rs copyFrom: 1 to: 100) collect: [:x | x Last_Name].

?

Thanks for the help Avi.

Actually VB does get all 6500 rows at once and binds them to the grid (in
under one second). The grid is scrollable and you can zip up and down the
6500 virtually instantaneously. It turns out that this is a client
requirement -- they are looking for a Club Member and this is the quickest
way to get the one the want -> much less than a second to populate the grid
and drag the scroll bar until they see the one they want.

So what I am gathering from this is that when Dolphin executes the SQL
SELECT and binds to a record set, it has not brought back any of the records
from the database?

The secret seems to be the #forwardOnly cursor type! Now I get all records
in one second.

Thanks again,

Alex


"Avi Bryant" <[hidden email]> wrote in message
news:[hidden email]...
> "Alex Peake" <alexpSPAM FREE@waybettersoftwareSPAM FREE.com> wrote in
message news:<[hidden email]>...
> > Thanks for your help Chris.It is quite fast enough on Evaluate.
> >
> > Problems begin when I try to do something with the data. Inspect or
Display
> > takes 5-6 seconds. Even:
> >
> > rs collect: [:x | x Last_Name]. "Evaluate"
> >
> > takes 5.5 seconds!
> >
> > Can you suggest a better performing technique for doing something with
the

Thanks for all your help Chris,

Actually VB does get all 6500 rows at once and binds them to the grid (in
under one second). The grid is scrollable and you can zip up and down the
6500 virtually instantaneously. It turns out that this is a client
requirement -- they are looking for a Club Member and this is the quickest
way to get the one the want -> much less than a second to populate the grid
and drag the scroll bar until they see the one they want.

So what I am gathering from this is that when Dolphin executes the SQL
SELECT and binds to a record set, it has not brought back any of the records
from the database?

The secret seems to be the #forwardOnly cursor type! Now I get all records
in one second.

ADO, BTW, is rather "chatty" so ODBC in general outperforms it.

Thanks again,

Alex


And BTW, I was very familiar with Smalltalk 10 years ago. I am just getting
back into it!

"Christopher J. Demers" <[hidden email]> wrote in
message news:bn47ga$sje5h$[hidden email]... the
> > recovered data?
>
> Lets make sure we are comparing apples to apples first.  I assume that the
> DataBound grid in VB is virtual.  That means it probably only gets and
> displays the records that are visible.  So while your query in VB returns
> 6500 records only the first X records will initially be retrieved and
> displayed.  Where X is the number of rows visible in the DataBound grid.
So your
> VB DataBound grid.
>
> Now, if you really do need to iterate through all records and want to
> improve performance then you might consider using a forward only
recordset.
> You could experiment with DBConnection<<query:forwardOnly: to see if there
> is a performance gain.
>
> If you perform many queries or updates in loops you will likely need to
> consider using precompiled queries to get decent performance.  If I have
to
> do something like this I will generally try to use ADO rather than ODBC
for
> this reason.
>
> I am not sure what your Smalltalk experience level is, but since you are
> coming from VB I will mention this:  One certainly could write a Smalltalk
p
> rogram like a VB program.  However I suspect one would be missing out on
> many of Smalltalk's advantages over VB, namely real OOP.  Consider
wrapping
> your tables and records in objects.  You could use an object database (
>
http://www.gorisek.com/homepage/WOB-e9u7Om1BAMZD3hb61GvQSAc0-1-3.html?action=omnibase )
> , or a object to relational mapper (
> http://www.solutionsoft.co.uk/restore/ ).  I use ReStore, and have been
very
> pleased with it.  These tools are worth a look.
>
> Let us know how all this goes.
>
> Chris
>
>

Alex

You wrote in message news:3f95fbbd$[hidden email]...
> Thanks for all your help Chris,
>
> Actually VB does get all 6500 rows at once and binds them to the grid (in
> under one second). The grid is scrollable and you can zip up and down the
> 6500 virtually instantaneously. It turns out that this is a client
> requirement -- they are looking for a Club Member and this is the quickest
> way to get the one the want -> much less than a second to populate the
grid
> and drag the scroll bar until they see the one they want.

The DataGrid may give that appearance, but probably it is a virtual list in
order that it can support very large recordsets. Of course it is worth
bearing in mind that the DataGrid is not really VB, but is an external
control coded for a specific purpose and it is almost certainly written in
C/C++. To get a realistic comparison of performance comparing like with like
you would have to evaluate essentially the same loop in VB itself that you
did in Dolphin, e.g. by populating a listbox with a column from the
recordset using VB code.

BTW, if you have a license that allows you to ship the MS DataGrid (which
you would certainly have if you have a VB license), then you could use it in
Dolphin anyway.

>
> So what I am gathering from this is that when Dolphin executes the SQL
> SELECT and binds to a record set, it has not brought back any of the
records
> from the database?
>
> The secret seems to be the #forwardOnly cursor type! Now I get all records
> in one second.

For historical reasons Dolphin's DBConnection uses a #keysetDriven cursor
type by default. This is flexible, but can be expensive, particularly with
certain DBs that do not support it directly, which forces the ODBC layers to
emulate it. The #forwardOnly cursor type is likely to be the most
performant, but has certain limitations - for example against SQL Server
only a single such cursor can be active per connection at any one time.

The best compromise between flexibility and speed is provided by the
#dynamic cursor type, if natively supported by the database, which is not
often the case.

>
> ADO, BTW, is rather "chatty" so ODBC in general outperforms it.

Well, that depends on the application, and whether you are using a native
OLEDB driver, or going through the mediating OLEDB driver that can talk to
any ODBC database. In our experience ADO can offer the highest performance
solution when using native OLEDB drivers against SQL Server (for example),
but in any case there are other factors such as its flexibility and ease of
use that would make it our solution of choice when doing new work.

Regards

Blair

Thanks for sharing your experience Blair.

I will try the #dynamic cursor type.

I would also like to try ADO now, and see if I can bind to the ComponentOne
TrueDBGrid. I do not suppose there is a simple example anywhere of:
* Using ADO in Dolphin?
* Using an ActiveX frid in a Dolphin UI?
* Binding ADO recordsets to an ActiveX grid in Dolphin?

I would like to build my next app in Dolphin as a "proof of concept" of
Smalltalk in the Windows/RDBMS world.

BTW, is there any example of n-tier deployment - UI calling Business
componenets (calling Data Access componenets) on a central server (DCOM
maybe, or a "Remote" Smalltalk kind of thing)?

Thanks,

Alex

"Blair McGlashan" <[hidden email]> wrote in message
news:bn5fm4$t0jj1$[hidden email]...
> Alex
>
> You wrote in message news:3f95fbbd$[hidden email]...
> > Thanks for all your help Chris,
> >
> > Actually VB does get all 6500 rows at once and binds them to the grid
(in
> > under one second). The grid is scrollable and you can zip up and down
the
> > 6500 virtually instantaneously. It turns out that this is a client
> > requirement -- they are looking for a Club Member and this is the
quickest
> > way to get the one the want -> much less than a second to populate the
> grid
> > and drag the scroll bar until they see the one they want.
>
> The DataGrid may give that appearance, but probably it is a virtual list
in
> order that it can support very large recordsets. Of course it is worth
> bearing in mind that the DataGrid is not really VB, but is an external
> control coded for a specific purpose and it is almost certainly written in
> C/C++. To get a realistic comparison of performance comparing like with
like
> you would have to evaluate essentially the same loop in VB itself that you
> did in Dolphin, e.g. by populating a listbox with a column from the
> recordset using VB code.
>
> BTW, if you have a license that allows you to ship the MS DataGrid (which
> you would certainly have if you have a VB license), then you could use it
in
> Dolphin anyway.
>
> >
> > So what I am gathering from this is that when Dolphin executes the SQL
> > SELECT and binds to a record set, it has not brought back any of the
> records
> > from the database?
> >
> > The secret seems to be the #forwardOnly cursor type! Now I get all
records
> > in one second.
>
> For historical reasons Dolphin's DBConnection uses a #keysetDriven cursor
> type by default. This is flexible, but can be expensive, particularly with
> certain DBs that do not support it directly, which forces the ODBC layers
to of
> use that would make it our solution of choice when doing new work.
>
> Regards
>
> Blair
>
>

"Alex Peake" <alexpSPAM FREE@waybettersoftwareSPAM FREE.com> wrote in
message news:3f969ede$[hidden email]...

> I would also like to try ADO now, and see if I can bind to the
ComponentOne
> TrueDBGrid. I do not suppose there is a simple example anywhere of:
> * Using ADO in Dolphin?

Here is a quick ADO example, I will leave the other examples for others.  Be
advised that while ADODB_Recordset has a method named collect: it does not
appear to be what a Smalltalker would expect, but rather an ADO property.
Also some messages start with uppercase letters, and some follow Smalltalk
naming convention.

"ADO"
adoCon :=ADODB_Connection new connectionString: 'DSN=GenericDSN;'; open;
yourself.
rs:=(ADODB_Recordset new) open: 'SELECT * from MEASURE'
activeConnection: adoCon
cursorType: (ADODBConstants at: #adOpenDynamic )
lockType: (ADODBConstants at: #adLockOptimistic )
options: (ADODBConstants at: #adCmdUnspecified);
yourself.
"Get values via do"
col := OrderedCollection new.
rs do: [:eachFields | col add: (eachFields item: 'INST_CLASS_') value].
"Get a value from the first record."
rs MoveFirst.
(rs fields item: 'INST_CLASS_') value.

Chris

Thanks once again Chris.

I conclude that performance is about the same as the ODBC (again use
#adOpenForwardOnly).

I wonder if binding to an ActiveX is simpler, since the TrueDBGrid is
expecting an ADO Recordset (as one option)? Although maybe it is too late --
already converted to a Smalltalk object? I have no experience with ActiveX
in Dolphin (or any other Smalltalk for that matter).

Alex

"Christopher J. Demers" <[hidden email]> wrote in
message news:bn6sph$tbeb1$[hidden email]...
> "Alex Peake" <alexpSPAM FREE@waybettersoftwareSPAM FREE.com> wrote in
> message news:3f969ede$[hidden email]...
>
> > I would also like to try ADO now, and see if I can bind to the
> ComponentOne
> > TrueDBGrid. I do not suppose there is a simple example anywhere of:
> > * Using ADO in Dolphin?
>
> Here is a quick ADO example, I will leave the other examples for others.
Be

Alex

You wrote in message news:3f9745c8$[hidden email]...
> Thanks once again Chris.
>
> I conclude that performance is about the same as the ODBC (again use
> #adOpenForwardOnly).

In addition to Chris' example perhaps I should just comment that the
documentation for ADO in Dolphin is essentially just the normal ADO
documentation on MSDN. Translation of the VB examples is a mechanical
process once you understand the way in which Dolphin's type library analyzer
maps COM interfaces, methods and constants into Smalltalk classes, methods,
and pool dictionaries. Unlike VB, however, Dolphin always deals in terms of
the interfaces, so there are no objects explicitly representing the
coclasses in the library. With that in mind an interface you see in the
documentation can be found in Dolphin using the class browser. All of the
member functions in the interface will have corresponding wrapper methods in
the Smalltalk class. There will be low-level methods that directly invoke
the COM member function at the finest level of detail (which is hidden from
you in VB), and higher level methods that wrap these to make them more
Smalltalk friendly. The low-level methods will have the same names as the
member functions, even down to the casing (hence the methods with an
uppercase first letter, against the normal Smalltalk convention), but of
course in Smalltalk all the parameters are named by subsequent keywords with
these being pulled from the type library and so probably corresponding with
those in the documentation. The higher-level methods will follow normal
Smalltalk naming conventions, and these wrap up the inputs and outputs so
that "normal" Smalltalk objects can be passed in and out.

In many ways the COM support in Dolphin is analogous to that provided by the
#import mechanism in VC++ if you are familiar with that.

>
> I wonder if binding to an ActiveX is simpler, since the TrueDBGrid is
> expecting an ADO Recordset (as one option)? Although maybe it is too
late --
> already converted to a Smalltalk object? I have no experience with ActiveX
> in Dolphin (or any other Smalltalk for that matter).

The ADODB_Recordset object is a real COM object (well an interface onto
one), and so you will be able to pass it directly to any other COM object
that expects an ADO recordset.

Regards

Blair