[thelist] MySQL stored function looping problem

[Phil Turmel]

[Sorry for the slow response... no signal / no internet all day yesterday]

Jeremy Weiss wrote:
> Phil Turmel wrote:
> 
>> I think you've made this a little more complicated than necessary. 
> 
> I do have a tendency of doing that. :(
> 

Heh.  "This is a cool hammer!  That sure looks like a nail!"

>> This is very much a scripting task as opposed to a 
>> SQL task (not that it can't be done in SQL...)
> 
> I'm having a difficult time distinguishing where that line is.
> How you tell the difference between the two?
> 

The line varies with circumstances and languages involved, but in the
SQL vs. script case I usually ask myself the following questions:

Do I need a stored proc to enforce critical business rules?
Do I need to hold a lock for the duration of the procedure? (I *really*
don't want a lock waiting for the network)
Can I structure a stored proc for straight through evaluation? (I hate
loops in SQL)
Which language has the better tools for the task at hand? (i.e. PHP's
'explode' function)

>> Yeah, there'll be a bunch of single-row inserts issued from PHP, but for
>> a one-time conversion script, I doubt you'll suffer too much.  You did
>> say there's only ~ 15K listings.  Figure 1ms per insert to estimate the
>> runtime.  (If I'm way off on that, I'd like to know what hardware you're
>> running on...)
> 
> I don't know if it changes anything, but this would be ran once daily, 
> right after I pull the feed from the Realtor Assoc. It averages ~15K 
> properties but that translates into ~450K inserts into the 
> carmls_features table.
> 
>  I've never timed INSERTS on my server before, but you got me 
> curious. So, I gave it a go using your code (which worked great,
> btw) and here's what I saw:
> 
> 453866 records inserted
> total time 98.8076000008 seconds
> average time per insert = 0.000217702141162 seconds
> 

Nice.  Is this with or without an existing key on mls,fid?

> That's a lot faster than I thought it would be, but wouldn't it be
> faster if it wasn't going back and forth between PHP and MySQL?
> 

If PHP is on the same server as MySQL, I've never seen any significant
difference.  If different boxes, even on a gigabit lan, going back and
forth will certainly slow you down.

However, MySQL does support multiple values per insert statement, so the
round-trips could be reduced with modest additional script complexity.

> 
> -jeremy
> 
> 

HTH,

Phil