search

FirebirdSQL / NETProvider

Firebird ADO.NET Data Provider
https://www.firebirdsql.org/en/net-provider/
Other
159 stars 65 forks source link

Query on FlameRobin 15x faster than Firebird client. #1102

Closed BlackbirdSQL closed 1 year ago

BlackbirdSQL commented 1 year ago

Hi. I am executing the following script for a Visual Studio extension.
Execution time on 1500 triggers is 5 seconds plus. On FlameRobin it is < 0.3 seconds with it's settings configured to pull the full result set.

SELECT -- :TABLE_CATALOG, :TABLE_SCHEMA, :TABLE_NAME, :TRIGGER_NAME null AS TABLE_CATALOG, null AS TABLE_SCHEMA, trg.rdb$relation_name AS TABLE_NAME, trg.rdb$trigger_name AS TRIGGER_NAME, trg.rdb$description AS DESCRIPTION, -- :IS_SYSTEM_FLAG (CASE WHEN trg.rdb$system_flag <> 1 THEN 0 ELSE 1 END) AS IS_SYSTEM_FLAG, -- :TRIGGER_TYPE trg.rdb$trigger_type AS TRIGGER_TYPE, -- :IS_INACTIVE (CASE WHEN trg.rdb$trigger_inactive <> 1 THEN false ELSE true END) AS IS_INACTIVE, -- :PRIORITY trg.rdb$trigger_sequence AS PRIORITY, -- :EXPRESSION for parser (CASE WHEN trg.rdb$trigger_source IS NULL AND trg.rdb$trigger_blr IS NOT NULL THEN cast(trg.rdb$trigger_blr as blob sub_type 1) ELSE trg.rdb$trigger_source END) AS EXPRESSION, -- Initial value of :IS_IDENTITY for parser (CASE WHEN trg.rdb$trigger_sequence = 1 AND trg.rdb$trigger_type = 1 THEN true ELSE false END) AS IS_IDENTITY FROM rdb$triggers trg ORDER BY trg.rdb$trigger_name
The call is a straight FbDataAdapter(FbCommand).Fill on a Firebird 3.0 server and FirebirdClient 9.1.1 .
There is a caveat... The script is executing from within a Visual Studio Experimental instance on a debug build, so I would expect it to be slower, but the experimental instance is being launched from Windows, not from the Visual Studio IDE.
Am I missing a trick here?
Thanks, Greg

cincuranet commented 1 year ago

No trick. But without some reproducible test case it's hard to try on my side.

mrotteveel commented 1 year ago

FlameRobin by default only fetches the first 300 rows (and more when you scroll to the end), are you sure you're comparing the same things?

BlackbirdSQL commented 1 year ago

FlameRobin by default only fetches the first 300 rows (and more when you scroll to the end), are you sure you're comparing the same things?

I have FlameRobin settings configured to pull the full result set, overriding the 300 limit.
Performance on everything else is comparable. It's when I include the rdb$trigger_source and rdb$trigger_blr on 1500 rows that the performance drops off.
At first I thought it was Firebird's internal blr convertor that is called using the statement cast(trg.rdb$trigger_blr as blob sub_type 1), but then FlameRobin would be affected too.
So yeah, dunno...

cincuranet commented 1 year ago

Are you sure FlameRobin is fetching full blob as well?

mrotteveel commented 1 year ago

IIRC, FlameRobin only fetches those blobs of rows it needs to display, and possibly only sufficient bytes to have something to display in the grid.

BlackbirdSQL commented 1 year ago

I just want to say that it comes appreciated that you guys put some thought into this. Thanks.
So I did test this previously by using both Ctrl-PgDn and grabbing the scrollbar thumb and dragging it to the end. Those tests were flawed because the FlameRobin grid implements throttling.
By holding down PgDn FlameRobin takes arguably longer than the FirebirdSql client. On performing the page down a second time the blobs are already loaded and it pages to the bottom literally instantaneously.
I've executed the same command without the server-side blr conversion on 800 triggers that have valid rdb$trigger_source columns and the response is literally instantaneous, so it turns out it's likely the Firebird server blr decoder.
Issue resolved. Thank you.