How do I do? Firebird + Transactions (performance)

emailx45 · Post by **emailx45** » Tue 17 Apr 2012 18:53

Dear friends,

First, excuse-me for my poor english ok?

My system:
Pentium DUAL CORE 2,5ghz
Screen 1366x768
Memory 4gb
HD 300gb
RAD STudio XE (updated)
Not Anti-Virus installed
Not Other software in memory, when programing!

I use in my projects, FIBPLus 7.0.15 and FIREBIRD 2.5 (conection TCP in same pc with RADSTudioXE, I dont use network system pc, CharSet = iso-8859-1 or NONE)

I would like change for UniDAC for know who is who? (in performance)

Question:

In FIBPLUS, i use: (commit in 2 phase)

fibdatabase + 2 fibtransaction (1 = ready-only (readrepetable), 1 = write )

Transactions in InterBase/Firebird: how to use them in FIBPlus (part 2)
http://www.devrace.com/en/fibplus/articles/3292.php

fibtransaction READ-ONLY (READ COMMITTED ) open all time
isc_tpb_read <--- i changed here, before was isc_tpb_write)
isc_tpb_nowait
isc_tpb_rec_version
isc_tpb_read_committed

fibtransaction WRITE (SNAPSHOT) short-time for write changes
isc_tpb_write
isc_tpb_nowait
isc_tpb_rec_version

fibdataset use 2 transactions (1=ready-only open all time, 1=write, just in edit, delete, post records )

the fibtransaction WRITE, i use for REFRESHUPDATES
------------

In UniDAC, how will be this CUSTOM-TRANSACTION?
how can i insert one custom-transaction type?
How can i use it in UniTable?

*********************************
In FIBPLUS, i can use fibtrasaction.INTRASANCTION for to know if the transaction is Active, but, in UniDac components, i have to use UniConnection for this task.

Question:
How to know if TrRO or TrRW is ACTIVE if I have to use UniConnection for this task?

In FIBPLUS, i use TrRO.INTRANSACTION and TrRW.INTRANSACTION. I dont use FIBDATABASE.InTransaction (where is the string of connection with the database = UniConnection component)

*********************
in first test, I make one table (CITY) with fields:
ID = bigint, Name = varchar(40) , UF = char(2)

I Inserted 1.000.000 record for test

I used Delphi XE with cxGrid (DevExpress 2.4) in 2 forms, 1 = FIBPLUS and 1 UNIDAC, to access FIREBIRD DB 2.5

cxGrids = GridMode = True, SmartRefresh, BufferRecords = 25lines(records), so, I dont used FETCHED ALL RECORD ok? jUST 25 record in OPEN table, in the forms. cxGrid in GRIDMODE is equal DBGrid (vcl delphi), load only "n" records = row grid

Time Open: +/- 0.7seconds in FIB and UNI components = OK!

But, in the browse, from 1 to 1.000.000 record: (ctrl+end key)

FibPlus: 7,5 seconds
UniDac: 11,4 seconds

I use some combinations UniDac, but always there was a diference +/- 2,5 seconds between Fib And UNI

--- QueryRecordCounts = off
--- Pooling
--- not detail tables
--- just options default
--- any others combinations

Can you help me in this task?

FIBPLUS components:
-- FIBDatabase (connection)
-- FIBTransacton1 (ready-only)
-- FIBTransacton2 (write)
-- FIBdataset1 (table)

UNIDAC components
--Uniconnection1 (connection)
--UniInterbase (provider)
--UniTable1 (table)
--UniTransaction1 (READ COMMITTED)
--UniTransaction2 (snapshot)

DevExpress components
-- cxGrid in gridmode=true, smartrefresh, bufferrecords=25
or
-- DBGrid (delphi native grid)

For counting time
-- function WinAPI (QueryPerformanceFrequency, QueryPerformanceCounter) updated for XE use
http://stackoverflow.com/questions/1739 ... ncecounter

QueryPerformanceFrequency function
http://msdn.microsoft.com/en-us/library ... s.85).aspx

QueryPerformanceCounter function
http://msdn.microsoft.com/en-us/library ... s.85).aspx

Note: I just use BROWSING the datas, i dont write nothing in the table

Thanks for your time,

Emailx45

ZEuS · Post by **ZEuS** » Thu 19 Apr 2012 10:03

Hello.

To configure UniDAC transactions with the specified parameters, you need to set the properties as follows:

UniTransactionRead.IsolationLevel := ilReadCommitted;
UniTransactionRead.ReadOnly := true;

UniTransactionWrite.IsolationLevel := ilSnapshot;
UniTransactionWrite.ReadOnly := false;

To use the transactions with UniTable you should assign UniTransactionRead to the Transaction property of UniTable and UniTransactionWrite - to the UpdateTransaction property of UniTable.

*********************************

You should use the TUniTransaction.Active property to determine whether the specified transaction is active.

*********************************

To compare the performance of FIB and UNI packages when browsing a large number of records, try the following:
- try not to use visual components like cxGrid;
- try to set the DeferredBlobRead and DeferredArrayRead options of the UniTable.SpecificOptions to True;
- try to set the UseUnicode option of the UniConnection.SpecificOptions to False;
- if you are using a local Firebird server, try to set the UniConnection.Server property to an empty value.

emailx45 · Post by **emailx45** » Tue 24 Apr 2012 16:16

Dear Friends,

I would like to know if is Possible one CUSTOM-TRANSACTION as FIBtransaction allow!

in FIBPLUStransacation I can make my custom transaction using the params (one-by-one) need. Not just choice the type isoliation!

In UNIDacTransaction is it possible?

ZEuS · Post by **ZEuS** » Wed 25 Apr 2012 08:52

Hello.

UniDAC provides a server-independent interface for working with different databases.
If you want to use server-specific functionality of Firebird server, it's better to use specific IBDAC components to work with it.
TIBCTransaction from an IBDAC package allows to set custom transaction parameters, not just isolation level.
You can learn more about IBDAC features here: http://devart.com/ibdac/features.html.