mORMot Open Source

array81

Member

From: Italy

Registered: 2010-07-23

Posts: 411

Best way to call a hard query

I have a database with 2 table with this structure:

TABLE1 (about 2000000 records)
id|name|number1|number2|...|...
1|aaa |12.23  |43.17  |...|...
2|bbb |54.145 |77.23  |...|...
3|ccc |65.2345|13.234 |...|...

TABLE2 (about 1500000 records)
id|name|number1|number2
1|ddd |100.121|12.32
2|bbb |76.223 |11.45
3|eee |55.23  |32.1

I need compare the rows of TABLE1 with the rows of TABLE2 in this way: if the "name" of TABLE1 is equal the "name" of TABLE2 I need replace "number1" and "namber2" value of  TABLE1 with "number1" and "namber2" value of  TABLE2.

I have found this query on stackoverflow: http://stackoverflow.com/questions/3845 … -user-name

I have try to use the Synopse editor to call it but after about (2 hours) the editor are not finish besides the application "not responding".

The tables have many rows so I think it's normal a long wait, however I like know:

1. Is there a best way to call this query with your framework?
2. Is there a way to see the progress of query? (to see if the application is blocked and remaining time)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Best way to call a hard query

1. Creating a new table is perhaps faster if a lot of rows are to be updated.

2. There is no way to see the progress in SynDBExplorer yet.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Best way to call a hard query

Something like this will work in seconds:
Select t1.name, case when t2.name is null then t1.value else t2.value, ... from t1 left outer join t2 on t1.name=t2.name into resultTable

create index on t2.name is a good idea.

P.S. not shure about sqlite SQL syntax

Last edited by mpv (2014-07-21 20:32:05)