New features of Interbase 6.5
Author: Bill Todd (Team B members)
Translator: TR @ SOE
Original URI: http://www.dbginc.com/tech_pprs/ib65new.html
1. Improved cache management
2. Specify the processor
3. Cancel the execution of the statement
4. Metadata security
5. 64-bit file I / O
6. ROWS statement
7. Export data in XML format
8. Other improvements
Improved cache management
In previous versions of IB, if the cache size exceeds 10,000 pages, performance will decrease. This issue has been resolved. The cache manager has also been modified to reduce additional overhead when processing large bursts. For example, the total amount required for the 65,000 page cache is almost seven megabytes. The speed of allocating large cache is also improved, and the efficiency of cache I / O is also improved. In the case of a heavy load, the problem that the thread needs to wait for the shared page to lock. Increasing the cache size does not guarantee performance improvement, but there is absolutely no harm. Increasing the cache size will help when the number of users is large and other occasions.
Specify processor
In multiprocessor systems running Windows, the performance of IB hyper server version may be reduced because Windows always constantly switching the IB process from one processor to another. The Windows version of IB 6.5 can contain the following instructions in the IBConfig file: CPU_AFFINITY 1 to connect the IB process with the first processor. By changing the value, you can connect IB to any one processor, and the method is to modify the bit of the corresponding processor. For example, 1 represents the first processor, 2 represents the second, 4 represents the third. This syntax allows you to assign ib on multiple processors (IB 7 will support SMP). For example, when IB is assigned to the second and third processors, you can write: CPU_AFFINITY 6.
Cancel the execution of the statement
The execution of any statement can be canceled using the API function ISC_DSQL_Free_Statement of IB. As follows:
PROCEDURE TFRMMAIN.STOPQUERY;
VAR
StatementHandle: TISC_STMT_HANDLE;
ISC_RESULT: ISC_STATUS;
Begin
StatementHandle: = Dmmain.ibqtest.stmthandle;
ISC_RESULT: = ISC_DSQL_Free_Statement (statusvector, @statementhandle, dsql_cancel);
IF ISC_RESULT> 0. ibdatabaseerror;
END;
The execution of the statement cannot be canceled in the thread of the statement. Therefore, if you want to use this feature, you should run your SQL statement in a background thread. The API call cannot be used with DBEXPRESS or BDE because they cannot access the handle of the statement, and the handle is the second parameter of the function call. This property is provided in Interbase Express, which is derived from an application using IBQuery, which performs SQL statements in a background thread.
When you cancel the execution of a statement, the performance of the IB is like this statement execution. All changes made by the canceled statements are canceled, but transaction processing will continue. You can decide whether to confirm or roll back the transaction. If you perform a statement in ISQL, you want to cancel, just press CTRL C.
Metadata security
In previous versions of IB, you can authorize or unauthorize system tables, but these permissions are not retained after backup and recovery. IB 6.5 is different. The default permissions for all system forms in any database you create are SELECT. It allows users to see system forms, but they can't directly modify system forms. IB 6.5 has three scripts that make you easily modify the permissions of the system form. The readmeta.sql script gives the system form to SELECT permissions. Writemeta.sql scripts give the system form to all permissions. BLINDMETA.SQL scripts reclaim all permissions. The access to the restriction system table is never restricting the ability to modify other forms in the database using the DDL statement. The IB engine has improved, even if the user does not have the right to directly operate the system form, the engine can also modify the system table instead of the user.
If you use a blindmeta.sql script or use the revoke statement in early IB to recover all permissions access to the system table, you will not be able to access the database at all. IB 6.5 When opening the database of IB 6 ODS 10 for the first time, the ODS version will automatically rise to 10.1, and the public user will be set to SELECT for the permissions of the system table. IB 6.5 can also open the ODS 9 database, but will not make any modifications.
IB 6.0 can open an ODS 10.1 database as long as the public user is at least SELECT for the permissions of the system table. However, if the database is backed up and recovered by IB 6.0 server, all access limits for system tables will be lost.
64-bit file
IB now supports 64-bit file I / O operations on the Windows / Linux / Solaris platform. The size of the database will only be limited to the size of the hard disk. Only 2.4 core Linux provides 64-bit file I / O. If it is the previous version of Linux, IB 6.5 will automatically use 32-bit file I / O, which represents like IB 6.0.
ROWS statement [1]
The ROWS statement is an extension of IB SQL, which allows you to control the scope of the SELECT / UPDATE / DELETE statement. The syntax of the ROWS statement is as follows:
Rows
The location of the ROWS statement in the SQL statement is as follows:
It is best to understand what the ROWS statement can do is to look at the example. All examples in this article use IB's own Employee database. In the first example, the ROWS statement is used by ORDER BY, selects five records of the highest discount:
SELECT *
Order by Discount Desc
Rows 1 to 5
The ORDER BY statement will be returned in descending order in Discount, and the ROWS statement limits the number of records returned to the top five. Although you can use ROWS independently without ORDER BY, there is no significance, because the number of restrictions returns line means you need to arrange the previous N records in some order. If you need to return, the first line of the result is the first line of the record set, you can put the above queries as:
SELECT *
Order by Discount Desc
ROWS 5
If you want to have the same column value in the ORDER BY statement, you can use the with TIES option:
SELECT *
Order by Discount Desc
Rows 1 To 5 with Ties
This query will return 8 rows instead of 5 lines, as some data in the previous five-line discount fields also appear in other rows. Note that you must use the Order by when you use the with TIES. In addition to the fixed number of columns, you can also specify the number of rows in the return result data set. As shown in the following example, it will return TOTAL_VALUE records within the first 10%:
SELECT *
Order by Total_Value DESC
Rows 10 percent
The Percent Options Another interesting application is to select a average distributed row set. The following query returns five lines, each line is 20% record number of distances with its neighbors in the table. Although the query does not use Order By, it is also possible to use Order By. For example, to obtain a line according to a uniformly distributed location, you can sort the table according to the state name or ZIP.
SELECT * FROMER
Rows 100 by 20 percent
The ROWS statement can be used in combination with statistics. The following query returns the information of the top three salespersons based on the total orders of the salesperson. The second query returns the top 25% of the salesperson information according to the total orders of the salesperson:
SELECT SALES_REP, SUM (TOTAL_VALUE) AS Total
From sales
GROUP BY SALES_REP
ORDER BY 2 DESC
Rows 3
SELECT SALES_REP, SUM (TOTAL_VALUE) AS Total
From sales
GROUP BY SALES_REP
ORDER BY 2 DESC
Rows 25 percent
The ROWS statement can be combined with Update to add the highest 10 employees with the highest salary:
Update Employee
SET SALARY = SALARY * 1.01
Order by Salary DESC
ROWS 10
You can also use the Rows to delete to delete the top 10 employees with the highest salary. If you want to delete the employee as the top 10 employee salary, just join the TIES option:
DELETE FROM EMPLOYEEEE
Order by Salary DESC
ROWS 10
Export data in XML format
Interbase Express now contains a new class, TiboutputXML, so you easily read data with XML. The property of TiboutputXML is listed in the table below. To use this class, you must include the IBXML unit in the file. Interbase XML support is implemented in standalone DLL, so if you don't need this feature, you don't have to distribute the file.
Attributes
description
HEADERTAG
The XML stream is a string that is marked as a head. If it is empty, use "
As a string that is marked as a database. Use "Database" if it is empty. Tabletag
String as a table labeled. Use "table" if it is empty. RowTag
As a string of row markers. Use "row" if it is empty. Flags
There are three members of the property set. XMlattribute: Generate XML instead of the mark; XMLDisplayNull: The null value is included in the XML data; XMLNOHEADER: Do not include header information in XML. Stream
Receive the stream of XML.
The following code demonstrates how to export to XML and display XML in a note control in the Delphi form:
Procedure TMAINFORM.SHOWXMLBTNCLICK (Sender: TOBJECT); VAR
IBOUTPUTXML: TIBOUTPUTXML;
Stream: Tstringstream;
Begin
Iboutputxml: = TiboutputXml.create;
Stream: = TStringStream.create ('');
Try
IBOUTPUTXML.HEADERTAG: = '' # 10 # 13 '' # 10 # 13;
IBOUTPUTXML.DATABASETAG: = DatabaseEdit.Text;
Iboutputxml.tabletag: = TableEdit.Text;
Iboutputxml.rowtag: = Rowedit.Text;
IboutputXml.stream: = stream;
IBOUTPUTXML.FLAGS: = [];
if attributescb.checked the
IBOUTPUTXML.FLAGS: = IBOUTPUTXML.FLAGS [XMLATITRIBUTE];
if Nullcb.checked Then
IBOUTPUTXML.FLAGS: = IBOUTPUTXML.FLAGS [XMLDISPLAYNULL];
if noheadercb.checked the
IBOUTPUTXML.FLAGS: = IBOUTPUTXML.FLAGS [XMLNOHEADER];
SelectData;
OutputXML (IBSQL1, IBOUTPUTXML);
Stream.position: = 0;
Memo1.Lines.LoadFromstream (stream);
Finally
Iboutputxml.free;
Stream.free;
Ibsql1.close;
Employeetran.commit;
Employeedb.connected: = false;
END;
END;
Procedure tMainform.selectdata;
Begin
Employeedb.databaseName: = DatabasePathedit.Text;
Employeedb.connected: = true;
Employeetran.StartTransaction;
Ibsql1.sql.clear;
Ibsql1.sql.add (sqledit.text);
END;
Other improvements
InterClient 2.5, the latest version of Interbase JDBC driver (upgraded and compatible with JRE 1.3). That is to say, to use InterClient 2.5, you must have Java 2.
When the previous Interbase transmits the VARCHAR field from the server side, the space is filled with the space to make up the field length. This can cause performance problems on a slow or high load network. This has been improved, and now only the actual content in the varchar field is transmitted.
Ibconsole has also been improved, each of which contains a separate INDEXES node. After selecting this node, all index information to be grouped is displayed: index name, sort, whether the index contains the field, the order of the field, and whether the index is activated. If the index is part of a foreign key constraint, the name of the main index it is referenced will also be displayed.
in conclusion
Although it is not upgraded to IB 6.5, it still provides a lot of new features. 64-bit file I / O will make the operating large database easier, and improved cache management has brought better performance. Metadata security can protect your database design, the ROWS statement can control the number of records that the DML statement affects. The feature exported to XML will make web application development easier. This upgrade is still worth it. [1] I personally think this is the most important improvement. - Translator Note
