Implementation limit exceeded. Block size exceeds implementation restriction


This error message can show up for different reasons:

1. Your SQL statement has breached the limit for SQL statement size. The limit is 64KB for statement text, 64KB for compiled BLR (see FAQ #187 to learn what BLR is) and 48KB for execution plan.

2. The returned row size for SELECT statements is bigger than 64KB.

- Float, Integer, Date and Time columns take 4 bytes.

- Timestamp, BIGINT and Double Precision columns take 8 bytes.

- Smallint takes 2 bytes.

- Numeric and decimal columns take between 2 and 8 bytes, depending on the precision.

- Varchar and char columns take more space if you use multibyte character sets. For example, UNICODE uses 3 bytes per character and UTF8 internaly uses 4 bytes per character. For example, you would get this message if try to select three CHAR(8000) columns. To work around this you can use SUBSTRING to select less data.

3. If you use IN try converting it to EXISTS as IN gets converted to a lot of OR statements internally.

4. If you are joining views who are UNIONs, plan could be huge and complex - try writing views that cover only those tables you really need for this particular query. If nothing helps, perhaps you can break it into several queries, or write a stored procedure to do some part of the job.


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.



All contents are copyright © 2007-2024 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous