Subject Re: [firebird-support] gfix -sweep Versus gbak -g
Author
Post date 2013-11-04T20:37:03Z
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: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de
paul_mal...@...
Enviada em: segunda-feira, 4 de novembro de 2013 11:05
Para: firebird-support@yahoogroups.com
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.