Issue Details (XML | Word | Printable)

Key: CORE-3592
Type: Improvement Improvement
Status: Open Open
Priority: Critical Critical
Assignee: Unassigned
Reporter: Przemyslaw Jenarzewski
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Multiqueries needed to improve performance !

Created: 05/Sep/11 02:22 PM   Updated: 06/Sep/11 08:20 AM
Component/s: None
Affects Version/s: 2.5.0
Fix Version/s: None


 Description  « Hide
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..."

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 05/Sep/11 03:43 PM
Discover EXECUTE BLOCK for yourself.

Przemyslaw Jenarzewski added a comment - 05/Sep/11 06:19 PM - edited
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. " (DNET-392)

Adriano dos Santos Fernandes added a comment - 05/Sep/11 06:36 PM
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 = DEP.ID) FROM DEPARTMENTS DEP;

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

Philippe Makowski added a comment - 05/Sep/11 07:14 PM
are you speaking about something like in Postgresql : http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html

?




Adriano dos Santos Fernandes added a comment - 05/Sep/11 07:21 PM
Yes, with the exception of the "RETURN NEXT $1; RETURN NEXT $2;" part, which I think Firebird can handle better with output parameters.

Przemyslaw Jenarzewski added a comment - 05/Sep/11 08:03 PM - edited
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