TzQuery is SLOW to read records

Jay · Post by **Jay** » 27.10.2006, 08:41

I have recently started to look into Zeos 6.6 after being a long time user of of Zeos in version 5 and abandoning my efforts with 6.1 a couple years ago.

I just remembered why I abandoned it!!

It was because I was having MAYOR speed problems with large MySql tables. I rememberd now that I am having the exact same problem again.

I have an application that generates OLAP cubes (with PivotCube) based on a query. The way that pivotcube works is that it reads the results of entire query, record by record, and displays the cube when it is finished.

I am doing tests with Zeos, MyDac (CoreLab), SqlDirect, and Tquery (ODBC).

With Zeos, the cube takes almost 2 minutes (1:50) to process the Query, and al other options take between 8 and 12 seconds to process. Years ago, when this happened I actually thought the problem was with PivotCube and had a big discussion with their support, the final result was that the problem was with zeos (6.1.5).

Now that Zeos 6.6 is available, I am trying to go back to Zeos since I really do like it a lot (even before 5.5). I would really appreciate it if this could be resolved because I don't want to abandon Zeos again.

I don't know if this same problem happens with other databases, but if it is slow with MySql, Zeos is worthless for me.

Regards
Jay

gto · Post by **gto** » 27.10.2006, 14:02

Hello Jay!
Well.. here it's not so slow!

It may be the way that PivotCube reads from the Query. I don't know PivotCube enough, so can you post a piece of code?

[]'s

zippo · Post by **zippo** » 27.10.2006, 21:57

Using PivotCube on dataset with 25.000 records and 27 solumns- speed is OK. Or maybe I should test another component to compare..

Time to process is about 10 -20 seconds (depends a litle probably because usage of varcahr fields).

How many records and coluimns do you have in the dataset.

Jay · Post by **Jay** » 27.10.2006, 23:52

The Test I did was with a table of 31,000 records, 19 fields for the actual data. Besides, there are 6 other tables used for the dimension values which have:
2 records
4 records
43 records
143 records
6 records
19 records

The problem is that this was actually just a test cube, most of the real cubes that will be generated commonly use between 500,000 and 2,000,000 records and some dimensions of up to 4,000 values. As I Recall, with zeos 6.1.5, the speed problem grew exponentially as the data records got larger.

As I mentioned before, Why does Zeos take MUCH LONGER than any other components? Even the Plain TQuery (with ODBC) is much faster. If the problem was with PivotCube, the diference between all data components would not be great, and in fact it isn't, except for Zeos which takes MUCH LONGER.

You can try it with a larger data set and compare it using other components.

Regards
Jay

fduenas · Post by **fduenas** » 29.10.2006, 22:47

Hola

What version of mysql are you using? are you sure youre using the correct libmysqlXX.dll for the myswql version you're using?

Jay · Post by **Jay** » 30.10.2006, 17:38

I am using the LibMySql40.dll that comes with the Zeos 6.6 download. I renamed it to Libmysql.dll and use the "mysql" option on the connection component, since there is no option for "mysql-40".

Regards
Jay

fduenas · Post by **fduenas** » 31.10.2006, 06:38

Using 'mysql' protocol will load latest mysql-5 version so this can influence with your speed becaus eytou're loading a libmysql.dll that doesn't correpond to the correct version. try to work with latest mysql 5.0

Mysql 4.0 is not supported. If you still want to use mysql 4.0 server protocol open zeos.inc located in src folder and uncomment line

{.$DEFINE ENABLE_MYSQL_DEPRECATED} (just delete the point)

save it and recompile zeoslib. this will add support for mysql323 and mysql40

also uncomment line

{.$DEFINE MYSQL_STRICT_DLL_LOADING}

to strinct load the dll named to libmysqlXX.dll and prevent any loading of limysql.dll.

I mostly recommend you to use at least Mysql 4.1 server or mysql50 for better performance.

Jay · Post by **Jay** » 31.10.2006, 07:40

Thank you very much for your time and effort on this issue.

I am in the process of moving my main DB to MySql 5 which will probably still take a couple of months.

For the time being, the program I was intending to use Zeos 6.6 with, will be done with something else.

When I have the DB in MySql 5 I will probably come back to test this and you will probably hear from me again if I still encounter this issue.

Regards
Jay

bangfauzan · Post by **bangfauzan** » 31.10.2006, 09:45

Hi Jay....

how about using "mysql-4.1" protocol on the conncetion component ?
it will be automatically load libmysql41.dll which is compatible with mysql40.

or you can choose "mysql" of protocol, and then (as tohenk's advices)
specify the connection properties to be somethink like these :

Library=libmysql40.dll
ForceVersion=500009

hopely useful

Post by **mdaems** » 31.10.2006, 10:51

Bangfauzan,

This is not yet available in ZeosDbo 6.6. We are looking at it and may add it to next version, after 6.6.X-stable.

Mark

fduenas · Post by **fduenas** » 02.11.2006, 02:04

Jay wrote:Thank you very much for your time and effort on this issue.

I am in the process of moving my main DB to MySql 5 which will probably still take a couple of months.

For the time being, the program I was intending to use Zeos 6.6 with, will be done with something else.

When I have the DB in MySql 5 I will probably come back to test this and you will probably hear from me again if I still encounter this issue.

Regards
Jay

Well even that, you can still test mysql 4.0 compiling zeoslib with the switch i told you. Just me sure you will have a libmysql.dll for mysql 4.0 server renamed to libmysql40.dll in your application folder.