Skip to content
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

Open
firebird-automations opened this issue Sep 5, 2011 · 12 comments
Open

Multiqueries needed to improve performance ! [CORE3592] #3946

firebird-automations opened this issue Sep 5, 2011 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Commented by: @aafemt

Discover EXECUTE BLOCK for yourself.

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @pmakowski

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

?

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link

PaladinMattt commented Oct 7, 2022

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
Copy link
Contributor

aafemt commented Oct 7, 2022

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
Copy link
Member

AlexPeshkoff commented Oct 9, 2022 via email

@aafemt
Copy link
Contributor

aafemt commented Oct 9, 2022

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
Copy link
Member

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

@PaladinMattt
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants