Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Alternative for multiple while loops to improve performance

by MissPerl (Sexton)
on Dec 18, 2018 at 15:13 UTC ( #1227403=perlquestion: print w/replies, xml ) Need Help??

MissPerl has asked for the wisdom of the Perl Monks concerning the following question:

I wrote this script, but it's not performance-wise, so i try to improve it,

I am lack of knowledge to know how can I possible improve this

So was hoping fellow perl monks can point me into the direction so I can get a start from there.

this script is used to find the first decreased price, will get 1.67 in this case to insert to db.

3.88 1.67 1.53 1.52 + - -
i got multiple tables in database, fruit_load , fruit_pricing country zipcode city name mgroup subgroup fruit special_fruit fruit_price special_fruit_price To get the pricing for each fruit in each group in each city, zipcodeand country, I do multiple while loops,
SELECT DISTINCT (country, zipcode, city) FROM fruit_load WHERE fruit_l +oad.sold_date BETWEEN NOW() - INTERVAL 60 DAY AND NOW(); SELECT DISTINCT (owner) FROM fruit_pricing b JOIN fruit_load a ON a.fr +uit_load_id = b.fruit_load_id WHERE a.country= '$data[0]' AND a.zipco +de = '$data[1]' AND a.city = '$data[2]'; SELECT DISTINCT (mgroup) FROM fruit_pricing b JOIN fruit_load a ON a.f +ruit_load_id = b.fruit_load_id WHERE a.country = '$data[0]' AND a.zip +code = '$data[1]' AND a.city = '$data[2]' AND b.owner ='$data2[0]'; SELECT DISTINCT (fruit) FROM fruit_pricing b JOIN fruit_load a ON a. f +ruit_load_id = b. fruit_load_id WHERE a.country = '$data[0]' AND a.zi +pcode = '$data[1]' AND a.city = '$data[2]' AND b.owner = '$data2[0]' +AND b.mgroup = '$data3[0]'; SELECT * FROM fruit_load a JOIN fruit_pricing b ON a.fruit_load_id = b.fruit_load_id WHERE b.sold_date BETWEEN NOW() - INTERVAL 180 DAY AND NOW() AND a.country = '$data[0]' AND a.zipcode = '$data[1]' AND a.city = '$data[2]' AND b.owner = '$data2[0]' AND b.mgroup = '$data3[0]' AND b.fruit = '$data4[0]' ORDER BY sold_date DESC; put found row into array in this loop SELECT DISTINCT (subgroup) FROM special_fruit_price c JOIN fruit_load +a ON a.fruit_load_id = c.fruit_load_id WHERE a.country = '$data[0]' A +ND a.zipcode = '$data[1]' AND a.city = '$data[2]' AND c.owner = '$dat +a2[0]'; SELECT DISTINCT (special_fruit) FROM special_fruit_price c JOIN fruit_load a ON a.fruit_load_id = c.fruit_load_id WHERE a.country = '$data[0]' AND a.zipcode = '$data[1]' AND a.city = ' +$data[2]' AND c.owner = '$data2[0]' AND c.mgroup = '$data3[0]' AND c.subgroup = '$data6[0]'; SELECT * FROM fruit_load a JOIN special_fruit_price c ON a.fruit_load_id = c.fruit_load_id WHERE c.sold_date BETWEEN NOW() - INTERVAL 180 DAY AND NOW() AND a.country = '$data[0]' AND a.zipcode = '$data[1]' AND a.city = '$data[2]' AND c.owner = '$data2[0]' AND c.mgroup = '$data3[0]' AND c.subgroup = '$data6[0]' AND c.special_fruit = '$data7[0]' ORDER BY sold_date DESC; put found row into array in this loop
Let meknow if I can provide more information, I can give the whole code, but I try to makeit short and sweet.

The code currently run 15 mins to 2 hours varies, is it possible to shorten the time ?

I believe the multiple insert will slow down the process, at current I am trying to put them into array and insert at once.

Replies are listed 'Best First'.
Re: Alternative for multiple while loops to improve performance
by NetWallah (Canon) on Dec 18, 2018 at 18:44 UTC
    SQL GROUP BY and HAVING clauses can help summarize and filter data significantly faster than running multiple queries in a while loop.

    Also - LanX's suggestion of having appropriate INDEXes should improve performance.

    SQL help would be off-topic for this forum, so I'm resisting requesting schema information, and a better problem description.

                    As a computer, I find your faith in technology amusing.

Re: Alternative for multiple while loops to improve performance
by LanX (Archbishop) on Dec 18, 2018 at 15:35 UTC
    > Let meknow if I can provide more information

    Um ... where is the Perl code?

    On a side note: Please consider using placeholders to avoid huge security risks.

    edit

    Sorry, I didn't really bother to decipher your SQL, but often performance issues are connected to missing indices on relevant fields.

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery FootballPerl is like chess, only without the dice

Re: Alternative for multiple while loops to improve performance
by learnedbyerror (Monk) on Dec 19, 2018 at 12:43 UTC

    I agree with the previous commenters regarding making sure that you have an optimized database. Correct indices are critical

    Since you haven't provided any perl code, we can't give you very much advice. What I can offer, is that I often read data from a database and load it into a perl HashRef that is shaped in a way to allow me to access the data very directly for my purpose. This assumes that the data can fit within memory. See perlref and perlreftut for information on references if you are not familiar with them.

    Cheers, lbe

      I often read data from a database and load it into a perl HashRef that is shaped in a way to allow me to access the data very directly for my purpose. This assumes that the data can fit within memory.

      Depending on your data, this can be a huge waste of resources. As a general rule of thumb, try not to fetch more data than you really need from the database, and let the database sort, filter, and aggregate the data as much as possible.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1227403]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (5)
As of 2019-07-19 20:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?