naoyuki

Idea that improvement for ExecuteSQL

Discussion created by naoyuki on May 19, 2016
Latest reply on May 25, 2016 by beverly

I tried a below SQL for table has 1000000 records.

And the found records(by WHERE part) are only 100 records.

SELECT category, \"date\", SUM(amount)

FROM test_table

WHERE \"date\" >= '2016/05/01' AND \"date\" <= '2016/05/31'

GROUP BY category, \"date\"

ORDER BY \"date\"

Before I tried that, I have had thought that sorting works for only constrained records.

But I seemed ExecuteSQL method sorts whole records.

Because I saw progress dialog that counting down remained sort records from about 1000000 to 0.

 

So, using ExecuteSQL with ORDER BY or GROUP BY for tables have a large amount records is extremely slow. It's terrible.

 

I think that ExecuteSQL method should executes "ORDER BY" part or "GROUP BY" part for constrained records by "WHERE" part.

 

I modified the code, because it has some errors. Thank you for pointing out, beverly.

Outcomes