Subject Re: [IBO] Performance..
Author Gordon Hamm
Took longer to load and sort the data, when it actually looped, it wasnt any faster..
about the same from what I could tell.

----- Original Message -----
From: Aage Johansen
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]