Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
python-sqlite
Home
+ new post
About this group
Join this group
Message from discussion Speeding up SELECT queries?
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Durand  
View profile  
 More options Jun 3 2010, 3:29 pm
From: Durand <dura...@gmail.com>
Date: Thu, 03 Jun 2010 20:29:19 +0100
Local: Thurs, Jun 3 2010 3:29 pm
Subject: Re: [python-sqlite] Speeding up SELECT queries?
Print | View thread | Show original | Report this message | Find messages by this author
Hi,

Thanks for the reply. I see what you mean about the comparison. I just  
realised that I had actually improved speed on the xml one by using an  
index. I didn't realise that the same thing was possible with sql. I'm  
reading up on indexes now. Thanks again!

Durand

On Wed, 02 Jun 2010 03:05:02 +0100, Roger Binns <rog...@rogerbinns.com>
wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1

> On 06/01/2010 12:59 PM, Durand wrote:
>> Each execute statement takes about 20ms whereas getting the same data
>> from the xml file takes about 0.3ms. I thought sqlite was much faster
>> than this?

> I don't see you measuring the same thing from the code you supplied.  The
> XML is doing a single iteration over every 'reading'.  The SQLite is  
> doing
> unindexed searches over the whole dataset three times.

> In the SQLite case you are looking for a particular value (literal = ?)
> which means SQLite has to check every row for a match.  If you create an
> index then it can merely consult the index.

> To see what SQLite is doing, put "EXPLAIN QUERY PLAN" in front of your  
> query
> - - for example "EXPLAIN QUERY PLAN SELECT reading FROM readings where  
> ..."
> and you'll see what indices will be used (if any).

> That will give you the biggest performance improvement.  Following on  
> from
> that SQLite defaults to a maximum memory cache of 2MB.  Your XML code
> doesn't show whether you are using the incremental parser or if the XML  
> was
> already all parsed and present in memory.

> Roger

> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

> iEYEARECAAYFAkwFvEoACgkQmOOfHg372QTr0QCg5DC1S0ZyAHdGWGs9VqMaF6fZ
> Xc8AoJipxGjMdCZqWZTKPE8LSQKo4Cnr
> =JyG5
> -----END PGP SIGNATURE-----


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google