[thelist] MySQL stored function looping problem
Phil Turmel
pturmel-webdev at turmel.org
Wed Dec 10 06:43:52 CST 2008
[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
More information about the thelist
mailing list