INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query in loop becomes really slow

Query in loop becomes really slow

(OP)
I was having timeout troubles with a certain PHP batch script, and narrowed down the problem to one MySQL statement that is executed in a loop with different values for comparison (it's checking for similar records to data already acquired in another database). Here is one example of the query:

CODE --> SQL

SELECT p.PersonID, p.FullName, p.HouseholdID, h.LabelName,
IF(LOWER(REPLACE(p.FullName,' ',''))!='第一宣教バプテスト教会'
  AND LOWER(REPLACE(REPLACE(p.Furigana,' ',''),',',''))!='daiichisenkyoubaptistchurch',1,0)
  AS hhonly
FROM kizuna_crash_test.person p
LEFT JOIN kizuna_crash_test.household h ON p.HouseholdID=h.HouseholdID
WHERE LOWER(REPLACE(p.FullName,' ',''))='第一宣教バプテスト教会'
  OR LOWER(REPLACE(REPLACE(p.Furigana,' ',''),',',''))='daiichisenkyoubaptistchurch'
  OR LOWER(REPLACE(REPLACE(h.LabelName,' ',''),',','')) LIKE '%第一宣教バプテスト教会御中%'
  OR (h.PostalCode='277-0014' AND h.Address='3-8-34')
  OR REPLACE(h.Phone,'-','')='0471649159' 
(Except for the ID fields, the fields in the query are all VARCHAR of varying lengths, and there are about 10,000 records in each table. There are indexes, but due to the functions they are not being used.)

The execution times might start out less that a millisecond each, but at some point in the loop (sometimes the very first time, sometimes a few or a few dozen iterations later) they will suddenly get slow (typical times 200-300 milliseconds) and stay that way. I can only assume some sort of memory issue, but is there something bad I'm doing in the construction of the query to cause it to use massive memory? At the moment this is a one-off to process about 800 times, so I could break it up into smaller chunks to stay under the time limit, but I might use the same code in a more permanent and dynamic situation in the future, so I should figure out what is going on.

RE: Query in loop becomes really slow

I see that you want the data to be in some format, but allow other formats to be used and therefore use the functions. It would probably make a large difference if you could just apply the format one for every record of the table. If you do not want to loose the current entries, you could do that in extra fields, but you could just store, for example, telephone numbers as numbers only.

You could also create a temporary table, store all the comparison strings in them, and then build just one query based on a join with the temporary table. Queries in loops are not exactly clean programming practise.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close