List:General Discussion« Previous MessageNext Message »
From:hsv Date:November 23 2012 1:25pm
Subject:Re: Stored Procedure Question?
View as plain text  
>>>> 2012/11/23 10:49 +0530, Girish Talluru >>>>
I have a scenario where I have to screen a huge bunch of records for in db
using certain rules. I have done in traditional php style record by record
and it took 90 mins for 4000 records. I have 800k - 900k records in
production which might possibly lead to days of execution.

I have figured out that the php script does wait for the record to execute
and then only after it it will process the next record. For this if it is
java I should have used stored procedure and multithreading concept to run
multiple threads in parallel.

But I don't think PHP supports multithreading. Now I have idea to create a
stored procedure to do all the checks and my question here is when I call a
stored procedure does the control get backs immediately to the php script?
Bcoz I want to pick other record immediately while the first one going
through the process and call the procedure again. 
Sounds to me that if your data are in a character form like a CSV file, or you can put
them into such a form, you can use LOAD DATA to insert into the database. Then you would
use a separate procedure, outside SQL, beforehand to screen the data, and maybe turn them
into MySQL s CSV-ish form.

If you are using a PHP procedure, I suspect that you can do that. Furthermore, since your
screener does not wait for MySQL, but only for PHP s own input-output, there is not that
wait. Once LOAD DATA begins, it very swiftly runs, and your (other?) PHP procedure waits
for _all_ the records to be inserted, not each one by one.

Of course, you could batch them, too, instead of making one CSV file of 900,000 records.

Stored Procedure Question?Girish Talluru23 Nov
  • Re: Stored Procedure Question?hsv23 Nov
  • Re: Stored Procedure Question?Peter Brawley23 Nov