Subject | Re: optimization question (select) |
---|---|

Author | svanderclock |

Post date | 2009-12-06T08:43:40Z |

it's not really a problem of fetch time because i alway put 'First 10 skip 0' so i fetch only 10 records max

Query time: 0ms

Execute: 483 ms

Avg fetch time: 48 ms

the difference in speed between an index on fieldA and fieldB is :

SELECT

FIRST 1 *

FROM

TableX x

WHERE

x.FieldA > 1 and x.FieldA < 10000000 and

x.FieldB > 5 and x.FieldB < 6

PLAN (Tablex INDEX (FieldA))

execution time: 7.44 Seconds

SELECT

FIRST 1 *

FROM

TableX x

WHERE

x.FieldA > 1 and x.FieldA < 10000000 and

x.FieldB > 5 and x.FieldB < 6

PLAN (Tablex INDEX (FieldA, FiledB))

execution time: 500 ms

SELECT

FIRST 1 *

FROM

TableX x

WHERE

x.FieldA > 1 and x.FieldA < 10000000 and

x.FieldB > 5 and x.FieldB < 6

PLAN (Tablex INDEX (FieldB))

execution time: 0 ms (of course, because the restriction is on field B in this sample)

for the test we use firebird 2.1.2 but i don't thing it's will change in 2.1.3 ?

thanks

stephane

Query time: 0ms

Execute: 483 ms

Avg fetch time: 48 ms

the difference in speed between an index on fieldA and fieldB is :

SELECT

FIRST 1 *

FROM

TableX x

WHERE

x.FieldA > 1 and x.FieldA < 10000000 and

x.FieldB > 5 and x.FieldB < 6

PLAN (Tablex INDEX (FieldA))

execution time: 7.44 Seconds

SELECT

FIRST 1 *

FROM

TableX x

WHERE

x.FieldA > 1 and x.FieldA < 10000000 and

x.FieldB > 5 and x.FieldB < 6

PLAN (Tablex INDEX (FieldA, FiledB))

execution time: 500 ms

SELECT

FIRST 1 *

FROM

TableX x

WHERE

x.FieldA > 1 and x.FieldA < 10000000 and

x.FieldB > 5 and x.FieldB < 6

PLAN (Tablex INDEX (FieldB))

execution time: 0 ms (of course, because the restriction is on field B in this sample)

for the test we use firebird 2.1.2 but i don't thing it's will change in 2.1.3 ?

thanks

stephane

--- In firebird-support@yahoogroups.com, "Arno Brinkman" <fbsupport@...> wrote:

>

> Hi,

>

> > unfortunatly put an index on fieldA and another on fieldB not help ! same speed (or i miss

> > something, i probably write badly the query plan ?)

>

> Could (or may be you already do) also test the difference between the time for the "index builds"

> and "fetch time".

>

> What is the difference in speed between (with an index on FieldA and an index on FieldB)

>

> SELECT

> FIRST 1 *

> FROM

> TableX x

> WHERE

> x.FieldA > 1 and x.FieldA < 10000000 and

> x.FieldB > 5 and x.FieldB < 6

>

> and

>

> SELECT

> *

> FROM

> TableX x

> WHERE

> x.FieldA > 1 and x.FieldA < 10000000 and

> x.FieldB > 5 and x.FieldB < 6

>

> Do you always need to fetch all rows?

>

> Which Firebird version are you using?

>

> Regards,

> Arno Brinkman

> ABVisie

> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

>

> General database development support:

> http://www.databasedevelopmentforum.com

>

> Firebird open source database (based on IB-OE) with many SQL-99 features :

> http://www.firebirdsql.org

> http://www.firebirdsql.info

> http://www.fingerbird.de/

> http://www.comunidade-firebird.org/

>

> Support list for Firebird and Interbase users :

> firebird-support@yahoogroups.com

>

> Nederlandse firebird nieuwsgroep :

> news://newsgroups.firebirdsql.info

>