Multiqueries needed to improve performance ! [CORE3592]

[firebird-automations]

Submitted by: Przemyslaw Jenarzewski (jenar)

Please implement multiqueries support for firebird, so insert/update/select queries could be send as one sql and results will be delivered as one dataset. It is very important feature to speed up database access (also for bigger applications).

Some other database providers (MS Sql Server,MySQL,PostGreSQL,SQLite,Microsoft SQL CE) are already using this. We would able to use some optimisations ie with NHibernate and other cases.

Some performance boost ca 25-30% could be done through this feature (see: http://ayende.com/blog/3979/nhibernate-futures). "select * from a; select * from b;" could be send as single sql

With inserts/Updates is the same problem, they are send to DB as single sql which is slow. Again, we could send single sql: "insert into a ....;insert into b ... ;" or "update a set...; update b set..."

[firebird-automations]

Commented by: @aafemt

Discover EXECUTE BLOCK for yourself.

[firebird-automations]

Commented by: Przemyslaw Jenarzewski (jenar)

I know, i have used execute block many times, but this is not solution for this improvement. As Jiri Cincura said "This is a limitation of Firebird protocol itself, not the provider. " (DNET392)

[firebird-automations]

Commented by: @asfernandes

I think ability to have different selects making different result sets that are automatically returned (say, like MSSQL, AFAIR) has nothing to do with Firebird.

On the other hand, we could just create a CURSOR datatype so people can use them, for example, in procedure output parameters.

Example:

... RETURNS (C1 CURSOR, C2 CURSOR) AS BEGIN C1 = CURSOR(SELECT * FROM T1); C2 = CURSOR(SELECT * FROM T1); END

Or in things like SELECT DEP.*, CURSOR(SELECT * FROM EMPLOYEES WHERE DEPARTMENT = http://DEP.ID) FROM DEPARTMENTS DEP;

This is something Oracle supports with some limitations and maybe differences. It's much more the "Firebird way".

[firebird-automations]

Commented by: @pmakowski

are you speaking about something like in Postgresql : http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html

?

[firebird-automations]

Commented by: @asfernandes

Yes, with the exception of the "RETURN NEXT $1; RETURN NEXT $2;" part, which I think Firebird can handle better with output parameters.

[firebird-automations]

Commented by: Przemyslaw Jenarzewski (jenar)

Not exacly "cursors" it is more like "execute block" (running multiple queries in one call)

Example for selects: "select * from Table_A; select * from Table_B;" will return one data set, not scrolling through rows. Result are returned as list or array result[0]= select from table_a, result[1]=select from table_b.

Example for insert/update "insert into Table_A...; update Table_B..."

please take a look at http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

[PaladinMattt]

This has been open a long time, but still an issue. We are able to pass a single semi-colon delimited query to some of our database systems (PostreSQL, SQL Sever), and have a dataset loaded up with a single round trip to the database. For Firebird we need to loop through each query and load each table individually, which can be terribly inefficient at times. We often see a 7-10 times performance gain utilizing other providers. This still doesn't work in Firebird 4.0 and Execute Block is not a solution to the problem. Is there any thought on implementing this in a future version?

[aafemt]

Firebird API doesn't support multiple result sets with different metadata so if EB is not an option for you - nothing can be done.

[AlexPeshkoff]

On 10/8/22 00:41, Dimitry Sibiryakov wrote:

Firebird API doesn't support multiple result sets with different metadata so if EB is not an option for you - nothing can be done.

Adding such feature to APi is useful in many aspects. Unfortunately we have no plans on implementing it in next version.

[aafemt]

I see no way how performance of such "multiquery" can be better than executing of single queries in parallel connections (or even sequentially with embedded engine).

[AlexPeshkoff]

Certainly almost no problems here for embedded engine, performance problems are with remote access

[PaladinMattt]

The performance is really seen because these queries are going over a WAN, so there is a lot of latency.