5

I have an INSERT INTO... SELECT ... FROM SQL statement that runs acceptably fast when executed from SQLite's command line shell.

However, if I execute the same statement (copy/pasted) with Perl's DBI::SQLite, the statement becomes slow.

The reason must be the execution plan: when I let the statement be explained from the shell and from within DBI::SQLite, they're different: the fast version uses the optimal indexes and table order, the slow version chooses to access the tables in a less than optimal way.

So, I have two questions.

  1. Why is the plan different?
  2. How can I make the plan used in the Perl environment the same as in the shell?
Mittal Patel
  • 2,732
  • 14
  • 23
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • Have you looked at the output of statistics_info ? Does it show the indexes? – bytepusher Sep 10 '17 at 22:46
  • Do you use transactions in `DBI::SQLite` case (and commit infrequently)? Any difference in settings when you connect from the script? (Are REGEXP involved by any chance?) – zdim Sep 11 '17 at 04:59
  • 2
    What is the output of `SELECT sqlite_version();` in both cases? – CL. Sep 11 '17 at 08:12

0 Answers0