New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Multiqueries needed to improve performance ! [CORE3592] #3946
Comments
Commented by: @aafemt Discover EXECUTE BLOCK for yourself. |
Commented by: Przemyslaw Jenarzewski (jenar) I know, i have used execute block many times, but this is not solution for this improvement. |
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) 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". |
Commented by: @pmakowski are you speaking about something like in Postgresql : http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html ? |
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. |
Commented by: Przemyslaw Jenarzewski (jenar) Not exacly "cursors" it is more like "execute block" (running multiple queries in one call) Example for selects: Example for insert/update please take a look at http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html |
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? |
Firebird API doesn't support multiple result sets with different metadata so if EB is not an option for you - nothing can be done. |
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.
|
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). |
Certainly almost no problems here for embedded engine, performance problems are with remote access |
The performance is really seen because these queries are going over a WAN, so there is a lot of latency. |
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..."
The text was updated successfully, but these errors were encountered: