Performance issue with strings in embedded firebird?

[Rand-Random]

I have the following query

select ABS(t1.DB + t1.CR), t1.AdjustId 
from TabAdjustAccount as t1 
JOIN TabAdjust as t2 on t1.AdjustId = t2.AdjustId 
where t1.AccType = 1 and t2.EffectiveDate >= @perBegin and t2.EffectiveDate <= @perEnd;

When I execute this code on the same database on the same machine I get two different results depending if embedded or not. Both running Firebird 4.0.1.2692 .NET Provider Version didn't seem to influence the performance tested against 8.5.4 and 9.0.0, both gave the same results.

I have this result:

Firebird Version	no cast
non-embedded	74 ms
embedded	174 ms

By adding a cast and execute this query

select CAST(ABS(t1.DB + t1.CR) as varchar(8190)), CAST(t1.AdjustId as varchar(8190)) 
from TabAdjustAccount as t1 
JOIN TabAdjust as t2 on t1.AdjustId = t2.AdjustId 
where t1.AccType = 1 and t2.EffectiveDate >= @perBegin and t2.EffectiveDate <= @perEnd;

Notice, I am casting to string because the fields and operation are dynamically generated and the code handling the result only works with strings, nothing I can change about this.

I have this result:

Firebird Version	cast to string
non-embedded	167 ms
embedded	738 ms

Both numbers go up, but the increaese isn't the same for both firebird versions. So I believe there may be an additional penalty for the embedded version.

Firebird Version	no cast	cast to string	increase by
non-embedded	74 ms	167 ms	125,68%
embedded	174 ms	738 ms	324,14%

The same queries with double the amount of data results in the following number

Firebird Version	no cast	cast to string	increase by
non-embedded	136 ms	316 ms	132,35%
embedded	298 ms	1.410 ms	373,15%

You may find the code I used to test here: https://github.com/Rand-Random/FbEmbeddedString

[cincuranet]

Hi @Rand-Random. Yes marshaling string from/to embedded involves bit more than numbers or from/to wire. I'll check whether there's some low hanging fruit.