Thanks Roger for the quick reply. The query indeed is an inner query which
is supplied to a DELETE. Essentially, I am trying to DELETE ~1 million
records. This causes a DB hang or may be takes a lot of time. Following is
the query.
DELETE
FROM table1
WHERE ID NOT IN
(SELECT id
FROM ref_table
WHERE id2 IN
(SELECT distinct(id2)
FROM another_ref_table))
AND CREATEDDATE < current_timestamp()-1
I think deletion is taking lot of time, can we optimize this.
On Tuesday, April 15, 2014 11:24:47 PM UTC+5:30, Roger Thomas wrote:
>
> Well even with the best optimizer in the world this is likely to result in
> a complete table scan of table1, with each record then being checked
> against the results generated by the inner NOT IN select.
>
> When I do a simple test of having 2 tables in a clean database with just
> ID fields populated 1-1M and 1-100 this query takes 2.3s to run if I just
> request a count(*) back rather than the 999900 records that the original
> query will return. For me that 2.3s is basically disk I/O time, if you have
> records with more information a lot more information will be requested from
> the disk and so this will take longer.
>
> Try running the following at a h2 console after having restarted H2 to
> clear the cache and you will see the retrival time - if your cache is large
> enough the second time you run it should result in a near zero time taken.
>
> select count(*) from
> (
> select * from table1
> where
> ID
> NOT IN
> (
> select ID from table2
> )
> )
>
> The rest of the time will be the time spent dealing with the result set -
> can you move addtional processing to the database so ending up with a
> smaller result set?
>
> Roger
>
> On Tuesday, April 15, 2014 3:39:34 PM UTC+1, Devesh Mishra wrote:
>>
>> Hi,
>>
>> I am having performance problems while executing a query which has NOT IN
>> clause. Following is the query
>>
>> select * from table1
>> where
>> ID
>> NOT IN
>> (
>> select ID from table2
>> )
>>
>> Table1 contains 1 million records
>> table2 contains 100 records.
>>
>> expecting 1 million - 100 records as output. Both the columns are indexed.
>>
>> Please help, Thanks a lot in advance
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
