Hello,

I have table with about 5 000 000 of records.

Simple queries like SELECT * FROM "Cards" WHERE PoductID=25
executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM).

ProductID field is indexed of cource.
RequestLive is true
Session is Local.

Filter speed with TDBIsamTable is very slow to. Simple expression
like ProductID=25 is slow as query (40+ seconds).

It's possible to speed-up query execution of large tables ?
Or DBISam is not for large tables ?

Thanx.

Hi Max,

How large is the result set?

Are you doing this query from within DBSYS, or your own application? (If
your own application, can you try it in DBSys, just to make sure you're
haivng the same results)?

What antivirus software are you running? Does it make a difference if you
disable it?

Cheers

Adam.

"Max Terentiev" <maxterentiev@mail.ru> wrote in message
news:E40564DA-93DE-4232-AAAC-CA334059922C@news.elevatesoft.com...
> Hello,
>
> I have table with about 5 000 000 of records.
>
> Simple queries like SELECT * FROM "Cards" WHERE PoductID=25
> executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM).
>
> ProductID field is indexed of cource.
> RequestLive is true
> Session is Local.
>
> Filter speed with TDBIsamTable is very slow to. Simple expression
> like ProductID=25 is slow as query (40+ seconds).
>
> It's possible to speed-up query execution of large tables ?
> Or DBISam is not for large tables ?
>
> Thanx.
>

"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote:

>Hi Max,

>How large is the result set?

Result set is very large to (at least 500 000 records).

>Are you doing this query from within DBSYS, or your own application?

In my app.

>(If your own application, can you try it in DBSys, just to make sure you're
>haivng the same results)?

Same results

>What antivirus software are you running? Does it make a difference if you
>disable it?

All antiviruses disabled. My application is only running program.

Cheers

Adam.

"Max Terentiev" <maxterentiev@mail.ru> wrote in message
news:E40564DA-93DE-4232-AAAC-CA334059922C@news.elevatesoft.com...
> Hello,
>
> I have table with about 5 000 000 of records.
>
> Simple queries like SELECT * FROM "Cards" WHERE PoductID=25
> executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM).
>
> ProductID field is indexed of cource.
> RequestLive is true
> Session is Local.
>
> Filter speed with TDBIsamTable is very slow to. Simple expression
> like ProductID=25 is slow as query (40+ seconds).
>
> It's possible to speed-up query execution of large tables ?
> Or DBISam is not for large tables ?
>
> Thanx.
>

I try to set GeneratePlan option and see strange result
in Plan log after executing query:

-------------------------------------------------------
SELECT * FROM Probes1 WHERE DirectID<5000

Tables Involved
---------------

Probes1 (Probes1) table opened shared, has 923749 rows

Result Set Generation
---------------------

Result set will be live

Result set will consist of one or more rows

Result set will be ordered by the primary index for the table Probes1

WHERE Clause Execution
----------------------

The expression:

DirectID < 5000

is UN-OPTIMIZED, covers 923749 rows or index keys, costs 1041988872 bytes, and
will be applied to the Probes1 table (Probes1) before any joins

================================================================================
>>>>> 11550 rows affected in 28,25 seconds
================================================================================

Why it's UN-OPTIMIZED ???? DirectID is indexed non-unique field. What must i do to make
this query
OPTIMIZED ???

Hi Max,

Sorry - I should have asked. What version of DBISam are you running?

Best Regards

Adam.

"Max Terentiev" <maxterentiev@mail.ru> wrote in message
news:AE9ABD4E-2870-4CDB-8383-BA13C0A2CC2C@news.elevatesoft.com...
> "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote:
>
>>Hi Max,
>
>>How large is the result set?
>
> Result set is very large to (at least 500 000 records).
>
>>Are you doing this query from within DBSYS, or your own application?
>
> In my app.
>
>>(If your own application, can you try it in DBSys, just to make sure
>>you're
>>haivng the same results)?
>
> Same results
>
>>What antivirus software are you running? Does it make a difference if you
>>disable it?
>
> All antiviruses disabled. My application is only running program.
>
> Cheers
>
> Adam.
>
> "Max Terentiev" <maxterentiev@mail.ru> wrote in message
> news:E40564DA-93DE-4232-AAAC-CA334059922C@news.elevatesoft.com...
>> Hello,
>>
>> I have table with about 5 000 000 of records.
>>
>> Simple queries like SELECT * FROM "Cards" WHERE PoductID=25
>> executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM).
>>
>> ProductID field is indexed of cource.
>> RequestLive is true
>> Session is Local.
>>
>> Filter speed with TDBIsamTable is very slow to. Simple expression
>> like ProductID=25 is slow as query (40+ seconds).
>>
>> It's possible to speed-up query execution of large tables ?
>> Or DBISam is not for large tables ?
>>
>> Thanx.
>>
>
>

Hi Max,

Sorry - I should have asked. What version of DBISam are you running?

Plan log says 4.24 build 1:
================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

SELECT * FROM Probes1 WHERE DirectID<5000

Tables Involved
---------------

Probes1 (Probes1) table opened shared, has 923749 rows

Result Set Generation
---------------------

Result set will be live

Result set will consist of one or more rows

Result set will be ordered by the primary index for the table Probes1

WHERE Clause Execution
----------------------

The expression:

DirectID < 5000

is UN-OPTIMIZED, covers 923749 rows or index keys, costs 1041988872 bytes, and
will be applied to the Probes1 table (Probes1) before any joins

================================================================================
>>>>> 11550 rows affected in 28,25 seconds
================================================================================

Why it's un-optimized ? DirectID is non-unique indexed field !

Hi Max,

> Why it's un-optimized ? DirectID is non-unique indexed field !

Do you have an index on DirectID where directID is the only field in the
index? If not, can you please create an index with DirectID being the only
field in the index, and see if this optimizes the query?

Thanks & Regards

Adam.

Max,

<< Why it's UN-OPTIMIZED ???? DirectID is indexed non-unique field. What
must i do to make this query OPTIMIZED ??? >>

Could you send me an empty table structure for the table ?  My initial guess
is that DirectID is not the first field in any of the indexes, which is a
requirement in DBISAM for optimization:

http://www.elevatesoft.com/dbisam4d5_optimizations.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

>Could you send me an empty table structure for the table ?  My initial guess
>is that DirectID is not the first field in any of the indexes, which is a
>requirement in DBISAM for optimization:

I was change table structure and create individual Index for DirectID field
(before i have one index for several fields include DirectID).

Now query is optimized (in Plan log) and execute fasten - about 17-20 seconds.
But it's still to slow for me !

My table contain about 5 000 000 of records.
Result set for SELECT * FROM Probes WHERE DirectID=X may be very large
(from 50 000 to 500 000 records).

I use DirectID field for Master-Detail link. So, navigation in Master table is not
possible (user must wait up to 20 seconds before active record in Master table changed).

"Max Terentiev" <maxterentiev@mail.ru> wrote in message
news:733FD2AE-FB8E-4F72-AE66-7930081AFB25@news.elevatesoft.com...
>
> My table contain about 5 000 000 of records.
> Result set for SELECT * FROM Probes WHERE DirectID=X may be very large
> (from 50 000 to 500 000 records).
>

Why in the world would you want to scroll thru a master table that displays
50K details? Seems some redesign might be in order. But if you MUST, try
using tTables, in my experience they are much faster for master/detail,

Robert