Tim

I'd pretty well (in my own mind at least) figured out the relative benefits of tables and queries in DBISAM. Can you give guidance for ElevateDB please?

I haven't converted a big enough table yet to test things out properly so its things like eg

SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName

and then allowing the user to set a filter on _BoxName. For a large table (say 150k records), with _BoxName indexed, would this be fast or would I need to alter the query?


Any comments about other areas would be much appreciated.

Roy Lambert

Roy Lambert wrote:
> Tim
>
> I'd pretty well (in my own mind at least) figured out the relative benefits of tables and queries in DBISAM. Can you give guidance for ElevateDB please?
>
> I haven't converted a big enough table yet to test things out properly so its things like eg
>
> SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName
>
> and then allowing the user to set a filter on _BoxName. For a large table (say 150k records), with _BoxName indexed, would this be fast or would I need to alter the query?
>
>
> Any comments about other areas would be much appreciated.

That should work. Keep in mind that if you are not using a Live Query,
then a static query has to send a copy of the results to a temporary
table. For small tables, 5k or less, this is fine. But for >100k tables
static queries will take time. Also static queries are not updateable.
So if you have a form or updateable grid attached to a static query, you
need to handle the update mechanism yourself.

Dave

Roy,

<< I'd pretty well (in my own mind at least) figured out the relative
benefits of tables and queries in DBISAM. Can you give guidance for
ElevateDB please?

I haven't converted a big enough table yet to test things out properly so
its things like eg

SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName

and then allowing the user to set a filter on _BoxName. For a large table
(say 150k records), with _BoxName indexed, would this be fast or would I
need to alter the query? >>

What Dave said is correct - make sure the RequestSensitive property is set
to True, and you'll be fine.

BTW, testing out Harry's billion rows stored procedure, I now have a table
with 55 million rows in it, and it works just fine in the scenario that you
describe.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Roy,
>
> << I'd pretty well (in my own mind at least) figured out the relative
> benefits of tables and queries in DBISAM. Can you give guidance for
> ElevateDB please?
>
>  I haven't converted a big enough table yet to test things out properly so
> its things like eg
>
>  SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName
>
>  and then allowing the user to set a filter on _BoxName. For a large table
> (say 150k records), with _BoxName indexed, would this be fast or would I
> need to alter the query? >>
>
> What Dave said is correct - make sure the RequestSensitive property is set
> to True, and you'll be fine.
>
> BTW, testing out Harry's billion rows stored procedure, I now have a table
> with 55 million rows in it, and it works just fine in the scenario that you
> describe.
>

Tim,
    What happened to the other 945 million rows? What happens if you
need a case insensitive index on a table that large? Is it going to be
slower building the index or accessing the rows?

Dave

Dave


Blast I was going to ask that question

Roy Lambert

Dave,

<< What happened to the other 945 million rows? >>

It rightly crapped out with a "table full" message when it reached 4 gigs (I
didn't turn on LargeFileSupport on purpose .

<< What happens if you need a case insensitive index on a table that large?
Is it going to be slower building the index or accessing the rows? >>

It will be slower than a smaller table, sure.  But it should be usable just
fine.   Even though you can't see it, internally there's already an index on
the table even if you don't define any.   It is used to provide insert
ordering by when the insert occurred (internal row ID).

--
Tim Young
Elevate Software
www.elevatesoft.com