sqlite3 performance problems only in python
nick at craig-wood.com
Thu Jul 23 22:29:57 CEST 2009
David Stanek <dstanek at dstanek.com> wrote:
> On Thu, Jul 23, 2009 at 9:02 AM, Stef Mientki<stef.mientki at gmail.com> wrote:
> > btw, I don't know if it's of any importance, the SQL-statement I perform is
> > select OPNAMEN.*, NAME, NAME_, SCORES.SCORE, PATIENT.*
> > from OPNAMEN
> > inner join POID_VLID on OPNAMEN.POID = POID_VLID.POID
> > inner join VRAAGLST on VRAAGLST.VLID = POID_VLID.VLID
> > inner join VLID_SSID on VRAAGLST.VLID = VLID_SSID.VLID
> > inner join SUBSCHAAL_GEGEVENS on SUBSCHAAL_GEGEVENS.SSID = VLID_SSID.SSID
> > inner join POID_SSID_SCID on ( OPNAMEN.POID =
> > POID_SSID_SCID.POID ) and
> > ( SUBSCHAAL_GEGEVENS.SSID =
> > POID_SSID_SCID.SSID )
> > inner join SCORES on SCORES.SCID =
> > POID_SSID_SCID.SCID
> > inner join PID_POID on OPNAMEN.POID = PID_POID.POID
> > inner join PATIENT on PATIENT.PID = PID_POID.PID
> > where substr ( lower( NAME) , 1, 6) = 'cis20r'
> > and lower ( NAME_ ) = 'fatigue'
> > and TEST_COUNT in (3,4)
> > and DATETIME > 39814.0
> > and SCORE < 30
> Warning: I suck at SQL and hate it with a passion...
> By using lower() on the left side of the where expressions I believe
> that you are table scanning. So it is not the size of the data
> returned, but the size of the data that needs to be scanned.
In all the databases I've used, the like operator has been case
insensitive, so if that is the problem you could use
NAME like '%cis20r%' -- not quite the same, but close!
and NAME_ like 'fatigue'
instead which might be quicker. Or not ;-)
Nick Craig-Wood <nick at craig-wood.com> -- http://www.craig-wood.com/nick
More information about the Python-list