Tim

In DBISAM I set my query generator to produce a set of ORs rather than an IN. At some point I think I had been told it was faster. I'm working on TMSing and EDBing my query generator so I'd like a pointer in the right direction. Which is best ORs or an IN? I would just do some tests but Windows caching etc makes it difficult and long winded.

Roy Lambert

Roy

A simple test on a table with 1,000.000 rows

//veld1 is integer (indexed)
select * from test
where veld1 in (200,2000)  //executes in 2.375 seconds
select * from test
where veld1 = 200 or veld1 = 2000 //executes in 0 seconds (EDBmgr)

//veld2=varchar (indexed)
select * from test
where veld2 in ('_200','_2000') //executes in 3.015 seconds
select * from test
where veld2 = '_200' or veld2 = '_2000' //executes in 0.031 seconds

I ran the query several times.

So it seems that OR is faster, indeed. Also, with more items do query on,
the advantadge of using OR is increasing it seems (am I correct Tim?)

Regards, Harry



"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:B433BFCA-EBF4-485D-9E62-499955F794B2@news.elevatesoft.com...
> Tim
>
> In DBISAM I set my query generator to produce a set of ORs rather than an
IN. At some point I think I had been told it was faster. I'm working on
TMSing and EDBing my query generator so I'd like a pointer in the right
direction. Which is best ORs or an IN? I would just do some tests but
Windows caching etc makes it difficult and long winded.
>
> Roy Lambert

Harry


That's how it was with DBISAM but I'm always suspicious of benchmarks without boot after boot after boot.

Either ElevateDB is lightning or you have one fast machine


Roy Lambert

Roy

It was tested as a local session (so no c/s). on a P4 3.Ghz with 1 Gig Ram.
EDB is also ligtning

Harry





"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:F02FE00A-73BC-4017-BCDC-3ADE46045914@news.elevatesoft.com...
> Harry
>
>
> That's how it was with DBISAM but I'm always suspicious of benchmarks
without boot after boot after boot.
>
> Either ElevateDB is lightning or you have one fast machine
>
>
> Roy Lambert

Harry de Boer wrote:
> Roy
>
> It was tested as a local session (so no c/s). on a P4 3.Ghz with 1 Gig Ram.
> EDB is also ligtning
>
> Harry
>

Harry,
    If you don't mind, can you reboot between tests? So do the OR tests
first, then reboot, and do the IN() tests. That would eliminate the
cache. Of course if the OR's are still faster, it could just mean the
IN() clause isn't optimized.

Dave

Dave,

caching is all araound. OS (that is Windows), hard drive with multi MB (8 MB
on my two drives system). So is nearly impossible to test after reboot
because we would spend 95% of our time rebooting.

Speed testing has to be done WARM with some appropriate number of customer
computers/processes running.

Regards,
Bruno

Harry,

<< A simple test on a table with 1,000.000 rows

//veld1 is integer (indexed)
select * from test
where veld1 in (200,2000)  //executes in 2.375 seconds
select * from test
where veld1 = 200 or veld1 = 2000 //executes in 0 seconds (EDBmgr)

//veld2=varchar (indexed)
select * from test
where veld2 in ('_200','_2000') //executes in 3.015 seconds
select * from test
where veld2 = '_200' or veld2 = '_2000' //executes in 0.031 seconds >>

Could you post the query execution plans for those queries ?  Something is
amiss - IN should be as-fast or faster than OR.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

The requested query plans. It seems that OR is using an index scan while IN
is using a row scan.

Regards, Harry


select * from test
where veld1 in (200,2000)
============================================================================
====
SQL Query (Executed by ElevateDB 1.02 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

============================================================================
====

SELECT ALL
"test"."veld1" AS "veld1",
"test"."veld2" AS "veld2",
"test"."veld3" AS "veld3"
FROM "test"
WHERE "veld1" IN (200, 2000)

Source Tables
-------------

test: 1000002 rows

Result Set
----------

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the test table:

"veld1" IN (200, 2000) [Row scan: 1000002 rows, 56000112 bytes estimated
cost]

============================================================================
====
2 row(s) returned in 2,516 secs
============================================================================
====

select * from test
where veld1 = 200 or veld1 = 2000
============================================================================
====
SQL Query (Executed by ElevateDB 1.02 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

============================================================================
====

SELECT ALL
"test"."veld1" AS "veld1",
"test"."veld2" AS "veld2",
"test"."veld3" AS "veld3"
FROM "test"
WHERE "veld1" = 200 OR "veld1" = 2000

Source Tables
-------------

test: 1000002 rows

Result Set
----------

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the test table:

"veld1" = 200 OR "veld1" = 2000 [Index scan: 1 keys, 4096 bytes estimated
cost]

============================================================================
====
2 row(s) returned in 0 secs
============================================================================
====

select * from test
where veld2 in ('_200','_2000')
============================================================================
====
SQL Query (Executed by ElevateDB 1.02 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

============================================================================
====

SELECT ALL
"test"."veld1" AS "veld1",
"test"."veld2" AS "veld2",
"test"."veld3" AS "veld3"
FROM "test"
WHERE "veld2" IN ('_200', '_2000')

Source Tables
-------------

test: 1000002 rows

Result Set
----------

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the test table:

"veld2" IN ('_200', '_2000') [Row scan: 1000002 rows, 56000112 bytes
estimated
cost]

============================================================================
====
2 row(s) returned in 3,25 secs
============================================================================
====

select * from test
where veld2 = '_200' or veld2 = '_2000'
============================================================================
====
SQL Query (Executed by ElevateDB 1.02 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

============================================================================
====

SELECT ALL
"test"."veld1" AS "veld1",
"test"."veld2" AS "veld2",
"test"."veld3" AS "veld3"
FROM "test"
WHERE "veld2" = '_200' OR "veld2" = '_2000'

Source Tables
-------------

test: 1000002 rows

Result Set
----------

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the test table:

"veld2" = '_200' OR "veld2" = '_2000' [Index scan: 1 keys, 4096 bytes
estimated
cost]

============================================================================
====
2 row(s) returned in 0,016 secs
============================================================================
====




"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:0818DB86-7AC8-40C9-BB1D-DFC9A1B8DCF3@news.elevatesoft.com...
> Harry,
>
> << A simple test on a table with 1,000.000 rows
>
>  //veld1 is integer (indexed)
>  select * from test
>  where veld1 in (200,2000)  //executes in 2.375 seconds
>  select * from test
>  where veld1 = 200 or veld1 = 2000 //executes in 0 seconds (EDBmgr)
>
>  //veld2=varchar (indexed)
>  select * from test
>  where veld2 in ('_200','_2000') //executes in 3.015 seconds
>  select * from test
>  where veld2 = '_200' or veld2 = '_2000' //executes in 0.031 seconds >>
>
> Could you post the query execution plans for those queries ?  Something is
> amiss - IN should be as-fast or faster than OR.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry de Boer wrote:
> Tim,
>
> The requested query plans. It seems that OR is using an index scan while IN
> is using a row scan.
>
> Regards, Harry

Harry,
    If In() is doing a Row scan, the it is not optimized. I know MySQL
will optimize In() so it is quite fast. I'm sure Tim will get around to
it sooner or later.

Dave.

Harry,

<< The requested query plans. It seems that OR is using an index scan while
IN is using a row scan. >>

It's a bug - the optimization process is reversing the logic and preventing
index usage.  A fix will be in 1.03 by the end of the week.

--
Tim Young
Elevate Software
www.elevatesoft.com