Devart Forums

We have 80% implemented your drivers as a replacement layer to our old system.
mid sized tables, 500-500,000 record tables at moment. From a few Kb to 300MB. With larger warehouse tables to come. It all works pretty well.

we have come across a few quirks.


1.Since its unidirectional we would hope it would buffer one record at a time not many between open and closes.
Instead, we found that Tsqlquery releases the memory used in last sql statement on Tsqlquery.free instead of on Tsqlquery.close.
Work around being freeing the Tsqlquery all the time.

2. with 'direct' driver (talking directly to mysql server api) utf8 Tfield.size reports as 3 bytes ( one for each character)
via libmysql.dll Tfield.size reports 1 char
This seems inconsistent.
I understand that mysql stores utf8 as 3 bytes but why translate the locale correctly in one case and not the other.

3. We execute a large amount of queries on a single tsqlconnection raipidly. This causes the Mysql server to run out of ports to handle the queries. Tsqlquery.execute throws an exception when th eports run out. Fine.
But we have closed the other queries and would have hoped the ports would be closed.
We work around catching the exception and trying again and again till we get a port. Can take up to nearly a minute. Luckily we this makes it only marginally slower than paradox.

By the way. We have not replicated the above independently of our code. We have too tight deadlines. Our code base is ugly, massive and the data large and complex, so we cannot pass that on as a simple example.

Our set ups is D6/D7, latest drivers and Mysql 4.1.8-4.1.11


thanks

alex

> 1.Since its unidirectional we would hope it would buffer one record at a time
> not many between open and closes

TSQLQuery was developed by Borland not us and we are unable to change its behaviour.


> 2. with 'direct' driver (talking directly to mysql server api) utf8
> Tfield.size reports as 3 bytes

We are working on it.

> 3. We execute a large amount of queries on a single tsqlconnection raipidly

Do you use FetchAll = False? If not, please send us a complete small sample.

> We have not replicated the above independently of our code

Another users have never complained on such problems and we inclined to think that this situation is specific just for your case. Without your sample we cannot help you.

Ikar wrote:> 1.Since its unidirectional we would hope it would buffer one record at a time
> not many between open and closes
>>TSQLQuery was developed by Borland not us and we are unable to change its behaviour.

We understood that you implement an interface that is called by tsqlquery.
I had anticipated that one of the calls to the interface originating from tsqlquery.close would have led to the release of memory structures associated with previous tsqlquery.execute calls. I will look at your code and see.


> 3. We execute a large amount of queries on a single tsqlconnection raipidly

>>Do you use FetchAll = False? If not, please send us a complete small sample.

Yes we do, FetchAll = True causes a massive slow down in our code, as it replicates paradox table functionality. Hence tsqlquery.execute is usually a 'Select *' with a LIMIT clause. Then we a counter of were we are.

> We have not replicated the above independently of our code

Another users have never complained on such problems and we inclined to think that this situation is specific just for your case. Without your sample we cannot help you.

-noted, no time to supply a sample, just giving some feedback.

I think the issue is related to the first issue, that TSQLQuery.close is not releasing the memory used in previous TSQLQuery.executes. To me this means that the cursor(or whatever mysql use, socket connection?) is kept as well. I was hoping you may see that somewhere in the interface to Mysql the is rentention of previous calls that could be released.

Is it perhaps you are expecting similar SQL statements to be passed through the query that might use a subset of the result set in a previous query and hope to reuse it.
Such an idea would be useful if mysql had cursors. Useful in implementing quick work arounds to TSQLQuery.previous and TSQLQuery.last, as the dataset in the cursor could be reused and the cursor repositioned meaning no extra calls to the database. See VitaVoom's implementation of DBexpress for Postgresql. Which is the other database we are using. It does help performance a lot. Shame i have to use mysql at moment.

alex

> I will look at your code and see.

If you send us a complete small sample we can try to find out reasons of such behaviour.

>Do you use FetchAll = False? If not, please send us a complete small sample.
>Yes we do

Please pay attention to restrictions of this mode.

> Is it perhaps you are expecting similar SQL statements to be passed through the query that
> might use a subset of the result set in a previous query and hope to reuse it.

DbxMda doesn't do anything similar.