Livecode and slow select with sqlite

[skindoc4]

Opened my project in 4.5 (running perfectly in 4.0) and noticed that select statements into sqlite suddenly take an inordinate amount of time - 10 seconds or more. Anyone else noticed this behaviour?

Alex

[skindoc4]

A few views but no comments - this problem is basically rendering Livecode useless for me so I'll post my code. I would be very grateful for assistance.

put "title,first_name,other_names,surname,dob,category,subcategory,organisation,ph_work,ph_mobile,email,pt_patient.id" into myFields

put "SELECT " & myFields & " FROM pt_patient,bs_person,bs_address" into theSQL
put " WHERE pt_patient.id_bs_person = bs_person.id" after theSQL
put " AND bs_address.id_bs_person = bs_person.id" after theSQL

#get the selection criteria
set the itemdelimiter to comma
put fUpSQuote (item 1 of theString & "%") into theSurname
put " AND surname LIKE " & theSurname after theSQL

put item 2 of theString into theFirstName
if theFirstName is not empty then
put fUpSQuote(theFirstName & "%") into theFirstName
put " AND first_name LIKE " & theFirstName after theSQL
end if

put " ORDER BY surname,first_name,other_names" after theSQL

theSQL is the query I send to sqlite via Livecode - works instantaneously in RunRev 4.0 - takes 10 seconds or more in Livecode - what's going on?

other queries seem to work OK in Livecode

[trevordevore]

I don't know what is going on but I saw your bug report in RQCC. One suggestion would be to include a sample stack and database that exhibits the problem so the dev team can reproduce the issue and see what is going on. The SQLite driver was updated between 4.0 and 4.5 and this could be a side effect of that.

[skindoc4]

Hi Trevor

Thanks for your reply. I thought that the driver must have been changed as nothing in my programming had. My stack continues to work flawlessly in 4.0 and take forever in 4.5. Thus far it only affects moderately complex queries which return multiple records since returning a single record on a simple query with an indexed id works with no discernible loss of speed. Because I send encrypted data over the internet using sockets, I use the revQueryDatabase function which returns the data rather than the function which returns a recordset - I altered my querying to return the recordset but it made no difference - the access to sqlite works in 4.5 but just takes 10 seconds or more. For me, the old driver worked perfectly so I would be happy if they just un-updated it:-) For the moment I must continue my development in 4.0.

alex

[skindoc4]

An update.

Mark at Rev fixed the issue for me in a jiffy. I hadn't indexed the search fields. Worked OK under the old driver but not the new. Indexed the search fields and presto! - Livecode was working seamlessly and as quickly as 4.0. Well done Mark - thanks

[andyh1234]

Im having similar issues, but for our database only half the data loads with a query in 4.5

Can I ask what you needed to do to index the search fields? Was it something that could be done from within livecode?

Thanks

Andy

[skindoc4]

Hi Andy

I use "SQLite Manager", a free add on for Firefox to manage single changes to the database and for inspecting the contents of tables. It is quick and direct for this.

It is quicker to write code in Livecode for changes to multiple tables. You can in fact do everything in Livecode but I just find SQLIte Manager better for some tasks.

Hope this helps.

Alex

[andyh1234]

Thanks Alex.