Gareth Marshall wrote:
When attempting to run queries with a large number of expressions in the where clause, I have found a difference in behaviour between embedded and classic server:
Note that this is a simplified case from my application's actual behaviour.
I am using Firebird 2.5.2 Security Update 1 build 26540, 64-bit on Windows 7 64-bit. I am testing using application developed with Delphi XE2 Update 4 Hotfix 1 and IBObjects 5.2.0 Build 6. I am running my tests on Windows 7 SP1 64-bit. The same database file is used for all tests. The database is using the UTF8 character set, as are the connections to the database.
I generate a query that includes a number of tests ORed to each other. Each test just checks to see if the NAME field is equal to the string form of a number one larger that the one before, producing n terms in the WHERE clause.
For example, if n is 5, the following query is used:
SELECT * FROM Bldg B WHERE
B.Name = '1'
OR B.Name = '2'
OR B.Name = '3'
OR B.Name = '4'
OR B.Name = '5'
When run against classic server, I can have at most 3173 terms in the WHERE clause before the query string itself reaches a length of 65530 characters and then stops working. This is pretty much expected behaviour.
When run using the embedded engine, I can successfully have 1052 terms in the WHERE clause. In my tests, 1053, 1054, and 1056 terms in the WHERE clause cause Firebird to hang when attempting to prepare the query (1052 terms would prepare in less than a second; after 30 seconds I killed the test for these values). Testing with 1088 or more terms causes the test application to crash with a stack overflow in the fbembed.dll. 1052 terms has a query string of length 20989 characters, while 1053 terms has a query string of length 21010.
I have not tested any other configurations.
Is there a difference to the limits on queries that can be run when working with the embedded engine that I need to be aware of to avoid this hanging/crashing behaviour?
Dmitry Yemanov answers (in tracker):
The difference is caused by differing stack sizes. The Firebird server binaries are compiler with a 2MB stack for 32-bit and a 4MB stack for 64-bit, and setting Delphi to use the same stack size should resolve the issue.