Alternative for multiple while loops to improve performance

[MissPerl]

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.

[LanX]

> 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.

[NetWallah]

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.

[learnedbyerror]

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

[afoken]

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