I dont mind dropping the index, I was always under the assumtion that indexes were always good. So, are you thinking not using an index at all? Is there a way to get the table in to a natural order thats the same as the index?

----- Original Message -----
From: Aage Johansen
To: ibobjects@yahoogroups.com
Sent: Tuesday, August 26, 2008 12:24 PM
Subject: Re: [IBO] Performance..


Gordon Hamm wrote:
> Hi,
> Im using Firebird 2.1.
> I have a table that has about 8 fields of prices and a date/timestamp field.
> There are about 7 million records.
> There is only one index, and thats on the time stamp field.
> I have a routine that does a simple sql select and orders on the date/time
> field (That has an index)
>
> I then loop through the entire table, and do some complex math etc on the
> data.. top to bottom..
> Then, I do it again and again, testing some modeling parameters or
"What if"
> stuff. I will litterally loop through it 500 times..
>
> No, there is no way to do this in a stored proc..
>
> Anyway, It works fine, but I was doing it on my pentium 4, took about 5
> minutes for each iteration.
> memory usage was small, and CPU was only about 30 %.
>
> I went out and bought a quad core , with more memory, much faster cpu and
> bus, faster drives etc..
>
> Its only a tiny bit faster now.. Im so discouraged with it.. I can imagine
> why its not many times faster..
>
> Any idea what the bottle neck might be? I am doing a unidirection query, to
> save memory, that helps, Im not using "Fieldbyname", getting the field
> directly by TempQuery.Fields.Fields[12].AsFloat.
>
> I dont know what else to do..
>
> Gordon
>

If you do
Select * from HedgeData Order By TradeDateTime
on 7 million rows, you should note that ordering on an indexed field
isn't necessarily a good thing. Firebird will be chasing records in
index order which may mean a lot of disk arm movement. Try to order
without using the index. If you don't want to drop the index you
could try this version of the select:
Select * from HedgeData Order By TradeDateTime+0
Firebird wil (probably) fetch all rows in storage order, and then
apply a sort (which usually is quite fast).

--
Aage J.





[Non-text portions of this message have been removed]