sqlite3 performance problems only in python

[Nick Craig-Wood]

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