Running query in command window is slow

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Running query in command window is slow

Frank Millman
Hi all

I am running Python 3.6.0 and Sqlite3 3.8.6 on Windows 10.

I have a fairly complex query. If I execute it using Python, it takes 1 second. If I copy and paste it into the Sqlite3 command window, it takes 10 minutes.

I am not too bothered – it is the Python one that is important. Still, it seems odd. Can anyone suggest a reason for this?

Thanks

Frank Millman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Running query in command window is slow

Clemens Ladisch
Frank Millman wrote:
> I have a fairly complex query. If I execute it using Python, it takes
> 1 second. If I copy and paste it into the Sqlite3 command window, it
> takes 10 minutes.

Probably different SQLite versions.

What is the EXPLAIN QUERY PLAN output in both cases?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Running query in command window is slow

Frank Millman
Clemens Ladisch wrote:

>
> > Frank Millman wrote:
> > I have a fairly complex query. If I execute it using Python, it takes
> > 1 second. If I copy and paste it into the Sqlite3 command window, it
> > takes 10 minutes.
>
> Probably different SQLite versions.
>
> What is the EXPLAIN QUERY PLAN output in both cases?
>


Thanks, Clemens. You just beat me to it.

I was about to reply that I had forgotten to check that, but now that I have, I see that Python is using Sqlite3 3.14.2.

I won’t show the query plan unless you really want to see it. The query involves a JOIN to a VIEW, which then has to retrieve data from the underlying physical tables. Obviously some work has been done to optimise this in more recent versions.

Frank
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Running query in command window is slow

Richard Hipp-3
On 9/22/17, Frank Millman <[hidden email]> wrote:
> I see that Python is using Sqlite3 3.14.2.
>

What version of the command-line shell are you using?  It would be a
problem if the command-line shell is a later version and yet is
running slower.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Running query in command window is slow

Frank Millman
Richard Hipp wrote:
On 9/22/17, Frank Millman <[hidden email]> wrote:
> > I see that Python is using Sqlite3 3.14.2.
>
> What version of the command-line shell are you using?  It would be a problem if the command-line shell is a later version and yet is running slower.
I was running 3.8.6, so it was actually quite old.

I have now upgraded both of them to 3.20.1

Sorry for the confusion.

Frank
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users