Tim,

I encounter a strange speed problem in 1.03 b1 I don't know if it happened
before, but I doubt it.

When quering a table with an index on an integer field veld1 (table of 1
million rows)

select * from test where veld1 = 100
is SLOWER then
select * from test where veld1 between 1000 an 20999
when requestsensitive = TRUE

This seems strange to me, annyone can confirm this?

Regards, Harry

Harry,

<< When quering a table with an index on an integer field veld1 (table of 1
million rows)

select * from test where veld1 = 100
is SLOWER then
select * from test where veld1 between 1000 an 20999
when requestsensitive = TRUE

This seems strange to me, annyone can confirm this? >>

If you are only selecting one row out of 1 million, then you are better off
using an insensitive result set due to the way EDB will have to navigate the
result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Ah.. I see. Ok. So I guess there are no rules then for knowing what is
faster for a certain statement (requestsensitive or not). It depends on how
many rows the table holds, the statement etc.

Regards, Harry



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:01CF854F-2520-4FB3-8B19-26FFC0364976@news.elevatesoft.com...
> Harry,
>
> << When quering a table with an index on an integer field veld1 (table of
1
> million rows)
>
>  select * from test where veld1 = 100
>  is SLOWER then
>  select * from test where veld1 between 1000 an 20999
>  when requestsensitive = TRUE
>
>  This seems strange to me, annyone can confirm this? >>
>
> If you are only selecting one row out of 1 million, then you are better
off
> using an insensitive result set due to the way EDB will have to navigate
the
> result set.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Tim,

So, with requestsensitive to TRUE I tested:
SELECT * FROM test
WHERE veld1 between 10001 and 10002 //2 records out of 1,000,000

and it's faster then
SELECT * FROM test
WHERE veld1 = 10001 (or veld1 between 10001 and 10001)

I guess it's is my lack of understanding here, but I can't figure out why 1
out of a million is so different the two out of a million). Could you
explain it some more (or point to docs where I could read more about this)?

Regards, Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:01CF854F-2520-4FB3-8B19-26FFC0364976@news.elevatesoft.com...
> Harry,
>
> << When quering a table with an index on an integer field veld1 (table of
1
> million rows)
>
>  select * from test where veld1 = 100
>  is SLOWER then
>  select * from test where veld1 between 1000 an 20999
>  when requestsensitive = TRUE
>
>  This seems strange to me, annyone can confirm this? >>
>
> If you are only selecting one row out of 1 million, then you are better
off
> using an insensitive result set due to the way EDB will have to navigate
the
> result set.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry

> SELECT * FROM test
> WHERE veld1 between 10001 and 10002 //2 records out of 1,000,000
>
> and it's faster then
> SELECT * FROM test
> WHERE veld1 = 10001 (or veld1 between 10001 and 10001)
>
> I guess it's is my lack of understanding here, but I can't figure out why
> 1
> out of a million is so different the two out of a million). Could you
> explain it some more (or point to docs where I could read more about
> this)?

The question here is not one record different from two records but the way
of comparation the data. In the first case the engine will have to test
between two values and it can be 2 records or 200k records instead of second
query that is much more specific.

Eduardo

Jose,

I understand that, but shouldn't the more specific be faster, that's what I
don't get.

Regards, Harry

"Jose Eduardo Helminsky" <contato@hpro.com.br> schreef in bericht
news:C01813EB-5C05-4E58-961F-B6823958D2DA@news.elevatesoft.com...
> Harry
>
> > SELECT * FROM test
> > WHERE veld1 between 10001 and 10002 //2 records out of 1,000,000
> >
> > and it's faster then
> > SELECT * FROM test
> > WHERE veld1 = 10001 (or veld1 between 10001 and 10001)
> >
> > I guess it's is my lack of understanding here, but I can't figure out
why
> > 1
> > out of a million is so different the two out of a million). Could you
> > explain it some more (or point to docs where I could read more about
> > this)?
>
> The question here is not one record different from two records but the way
> of comparation the data. In the first case the engine will have to test
> between two values and it can be 2 records or 200k records instead of
second
> query that is much more specific.
>
> Eduardo
>
>

Harry,

<< Ah.. I see. Ok. So I guess there are no rules then for knowing what is
faster for a certain statement (requestsensitive or not). It depends on how
many rows the table holds, the statement etc. >>

Actually, the main difference is the RequestSensitive property.  In some
cases it is very time-consuming for EDB to position itself on a single row
out of millions due to the way that the sets are represented internally in
EDB.  DBISAM had a solution for this, but it was buggy and problematic so it
was not carried over to EDB.  I hope to eventually get a solution in place
that is stable, but for now the rule is:

If you're selecting only a few rows out of millions or more, then you should
set RequestSensitive to False.

--
Tim Young
Elevate Software
www.elevatesoft.com

Harry,

<< So, with requestsensitive to TRUE I tested:
SELECT * FROM test
WHERE veld1 between 10001 and 10002 //2 records out of 1,000,000

and it's faster then
SELECT * FROM test
WHERE veld1 = 10001 (or veld1 between 10001 and 10001)  >>

Could you post the two query plans in question ?  I want to understand a)
what kind of time differences we're talking about and b) what exactly is
being executed.

--
Tim Young
Elevate Software
www.elevatesoft.com