Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Simple query very slow


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Jan 19, 2016 5:43 PM Last Post By: Corey Marques
Corey Marques

Posts: 3
Registered: 12/21/06
Simple query very slow  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 19, 2016 2:40 PM
I've posted this question on StackOverflow but there may be some people here that have seen this issue.

I have a firebird database with a table that has 2 columns, recordID:integer and a blob. There are 246k records. I have an index on recordid.

if i run the query:
select a.recordID from ARTrans a


It takes 20+ seconds to execute, but if I NULL out the blob field on all records, it returns in under a second.

I've garbage collected, recreated indexes, removed indexes and it's still super slow.

The table started as a more complex table with 25 columns but in debugging I've narrowed it down to those two fields.

The history of the issue and many more details are included here: [http://stackoverflow.com/questions/34816178/simple-firebird-query-very-slow]

Any help would be greatly appreciated.
quinn wildman

Posts: 156
Registered: 4/20/07
Re: Simple query very slow  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 19, 2016 2:59 PM   in response to: Corey Marques in response to: Corey Marques
Corey Marques wrote:
I've posted this question on StackOverflow but there may be some people here that have seen this issue.

I have a firebird database with a table that has 2 columns, recordID:integer and a blob. There are 246k records. I have an index on recordid.

if i run the query:
select a.recordID from ARTrans a


It takes 20+ seconds to execute, but if I NULL out the blob field on all records, it returns in under a second.

I've garbage collected, recreated indexes, removed indexes and it's still super slow.

The table started as a more complex table with 25 columns but in debugging I've narrowed it down to those two fields.

The history of the issue and many more details are included here: [http://stackoverflow.com/questions/34816178/simple-firebird-query-very-slow]

Any help would be greatly appreciated.
Do you need to fetch every row? Unless you have a report, that's
unlikely. So, just fetch a reasonable amount of rows and your speed
should be fine,

--
I used to work for Embarcadero - I don't work there any more.
Corey Marques

Posts: 3
Registered: 12/21/06
Re: Simple query very slow  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 19, 2016 5:42 PM   in response to: quinn wildman in response to: quinn wildman
The follow statement suffers the same slow down. All the time is taken up building the PLAN

SELECT first 1000 a.RECORDID
FROM ARTRANS a order by recordID


Here's the plan output from FlameRobin

Preparing query: SELECT first 1000 a.RECORDID
FROM ARTRANS a order by recordID
Prepare time: 19.835s
Field #01: ARTRANS.RECORDID Alias:RECORDID Type:INTEGER
PLAN (A ORDER IDX_ARTRANSRECID)
 
Executing...
Done.
3460 fetches, 0 marks, 24 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 1001 index, 0 seq.
Delta memory: 8540 bytes.
Total execution time: 19.842s
Script execution finished.


Edited by: Corey Marques on Jan 19, 2016 5:42 PM
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02