Non transactional table [CORE3361]

[firebird-automations]

Submitted by: @livius2

I suppose that this will be good feature

create table TABLE_A without transaction ( .. )

Table without transaction (outside) This type of table will be work in the "same way" like external table work now. But this will be table inside db (backup can then process it), we then can create indexes relations, triggers ..

If we update record then we can not get update conflict. No record versions will be generated.

This will be very usefull feature i think - table work will be very fast (no record versions) This will be usefull for storing data like e.g. statistics and will be competing for other none transactional sql databases.

[firebird-automations]

Modified by: @livius2

description: I suppose that this will be good feature Table without transaction \(outside\) This type of table will be work in the "same way" like external table work now\. But this will be table inside db \(backup can then process it\), we then can create indexes relations, triggers \.\. If we update record then we can not get update conflict\. No record versions will be generated\. This will be very usefull feature i think \- table work will be very fast \(no record versions\) This will be usefull for storing data like e\.g\. statistics and will be competing for other none transactional sql databases\. =\> I suppose that this will be good feature create table TABLE\_A without transaction \( \.\. \) Table without transaction \(outside\) This type of table will be work in the "same way" like external table work now\. But this will be table inside db \(backup can then process it\), we then can create indexes relations, triggers \.\. If we update record then we can not get update conflict\. No record versions will be generated\. This will be very usefull feature i think \- table work will be very fast \(no record versions\) This will be usefull for storing data like e\.g\. statistics and will be competing for other none transactional sql databases\.

[firebird-automations]

Commented by: @dyemanov

Why do you think it would be much faster? I'd expect a regular table after a GC pass (no unneeded versions) to have a similar performance. Avoiding update conflicts is possible even for transactional tables, if necessary. What non-transactional SQL databases do you mean, besides MySQL + MyISAM?

[firebird-automations]

Commented by: @livius2

Faster - becaouse of not creating record versions at all garbage collector should not work on this kind of tables

if 10 users update normal table that you get 10 version of record and until garbage collector perform this select is very bad the same with massive deletes .. you know - insert 20000 records into table and then delete all try do select count(*) ;-) this take long time because of garbage collector work to clear deleted versions

think about tables with billions records

>>What non-transactional SQL databases do you mean, besides MySQL + MyISAM? not only MyISAM MySQL in earlier versions not support transactions at all ;-) (may be this was default MyISAM and i do not know this name ealier)

but also this will be competing for simple databases like BDE ..

[firebird-automations]

Commented by: @dyemanov

As long as you speak about simultaneous updates, why would you want to get fast but wrong (inconsistent) results? Without good usage scenarios, this discussion is pointless.

[firebird-automations]

Commented by: @livius2

example of use table

CREATE TABLE ACTIVITY WITHOUT TRANSACTION (

ID_DEVICE BIGINT FOREIGN KEY .. WHEN_ACTIVITY TIMESTAMP )

from many "computer" you have UPDATE AVTIVITY SET WHEN_ACTIVITY='NOW';

and on some computer you test and do special tasks if some device not update status SELECT * FROM ACTIVITY WHERE WHEN_ACTIVITY<DATEADD(-10 MINUTE TO CAST('now' AS TIMESTAMP))

[firebird-automations]

Commented by: @asfernandes

You said about table with billions of rows. Do you expect this unfiltered update to run fast?

[firebird-automations]

Commented by: @livius2

of course Adriano sample should be e.g.

UPDATE ACTIVITY SET WHEN_ACTIVITY='NOW' WHERE ID_DEVICE=1

or

delete from ACTIVITY WHERE ID_DEVICE=1

or insert into

many operation cause many record versions in normal transactional work

but yes update whole table will be also fast (faster then normal table work)

[firebird-automations]

Commented by: @livius2

another sample

i suppose this is more real sample database with statistics for game serwer

CREATE TABLE PLAYER WITHOUT TRANSACTION (

ID BIGINT not null primary KEY, ID_SERWER Integer Not NULL, NAME VARCHAR(32) Not Null, Points Integer, Villages Integer, AsAgressor_points Integer, AsDefender_pints Integer, LAST_UPDATE TIMESTAMP, )

e.g. database is accessible from web if we update this table in eg 1 hour interval then users after this big update wait long

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

I have been watching this case and feel that the change is being requested to support poor application/database design. There are a large number of possible solutions to the "issues" raised which don't involve "hacking" the database engine.

Only if there is a very long running transaction, which would prevent garbage collection, would there exist more than 2 versions of a record (the current change and the previous version).

Finally, there is no such thing as a "non transactional SQL engine". In order to be a "SQL engine" transactions must be used. If not, then it is an engine which support SQL queries -- not at all the same thing!

[firebird-automations]

Commented by: @livius2

Sean Leyne

what about external tables? now you have one non transactional table in Firebird ;)

but as is post above

try this insert 20000 records into table and then delete all try do select count(*) how long your query run to return 0 value?

you see in some situation (first example) generating versions for "concurrent" updates is time costs and unnecessary..

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Karol,

So what is this case about? Non-transactional tables or the fact that multiple updates within a single transaction results in the database growing in size? Which is it????

If external tables meet your definition of non-transactional table, then why this case?

Further, I suppose that the request/case to allow for ODBC/JBDC databases to be accessed/queried would meet your requirements.

[firebird-automations]

Commented by: @livius2

First i see that you think about systems like CRM where 1 operation is per minute ;-) I do not say about many updates in one transaction but many updates in many transactions. Biggest system I have is system where i have 18 000 000 transaction per day and processing versions in some tables are unnecessary and time costs

External table have limit to 2GB and also you can not create indexes and relations on it. And External table is outside database file and not processing by backup.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

First, don't presume that everyone think of systems as being CRMs -- our own application handles between 500 000 to 1 000 000 per day!

Second, your examples and details are incomplete. You posted:

try this 
insert 20000 records into table 
and then delete all 
try do select count\(\*\) 
how long your query run to return 0 value? 

Then you posted

I do not say about many updates in one transaction but many updates in many transactions\. 

Where in the example did you reference multiple transactions???

FYI, the 2GB limit for external tables was removed in v2.5.

Next, the Classic engine does active version management (not cooperative) so as long as your application is well designed (ie. no long running transactions), versions are actively cleaned-up and don't have as significant an impact.

It seems that you are trying to fight the multi-generational (i.e. versioning) nature of the engine. While the performance of some cases can be improved, I don't see a change at the scale you propose as being appropriate.

Finally, if I recall your postings to the support mailing list, 90% of your transactions from due to the design of your "active monitoring" functionality. This suggests that this is an application area which should be re-thought.

[firebird-automations]

Commented by: @livius2

Seane,

this sample was for showing how versioning could slowdown performance not for showing updates insert 20000 records into table and then delete all try do select count(*) how long your query run to return 0 value?

in my example of use i wrote this from many "computer" you have UPDATE AVTIVITY SET WHEN_ACTIVITY='NOW' WHERE ..; here you have info about many updates

you write good info about external tables about storage limit but this kind of tables are useful only for import export purposes because in another cases it is useless without creating index and references

---------------------------------------------

about this what i omit previously "Finally, if I recall your postings to the support mailing list, 90% of your transactions from due to the design of your "active monitoring" functionality. This suggests that this is an application area which should be re-thought."

this is not related to that problem how many versions you get i you update 1000 records in 1 transaction and how many if you update 1 different record in 1000 transaction? I suppose the same version count will be generated.. and about long running transactions - i have it not i check this in 30 minutes interval by monitoring tables

---------------------------------------------

another useful sample for nontransactional tables

table for storing log info you need to store info about activity or you are at design time and you put log info in your stored proc you need to store info also if transaction fails i know that this can be done with new feature autonomous transaction - but starting transaction for this unnecessary and costs