MySql Native Connector 30x slower dan ODBC connector

[Fernand Vanrie]

We did some tests with the latest MySql-connector Beta on Windows and
found that in a real live situation that opening a table with 28.000
records takes  35 seconds before  the whole table is loaded in memory.(
going to the last record)
With a ODBC connection  it takes 1 second.
Are we doing something wrong with our native connection  or can this
behavior  been confirmed ?

Fernand

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Mechtilde Stehmann-2]

Hello,

Fernand Vanrie schrieb:
> We did some tests with the latest MySql-connector Beta on Windows and
> found that in a real live situation that opening a table with 28.000
> records takes  35 seconds before  the whole table is loaded in memory.(
> going to the last record)

I did a similar test: 16443 records need nearly 8 s using table view.

> With a ODBC connection  it takes 1 second.
I can't test it with ODBC because my configuration is defect.

with JDBC I meassured 9 s
> Are we doing something wrong with our native connection  or can this
> behavior  been confirmed ?
Is it a local connection or via Intranet or maybe via internet?

Regards

Mechtilde

--
Dipl. Ing. Mechtilde Stehmann
## http://de.openoffice.org
## Ansprechpartnerin für die deutschsprachige QA
## Freie Office-Suite für Linux, Mac, Windows, Solaris
## Meine Seite http://www.mechtilde.de
## PGP encryption welcome! Key-ID: 0x53B3892B


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Hallo Mechtilde ,


Our Mysql server is based on a MS-server who I accesed via our Intranet.

Your results confirms the slowness off the Native Connector.  I never
used JDBC but 9 seconds for 16.000 records is also very slow no ?

Using the latest MySql connector ODBC 5.1.5 for windows (dowloaded from
the MySql site)  it takes a blink ( 1sec) to read all 28.000 records.
Same situation for the Beamer (F4).
Ones the records are read in to the memory then the speeds seems to bee
the same....

Greetz

Fernand

> Hello,
>
> Fernand Vanrie schrieb:
>  
>> We did some tests with the latest MySql-connector Beta on Windows and
>> found that in a real live situation that opening a table with 28.000
>> records takes  35 seconds before  the whole table is loaded in memory.(
>> going to the last record)
>>    
>
> I did a similar test: 16443 records need nearly 8 s using table view.
>
>  
>> With a ODBC connection  it takes 1 second.
>>    
> I can't test it with ODBC because my configuration is defect.
>
> with JDBC I meassured 9 s
>  
>> Are we doing something wrong with our native connection  or can this
>> behavior  been confirmed ?
>>    
> Is it a local connection or via Intranet or maybe via internet?
>
> Regards
>
> Mechtilde
>
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Fernand, Mechtilde,

Same observation here - MySQL 5.0 running on a local MS machine.
A very narrow table with 16,341 records and 1st to last record move in
dataview
ODBC 5.1  - ~1second
Native (BETA) ~3seconds (using named pipe)

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Drew Jensen wrote:
> Fernand, Mechtilde,
>
> Same observation here - MySQL 5.0 running on a local MS machine.
> A very narrow table with 16,341 records and 1st to last record move in
> dataview
> ODBC 5.1  - ~1second
> Native (BETA) ~3seconds (using named pipe)
>

OOOOPS - need to amend that, the wrong way.

The above run I did with OO.o 3.1 and an earlier binary for the
MySQLNative driver (not released via the extension site).

Just now changed over to the binary available from the extension site
and it is markedly slower.
Same machine, same OO.o instance but now the record move is taking over
12 seconds.

Also getting framkework error messages regarding an embedded macro
library that where not being displayed before the change..

I will try to duplicate this - revert to original oxt / then / switch to
oxt from extension site again - should it repeat - off to the Issue
tracking system I suppose,

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Drew ,

Did you filed a issue ? or sould I do it but I do not know where ?

Greetz

Fernand

> Drew Jensen wrote:
>> Fernand, Mechtilde,
>>
>> Same observation here - MySQL 5.0 running on a local MS machine.
>> A very narrow table with 16,341 records and 1st to last record move
>> in dataview
>> ODBC 5.1  - ~1second
>> Native (BETA) ~3seconds (using named pipe)
>>
>
> OOOOPS - need to amend that, the wrong way.
>
> The above run I did with OO.o 3.1 and an earlier binary for the
> MySQLNative driver (not released via the extension site).
>
> Just now changed over to the binary available from the extension site
> and it is markedly slower.
> Same machine, same OO.o instance but now the record move is taking
> over 12 seconds.
>
> Also getting framkework error messages regarding an embedded macro
> library that where not being displayed before the change..
>
> I will try to duplicate this - revert to original oxt / then / switch
> to oxt from extension site again - should it repeat - off to the Issue
> tracking system I suppose,
>
> Drew
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Fernand Vanrie wrote:
> Drew ,
>
> Did you filed a issue ? or sould I do it but I do not know where ?

I did not , please do so yourself
 
I reverted to the Alpha dirver and the time went to ~3 seconds (still
too long)
Then upgraded to the Beta driver and the time did not change.
I repeated that process 2 more times with the same results.

THEN this morning I wake up and read your email - so try it one more time...
*dazed look*...and I am getting >3 seconds with the Alpha driver now..

I know off hand if logging is supported with the native driver
(Unless someone reading this list already knows :>)
but will try it later today to see, if so that may give us a clue as to
what is happening on your machine.

Drew



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Drew Jensen wrote:
> Fernand Vanrie wrote:
>> Drew ,
>>
>> Did you filed a issue ? or sould I do it but I do not know where ?
>
> I did not , please do so yourself
please tell me where I sould file a issue to been sure it comes in the
box of the right developers !

>
> I reverted to the Alpha dirver and the time went to ~3 seconds (still
> too long)
> Then upgraded to the Beta driver and the time did not change.
> I repeated that process 2 more times with the same results.
>
> THEN this morning I wake up and read your email - so try it one more
> time...
> *dazed look*...and I am getting >3 seconds with the Alpha driver now..
>
> I know off hand if logging is supported with the native driver
> (Unless someone reading this list already knows :>)
> but will try it later today to see, if so that may give us a clue as
> to what is happening on your machine.

I tested here on different machines in our Compagnie network anywhere
Native is up to 30x slower than the ODBC connector.
Ones the table is loaded in the Machines memory, then we can have
differences depending on the power off the involved Machine.
What i do not understands is why the "Search" in a Beamer or Table view
is also so very slow ?

Greetz

Fernand
>
> Drew
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Ok - the place to start is at this page
http://qa.openoffice.org/issue_handling/pre_submission.html

When you go to actually enter the issue simply select the application
'Database'.

Now - there is no entry in the issue tracking system for the MySQL
Native Driver currently - I have sent an email to the QA development
mailing list to see if we can get one added.

If you want to hold off a few hours I'll let you know what I hear back
on that - other wise if you enter the issue now jsut select 'none' for
the database sub-component and/if a new entry is made later I'll just
update the issue.

Let me know if you have any further questions.

Drew

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Marcus Lange-2]

Fernand Vanrie wrote:

Hi all,

> please tell me where I sould file a issue to been sure it comes in the
> box of the right developers !

you can file a bug report here:
http://www.openoffice.org/issues/enter_bug.cgi?component=extensions

Please enter "fs" to assign it to the developer. Unfortunately, the
correct subcomponent does not exist yet. I'll ask to create it. But you
can file it and change it later-on.

Thanks

Marcus

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Marcus,

>> please tell me where I sould file a issue to been sure it comes in the
>> box of the right developers !
>
> you can file a bug report here:
> http://www.openoffice.org/issues/enter_bug.cgi?component=extensions

Sorry, I have to disagree. The idea to have a dedicated project for the
extensions altogether is somewhat bogus. There's a variety of reasons
for that, all boiling down to the fact that the separation into a
dedicated project does not make sense if the people working on those
issues spend their day work time in another project. Submitting issues
for the MySQL Connector in "extensions" instead of "Database Access"
just makes work for Base's dev and QA more complicated, consuming and
wasting precious resources, without any real benefit.

So, I prefer submitting the issues in component "Database access", as it
is done with SRB.

"Database Access" has now a sub component "MySQL Connector/OOo", with
the default owner and QA contact being the same as for the other sub
components.

Ciao
Frank

PS: apologies to everybody for not responding to those MySQL Connector
problems recently. I'll try to clear that backlog ...

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Drew , Marcus,
I posted 102896
<http://www.openoffice.org/issues/show_bug.cgi?id=102896> for (fs)
Greetz

Fernand

> Ok - the place to start is at this page
> http://qa.openoffice.org/issue_handling/pre_submission.html
>
> When you go to actually enter the issue simply select the application
> 'Database'.
>
> Now - there is no entry in the issue tracking system for the MySQL
> Native Driver currently - I have sent an email to the QA development
> mailing list to see if we can get one added.
>
> If you want to hold off a few hours I'll let you know what I hear back
> on that - other wise if you enter the issue now jsut select 'none' for
> the database sub-component and/if a new entry is made later I'll just
> update the issue.
>
> Let me know if you have any further questions.
>
> Drew
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Fernand,

> We did some tests with the latest MySql-connector Beta on Windows and
> found that in a real live situation that opening a table with 28.000
> records takes  35 seconds before  the whole table is loaded in memory.(
> going to the last record)
> With a ODBC connection  it takes 1 second.
> Are we doing something wrong with our native connection  or can this
> behavior  been confirmed ?

In general, our tests with large tables showed no significant
performance handicap, compared with ODBC. Well, some initial versions of
C/C++ *did* have a handicap here, but this had been fixed. But perhaps
in broke ...

Will need to do some tests with that.

Ciao
Frank

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Fernand,

> We did some tests with the latest MySql-connector Beta on Windows and
> found that in a real live situation that opening a table with 28.000
> records takes  35 seconds before  the whole table is loaded in memory.(
> going to the last record)
> With a ODBC connection  it takes 1 second.
> Are we doing something wrong with our native connection  or can this
> behavior  been confirmed ?

Ocke found and fixed a bug in Connector/C++ - now the native driver is
faster then the ODBC driver when scrolling to the last record :)

The patch is still subject to a review from the MySQL team (where the
experts currently are ... on the Bahamas, or whereever they spend their
vacation), but assuming that they approve it, the problem should be
fixed in the GA release.

Thanks & Ciao
Frank

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Hallo Frank ,

Thanks for this quick fix, now I have a other "performance" problem:
When have a table opened (all content in memory) or wehave the content
in a Beamer then the search is very... very... slow compared to wath we
can do in the original MySQL tables or compared to a ohter frondend
application.
When searching in a OO-table you can see rolling the row counter !
Using my 28.000 records test table and searching to the last record
(using field text content) it takes over 60 secs to find this record..
AND:  the search is eating memory like sweeties (about 1,5 MB par sec)
memory who is only released after closing ALL OO-instances.

Sould i fill a issue or is this a known performance problem?
Greetz

Fernand

> Hi Fernand,
>
>  
>> We did some tests with the latest MySql-connector Beta on Windows and
>> found that in a real live situation that opening a table with 28.000
>> records takes  35 seconds before  the whole table is loaded in memory.(
>> going to the last record)
>> With a ODBC connection  it takes 1 second.
>> Are we doing something wrong with our native connection  or can this
>> behavior  been confirmed ?
>>    
>
> Ocke found and fixed a bug in Connector/C++ - now the native driver is
> faster then the ODBC driver when scrolling to the last record :)
>
> The patch is still subject to a review from the MySQL team (where the
> experts currently are ... on the Bahamas, or whereever they spend their
> vacation), but assuming that they approve it, the problem should be
> fixed in the GA release.
>
> Thanks & Ciao
> Frank
>
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Fernand,

> Thanks for this quick fix, now I have a other "performance" problem:
> When have a table opened (all content in memory) or wehave the content
> in a Beamer then the search is very... very... slow compared to wath we
> can do in the original MySQL tables or compared to a ohter frondend
> application.

This probably is the very same bug: The search also scrolls through the
complete result set, and this is what takes ages in the current version
(technically: The time for visiting all records grows quadratically with
the number of records).

> When searching in a OO-table you can see rolling the row counter !
> Using my 28.000 records test table and searching to the last record
> (using field text content) it takes over 60 secs to find this record..
> AND:  the search is eating memory like sweeties (about 1,5 MB par sec)
> memory who is only released after closing ALL OO-instances.

The memory thing is worth an issue, I'd say.

Ciao
Frank

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Frank ,

> Hi Fernand,
>
>  
>> Thanks for this quick fix, now I have a other "performance" problem:
>> When have a table opened (all content in memory) or wehave the content
>> in a Beamer then the search is very... very... slow compared to wath we
>> can do in the original MySQL tables or compared to a ohter frondend
>> application.
>>    
>
> This probably is the very same bug:

?? I do not think it is related to the driver, we have this slowness
with every Database not only with MySQL

> The search also scrolls through the
> complete result set,
> (technically: The time for visiting all records grows quadratically with
> the number of records).
>
>  
>> When searching in a OO-table you can see rolling the row counter !
>> Using my 28.000 records test table and searching to the last record
>> (using field text content) it takes over 60 secs to find this record..
>> AND:  the search is eating memory like sweeties (about 1,5 MB par sec)
>> memory who is only released after closing ALL OO-instances.
>>    
>
> The memory thing is worth an issue, I'd say.
>  

I dot it
> Ciao
> Frank
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Frank Schoenheit, Sun Microsystems Germany]

Hi Fernand,

>>> Thanks for this quick fix, now I have a other "performance" problem:
>>> When have a table opened (all content in memory) or wehave the content
>>> in a Beamer then the search is very... very... slow compared to wath we
>>> can do in the original MySQL tables or compared to a ohter frondend
>>> application.
>>>    
>> This probably is the very same bug:
> ?? I do not think it is related to the driver, we have this slowness
> with every Database not only with MySQL

Okay, I got you wrong here, should have read more carefully :) I assume
that you were talking about bad performance native vs. ODBC driver.

Not sure we already have a performance bug for the search, you might
want to submit one. However, there's a certain limit which we cannot
break: Due to the nature of this searching, all data has to be fetched
from the server, so it's surely slower than server-side filtering. Which
doesn't mean there's no room for improvement in the client-side
searching ...

Ciao
Frank

--
- Frank Schönheit, Software Engineer         frank.schoenheit@... -
- Sun Microsystems                      http://www.sun.com/staroffice -
- OpenOffice.org Base                       http://dba.openoffice.org -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Fernand Vanrie]

Frank Schönheit - Sun Microsystems Germany wrote:

> Hi Fernand,
>
>  
>>>> Thanks for this quick fix, now I have a other "performance" problem:
>>>> When have a table opened (all content in memory) or wehave the content
>>>> in a Beamer then the search is very... very... slow compared to wath we
>>>> can do in the original MySQL tables or compared to a ohter frondend
>>>> application.
>>>>    
>>>>        
>>> This probably is the very same bug:
>>>      
>> ?? I do not think it is related to the driver, we have this slowness
>> with every Database not only with MySQL
>>    
>
> Okay, I got you wrong here, should have read more carefully :) I assume
> that you were talking about bad performance native vs. ODBC driver.
>
> Not sure we already have a performance bug for the search, you might
> want to submit one. However, there's a certain limit which we cannot
> break: Due to the nature of this searching, all data has to be fetched
> from the server,

Frank,  are you telling that after opening a table  and after we scroll
to the last record, (I suppose we have the content off the table now in
memory) the search go's back to the Server to find a result ?? and the
search is not using the resultset already present in the memory off the
local machine ?
BTW: filtering is done where ?
>  so it's surely slower than server-side filtering. Which
> doesn't mean there's no room for improvement in the client-side
> searching ...
>
> Ciao
> Frank
>
>  


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[drewjensen]

Fernand Vanrie wrote:
> Frank,  are you telling that after opening a table  and after we
> scroll to the last record, (I suppose we have the content off the
> table now in memory) the search go's back to the Server to find a
> result ?? and the search is not using the resultset already present in
> the memory off the local machine ?
> BTW: filtering is done where ?

Hello Fernand,


Searching vs Filtering in Base is indeed quite different.

This moment is a very good time for me to review those differences,
purely for personal reasons (time has proven that I don't know nearly as
much as I once thought I did)-

Heres what I think:

Filters
- Change the result set contents
- - for rdb datasources this means a trip to/from the server

- filter criteria are automatically stored with dataview components

- setting filters is a purely modal action (no change to other UI
objects until the dialog is removed - or - the form data controls are
returned to display mode in the case of FilterByForm )
- - setting or removing filter criteria resets the record pointer within
the result set to the first record in the result set.
- - - when the result set is reset, upon setting or removing filter
criteria, only some number of records will be read to the client memory.
(the number will vary depending on the size of each row in the result set)

Searchs
- Applies search to current result set (there should not be any traffic
between the client and server -once- the current result set is read into
the clients memory)
- - for all datasources this means that the comparisons are performed on
the client machine
- - - so if you open a dataview for a table - a certain number of
records are retrieved - if you apply a search to this result set records
are retrieved from the server AS NEEDED as until the first match is
found of an in-memory row

- criteria is stored in a previously used queue

- the search dialog is not  modal - but you can apply the search and see
the record found without clearing the dialog from the screen - can't
work with the calling form however until it is (is that really necessary?)
- - performing a search moves to the first matching record and changes
the current record pointer within the result set
- - - if no match is found the current record is not changed


But - there is more in the way of differences to be aware of, aren't there:

continued....



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...