We have a
csv file with 2000 rows and a database
table1 about 2 million rows and another
table2 with 60000 rows.
We need to query from
table1 based on parameters from the csv file, so each row in
csv should execute one select query. Initially we tried to execute the following query within a
for loop in the application:
SELECT table1.c1, table1.c2, ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]), point(table1.c3, table1.c4))*0.2 AS length, table1.c5 FROM table1 WHERE table1.c1 IN ( SELECT DISTINCT table2.c1 FROM table2 LEFT JOIN table2.c1=table1.c1 WHERE table2.c2=1 AND table2.c3 BETWEEN 1000 AND 2000 ) HAVING length < csv.c4[i] AND table1.c5 BETWEEN date("start_date") AND date("end_date") ORDER BY table.c1
i is actually the loop index. Because of the 2000 round trips to MySQL server, it takes a long time to complete this. It takes about 16 hours just to get through the queries.
So, I wrote the SP below to avoid looping and simply call this SP so can do the looping in MySQL server:
CREATE PROCEDURE sp() BEGIN DECLARE arg0 VARCHAR(255); DECLARE arg1 FLOAT; DECLARE arg2 FLOAT; DECLARE arg3 FLOAT; DECLARE cur1 CURSOR FOR SELECT * FROM csv_based_table; OPEN cur1; read_loop: LOOP FETCH cur1 INTO arg0, arg1, arg2, arg3; SELECT col0, col1, ST_Distance_Sphere(point(arg1, arg2),point(col3,col4))*2 AS length, arg0 FROM important_table1 WHERE col0 IN ( SELECT DISTINCT c0 FROM important_table2 LEFT JOIN c0 ON col0 = c0 WHERE c1=1 and c2 BETWEEN 1000 AND 2000) HAVING distance < arg3 AND col5 BETWEEN date("start_date") AND date("end_date") ORDER BY arg0, col0; END LOOP; CLOSE cur1; END;
So, instead of using the csv file, we create a database table for that csv file and run the above stored procedure. Problem is this turned out to be slower than the original 2000 iterations loop. In a test over 100 csv rows, for loop-based original solution finished in 317.6 seconds while the stored procedure took 321.5 seconds just for the SP itself. Why is this happening and how can I optimize this?
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!