I am not using a stored procedure for my slow query. "select first 0 ..." does instantly return with no rows, however it does not help the next query run any faster.
I just discovered that removing "DISTINCT" from the "SELECT" statement causes the query to run in the expected time with Firebird 2.5. With Firebird 2.1 using "DISTINCT" does cause the query to run very slightly slower (about 0.25 seconds with DISTINCT and about 0.15 seconds without DISTINCT). However, when using "DISTINCT" with version 2.5, it sometimes takes several seconds to run with "DISTINCT" but less than 1 second without "DISTINCT". The query I'm using for testing returns the same number of rows with our without "DISTINCT". It seems like "DISTINCT" is causing the Firebird 2.5 optimizer a problem or perhaps there is a bug. I would like to be able to use "DISTINCT" in some of my queries, but there is too big of a performance hit in version 2.5. Does anyone have any suggestions? Thanks, Paul Fabiano - Desenvolvimento SCI Mon, 04 Nov 2013 06:42:52 -0800 Hello Paul, I will take a look in your post latter. The problem is exactly the same. I discovered something: In my case, I ran a command in "QUERY A" (is an auto generated stupid code): "Select * from my_table" With "QUERY A" opened, I run my report over the my_table table. Then, I change my code of "QUERY A" to: "select first 0 * from my_table" Then my report runs all times at the same fast speed! Except first run is slower maybe because it is freeing something. The problem appears to be some king of table locking! It only occurs inside the same connection. When another user runs "select * from my_table" and I ran my report everything is fine. My report is generated by a stored procedure, I don't know if this have any influence. Take a look about this and please let me know any news. Fabiano. I forgot to say: We use an application server, we stop the server at 10PM do disconnect/release all connections (and transactions) restart the application server and then run gbak (suppressing -g parameter) and then after this run gfix -sweep to effectively remove old record versions from the database. This makes our application run smoth the entire day, day after day. De: [email protected] [mailto:[email protected]] Em nome de [email protected] Enviada em: segunda-feira, 4 de novembro de 2013 11:05 Para: [email protected] Assunto: Re: [firebird-support] gfix -sweep Versus gbak -g Hello Fabiano, Did you find a solution to the reports running slower with each successive run? I am having a similar problem that started when I upgraded from Firebird version 2.1 to version 2.5. My SQL queries run consistently fast with Firebird 2.1, but when using Firebird 2.5, the query is as fast as 2.1 only for the first run. Then each successive run of the query with Firebird 2.5 takes longer than the previous run. Disconnecting and reconnecting to the database will cause the query to run in the expected time for the first run again, but then each run continues to slow down again until disconnection/reconnecting. Please see my post to the firebird-support mail list on October 24, 2013 titled "Query Performance Problem after Upgrading from Firebird 2.1 to 2.5". No one has responded to my issue. The only update that I have is I have also tried the Firebird Jaybird 2.2.3 JDBC driver and have the same problem with Firebird 2.5 but not 2.1. Thank you, Paul --------------------------------------------------------------------------------- Confidentiality Notice: This message is the property of the United States Bankruptcy Court for the Western District of Kentucky. It may be legally privileged and/or confidential and is intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or taking any action in reliance on the information contained herein is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this message.
