Extremely slow query on My SQL active table using $

[Jay Talbott]

I have a table in a desktop application with 82,000 records with people's demographic data (name, address, etc.)
I am converting it to a My SQL application.
I have made an active-link table and have a form based on that table.
There is an embedded browse on the table with all records, and a search capability using Last Name, First Name and Email address.
The Last Name and First Name searches search on the first part of the names, and that is very fast.
However, the email search uses the $ to look anywhere in the email address to find the seach string. This search is painfully slow.
Short of creating an Xdialog box, and hard coding the SQL select statement, how can I speed up the email search?

Here is the code on the search button.

t = table.current()
query.order = "lastname+firstname"
query.description = "find by name"
query.options = "T"


if vc_search_by = "Last Name" then
query.filter = "left(lastname,len('"+vc_search_for+"')) = '"+vc_search_for+"'"
end if

if vc_search_by = "First Name" then
query.filter = "left(firstname,len('"+vc_search_for+"')) = '"+vc_search_for+"'"
end if

if vc_search_by = "Email Address" then
query.filter = "'"+vc_search_for+"' $ email"
end if

t.query_create()
parentform.Resynch()

Thanks in advance.
Jay

[Jay Talbott]

Re: Extremely slow query on My SQL active table using $

Any suggestions (otherwise known as a shameless bump)?
Thanks.
Jay

[Steve Workings]

Re: Extremely slow query on My SQL active table using $

Jay - Would the query be any faster if you run it directly against the SQL source?

If it is, maybe do it that way first, return the record key(s) you need, and then use the desktop tools to locate the appropriate records for display.

[Jay Talbott]

Re: Extremely slow query on My SQL active table using $

That's a great idea. I will try it an post the solution.
Thanks.
Jay

[Jay Talbott]

Re: Extremely slow query on My SQL active table using $

Well, I took your advice and it works like gangbusters.
I created a duplicate dbf table (to be used as a temporary table), zap it when the form opens, and use the script below to copy the retrieved records from the SQL table to the temp table. What was taking over two minutes using the active table above takes less than three seconds with the script below.
Thanks again.
Jay

'Date Created: 21-Jul-2010 01:00:24 PM
'Last Updated: 21-Jul-2010 01:10:47 PM
'Created By : Owner
'Updated By : Owner
t = table.current()
query.order = "lastname+firstname"
query.description = "find by name"
query.options = "T"
'email LIKE '%"+response+"%';"

if vc_search_by = "Last Name" then
query.filter = "lastname like '%"+vc_search_for+"%';"
end if

if vc_search_by = "First Name" then
query.filter = "firstname like '%"+vc_search_for+"%';"
end if

if vc_search_by = "Email Address" then
query.filter = "email like '%"+vc_search_for+"%';"
end if
'Execute a query on a SQL database
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::cnet")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .t.
'Define the ResultSet object
dim rs as sql::resultset

'Define a DataSource object

delete ds
dim ds as p
ds.connectionstring = "::Name::cnet"
vc_sql = "SELECT * FROM people WHERE "+ query.filter
ds.SQLSelectstatement = vc_sql

delete resP
dim resP as p
hourglass_cursor(.t.)

'specify that when the ResultSet is created, A5 should not perform a second query
' to get the count of the number of records in the ResultSet
dim flagGetRecordCount as l = .f.

'Call a high level function that creates a ResultSet from a DataSource defintion.
'This function will automatically prompt for missing argument values if necessary.
resP = a5_SQL_RS_From_DataSourceDef(flagGetRecordCount,cn,ds,rs)
if resP.Error = .t. then
ui_msg_box("Error",resp.errorText)
cn.close()
end
end if

t = table.open("sql_people_temp")
t.zap(.t.)
rs.ToOpenTable(t, "", -1, -1, .T., .f.)
t.close()

'Now, close the connection
cn.close()

parentform.Resynch()