RE: Database Connection

"Jerry Bell" <[hidden email]> wrote in message:
news:98fvc4$1q53n$[hidden email]...

> Make sure you get Ian's profiler ...

Already my first-aid set...


> Running an explicit garbageCollect every few hundred rows helps.

It not help but slows down.

I've made another test. I've created link to Oracle ODBC source in MS
Access. Speed of fetching in Access is same as in native Oracle workspace
(SQL+) and 5 times faster then Dolphin Database Connection.


--
Posted from info.tvcom.ru [194.87.73.17]
via Mailgate.ORG Server - http://www.Mailgate.ORG

"Dmitry Zamotkin wrote:

> I've made another test. I've created link to Oracle ODBC source in MS
> Access. Speed of fetching in Access is same as in native Oracle workspace
> (SQL+) and 5 times faster then Dolphin Database Connection.

I've seen surprisingly poor performance too.  In my case I was fetching
largish amounts of data (~100K rows) from a SQL-Server 7 database.  The
query loaded data into Dolphin a couple of orders of magnitude slower than
M$s "Query Analyser" tool could read it into its output text window.

I've never gone back to look into why it was so slow, though.  A great deal
of it must be to do with the difference between whatever low-level interface
Query Analyser uses to SQL-Server, and poor old ODBC.   I *did* run the
profiler on it, and there were no obvious hotspots (well, not once I
realised I was better off getting the column indexes once at the start,
rather than accessing everything by name).  I was doing some processing on
the data too (naturally), but not much, and that didn't seem to be a hotspot
either.

    -- chris

Chris

"Chris Uppal" <[hidden email]> wrote in
message news:[hidden email]...
> "Dmitry Zamotkin wrote:
>
> > I've made another test. I've created link to Oracle ODBC source in MS
> > Access. Speed of fetching in Access is same as in native Oracle
workspace
> > (SQL+) and 5 times faster then Dolphin Database Connection.
>
> I've seen surprisingly poor performance too.

I'm not sure that 5 times slower than a dedicated DB tool written in C would
necessarily count as 'surprisingly poor performance'.

>...In my case I was fetching
> largish amounts of data (~100K rows) from a SQL-Server 7 database.  The
> query loaded data into Dolphin a couple of orders of magnitude slower than
> M$s "Query Analyser" tool could read it into its output text window.

However a "couple of orders of magnitude" surely does, and I might well be
surprised at that, although I would still expect the native tool to be
substantially faster.

>
> I've never gone back to look into why it was so slow, though.  A great
deal
> of it must be to do with the difference between whatever low-level
interface
> Query Analyser uses to SQL-Server, and poor old ODBC.   I *did* run the
> profiler on it, and there were no obvious hotspots (well, not once I
> realised I was better off getting the column indexes once at the start,
> rather than accessing everything by name).  I was doing some processing on
> the data too (naturally), but not much, and that didn't seem to be a
hotspot
> either.

Which version of Dolphin was this in? There was a particular problem with
enumerating some result sets in 3.0 because DBResult set frequently ran
through the rows twice, once to count the number of rows, and once for the
actual loop. This was a minor error in the integration into the collection
hierarchy (although it may be that it resulted from a change to the
colleciton hierarchy after the DB package was written).

Further optimization would always be handy of course, but when I compared
the speed of the Dolphin DB connection against accessing the same database
via ADO the former was substantially faster. Anyway getting back to the
original subject of the thread, I'm not at all convinced that retrieving
more than one row at a time from the underlying result set would necessarily
improve performance that much. As someone pointed, when doing performance
work out it is all too easy to jump to the wrong conclusion and expend a
great deal of effort for little real gain.

Regards

Blair

Blair,

> I'm not sure that 5 times slower than a dedicated DB tool written in C
would
> necessarily count as 'surprisingly poor performance'.

No, I agree.

> However a "couple of orders of magnitude" surely does, and I might well be
> surprised at that, although I would still expect the native tool to be
> substantially faster.

It was a sort of seconds to minutes thing.  3 secs in QA, 3 mins in Dolphin.

> Which version of Dolphin was this in?

D4 pl 1.

> Further optimization would always be handy of course, but when I compared
> the speed of the Dolphin DB connection against accessing the same database
> via ADO the former was substantially faster.

Something odd to do with my particular datasource ?  It was a server running
on the same twin CPU machine.  Reasonable amount of RAM.  Nothing special in
paging behaviour going on.

I've seen the same thing on a different box with 4 CPUs and a couple of Gig
of RAM.  (These, sadly, are not my home machines...)

> Anyway getting back to the
> original subject of the thread, I'm not at all convinced that retrieving
> more than one row at a time from the underlying result set would
necessarily
> improve performance that much. As someone pointed, when doing performance
> work out it is all too easy to jump to the wrong conclusion and expend a
> great deal of effort for little real gain.

Agreed.  Or for none at all.  (Did I ever tell you about the time I tried
re-writing an innermost-loop in assembler...?)

I just though I'd mention that I'd seen something odd too, so that you'd
know there was a *potential* issue here.  I'm not screaming for a "fix".  If
it'd affected me that badly I'd have looked more deeply into it (and started
screaming ;-)

I will take a second look if I get the time, but it's unlikely.

> Blair

    -- chris

"Blair McGlashan" <[hidden email]> wrote:
> Which version of Dolphin was this in?

4.0 pl 1

> Further optimization would always be handy of course, but when I compared
> the speed of the Dolphin DB connection against accessing the same database
> via ADO the former was substantially faster. Anyway getting back to the
> original subject of the thread, I'm not at all convinced that retrieving
> more than one row at a time from the underlying result set would
necessarily
> improve performance that much. As someone pointed, when doing performance
> work out it is all too easy to jump to the wrong conclusion and expend a
> great deal of effort for little real gain.

I have compared speed of fetching for same sql statement "select * from
table_name" with Dolphin and ObjectStudio 6.3, 20000 rows. The result is
same: ObjectStudio is 5 times FASTER.

Dmitry

Dmitry

You wrote in message news:98n2h6$a8b$[hidden email]...
>
> "Blair McGlashan" <[hidden email]> wrote:
> > Which version of Dolphin was this in?
>
> 4.0 pl 1
>
> > Further optimization would always be handy of course, but when I
compared
> > the speed of the Dolphin DB connection against accessing the same
database
> > via ADO the former was substantially faster. Anyway getting back to the
> > original subject of the thread, I'm not at all convinced that retrieving
> > more than one row at a time from the underlying result set would
> necessarily
> > improve performance that much. As someone pointed, when doing
performance
> > work out it is all too easy to jump to the wrong conclusion and expend a
> > great deal of effort for little real gain.
>
> I have compared speed of fetching for same sql statement "select * from
> table_name" with Dolphin and ObjectStudio 6.3, 20000 rows. The result is
> same: ObjectStudio is 5 times FASTER.

This is a friendly group, we try to help. Please post the code you used in
both cases and we'll look into it.

Regards

Blair