Purchases through FirebirdSQL shop generate commissions that fund open source Firebird development.

100% of profit from this item goes to Firebird Foundation to finance development of Firebird database.

Book: Detailed new features of Firebird 5, 2nd edition

The second edition of must-have book for Firebird SQL database developers who want to use the full potential of Firebird 5.

Time flies, and now we have the second edition of the must-have book for every Firebird developer. It has become bigger: we added a chapter about BLOB improvements which became available in the 5.0.3 minor release, and introduced many fixes of errors and typos.

1.5 years after the release, it is clear that Firebird 5 has become the fastest adopted version of Firebird: developers have migrated to it faster than to any other Firebird version, and their number is growing. The reasons are obvious: Firebird 5 is faster and more reliable than previous versions, and it has developer-friendly migration features which make migration very easy.

However, we noticed that many developers, even those who have migrated to v5, do not use all the modern features and therefore do not receive all possible improvements that are available in Firebird 5.

Among the most impactful new features, several standout capabilities require updating your existing SQL and PSQL code but deliver substantial performance and functionality gains. PARTIAL indices allow you to create highly selective indexes on specific row subsets using WHERE conditions, dramatically reducing index size and improving query speed for filtered data.

The SKIP LOCKED clause enables conflict-free queue processing and concurrent task management, requiring updates to your SELECT statements but eliminating deadlocks in multi-user scenarios.

Enhanced MERGE statements with the new WHEN NOT MATCHED BY SOURCE clause provide more powerful data synchronization capabilities, though existing MERGE operations will need syntax updates.

The RDB$BLOB_UTIL package introduces chunk-based BLOB reading and manipulation functions that require rewriting BLOB-handling procedures but offer significantly better memory efficiency and streaming capabilities.

Finally, the improved RETURNING clause now supports multiple record returns from INSERT, UPDATE, and DELETE operations, necessitating adjustments to existing PSQL procedures but enabling more efficient bulk operations with immediate result feedback. In this book you will find thorough, detailed explanations of new features and how to use them effectively.

In this book you will find thorough, detailed explanations of new features of Firebird 5 and how to use them effectively.

Table of Contents

Preface 2
Preface to the 2nd Edition of Detailed New Features of Firebird 5 2
Preface to the 1st Edition of Detailed New Features of Firebird 5 3
SQL Query Optimization: Faster Than Ever 3
Scalability: Growing with Your Data 3
Parallel Execution: Harnessing the Power of Modern Hardware 4
Prepared Statement Cache 4
Improved Compression of Records 4
SQL Query Profiling: Shining a Light on Performance Of Complex Stored Procedures 4
Wrapping Up and More Materials 5
Practical Migration Guide To Firebird 5 5
And, let's start! 5
1. New ODS and upgrade without backup-restore 7
2. Improving the data compression algorithm 8
3. Cache of prepared (compiled) statements 16
    3.1. A little theory 16
4. Tracing the COMPILE event 18
5. Per-table statistics in isql 20
6. Parallel execution of maintenance tasks 21
    6.1. Parallel execution of tasks in the Firebird kernel 21
        6.1.1. Practical recommendations for parameters 22
        6.1.2. Multi-threaded index creation or rebuild 22
    6.2. Parallel execution of maintenance tasks by Firebird tools 23
        6.2.1. Parallelism when performing backups using the gbak 23
        6.2.2. Parallelism when performing restore using the gbak 24
        6.2.3. Parallel manual sweep using the gfix tool 25
        6.2.4. Parallel icu update using the gfix utility 27
7. Improvements in Optimizer 28
    7.1. Cost estimation of HASH vs NESTED LOOP JOIN 28
    7.2. Cost estimation of HASH vs MERGE JOIN 30
    7.3. Transforming OUTER JOIN into INNER JOIN 31
    7.4. Converting subqueries to ANY/SOME/IN/EXISTS in semi-join 34
    7.5. Preliminary evaluation of invariant predicates 47
    7.6. Faster IN with list of constants 50
    7.7. Optimizer strategy ALL ROWS vs FIRST ROWS 53
    7.8. Improved plan output 55
    7.9. How to get stored procedure plans 58
8. New features in SQL language 60
    8.1. Support for WHEN NOT MATCHED BY SOURCE clause in MERGE statement 60
        8.1.1. WHEN MATCHED 61
        8.1.2. WHEN NOT MATCHED [BY TARGET] 61
        8.1.3. WHEN NOT MATCHED BY SOURCE 61
        8.1.4. Example of using MERGE with clause WHEN NOT MATCHED BY SOURCE 62
    8.2. Clause SKIP LOCKED 62
    8.3. Support for returning multiple records by operators with clause RETURNING 64
    8.4. Partial indices 65
    8.5. Functions UNICODE_CHAR and UNICODE_VAL 71
    8.6. Query expressions in parentheses 71
    8.7. Improved Literals 72
        8.7.1. Full syntax of string literals 72
        8.7.2. Complete syntax for binary literals 73
    8.8. Improved predicate IN 73
    8.9. Package RDB$BLOB_UTIL 74
        8.9.1. Using the function RDB$BLOB_UTIL.NEW_BLOB 74
        8.9.2. Reading BLOBs in chunks 75
9. Why SKIP LOCKED was developed? 78
    9.1. Preparing the Database 78
    9.2. Script simulating a job queue 79
    9.3. Clause SKIP LOCKED 85
    9.4. Job queue without conflicts 86
    9.5. Next steps 87
    9.6. Summary 89
10. Network Protocol Improvements 90
    10.1. Support for bidirectional cursors in the network protocol 90
    10.2. Improvements in BLOB transfer over the network 90
        10.2.1. How BLOB data is transferred over the network 90
        10.2.2. Using BLOB + VARCHAR together 94
        10.2.3. BLOB transfer improvements in Firebird 5.0.2 95
        10.2.4. BLOB transfer improvements in Firebird 5.0.3 96
        10.2.5. Comparison of BLOB transfer speed in different Firebird versions 97
        10.2.6. Article on transferring BLOB objects over the network 99
11. SQL and PSQL Profiling 100
    11.1. Starting a Profiling Session 101
    11.2. Pausing a Profiling Session 102
    11.3. Resuming a Profiling Session 102
    11.4. Finishing a Profiling Session 103
    11.5. Canceling a Profiling Session 103
    11.6. Resuming a Profiling Session 103
    11.7. Finishing a Profiling Session 104
    11.8. Canceling a Profiling Session 104
    11.9. Discarding Profiling Sessions 104
    11.10. Flushing Profiling Session Statistics to Snapshot Tables 104
    11.11. Setting the Statistics Flush Interval 105
    11.12. Snapshot Tables 105
        11.12.1. Table PLG$PROF_SESSIONS 105
        11.12.2. Table PLG$PROF_STATEMENTS 106
        11.12.3. Table PLG$PROF_REQUESTS 106
        11.12.4. Table PLG$PROF_CURSORS 107
        11.12.5. Table PLG$PROF_RECORD_SOURCES 107
        11.12.6. Table PLG$PROF_RECORD_SOURCE_STATS 108
        11.12.7. Table PLG$PROF_PSQL_STATS 109
    11.13. Auxiliary Views 109
    11.14. Profiler Launch Modes 110
        11.14.1. Option DETAILED_REQUESTS 110
        11.14.2. Running the profiler in a remote connection 117
    11.15. Examples of using the profiler to find "bottlenecks" 119
12. Conclusion 129

Purchase now: EUR €16/ USD $19.99

Remember that purchasing services and products in Firebird SQL shop directly funds the development of free open source Firebird SQL.