Home How to run thousands mysql select commands through java simultaneously?
Reply: 1

How to run thousands mysql select commands through java simultaneously?

saranya elumalai
1#
saranya elumalai Published in 2018-01-12 23:04:03Z

I need to run 1000s of mysql select queries and save the results in different files (1 file per query) everyday. Currently every mysql select query is running as a background command through shell script as follows

echo "select (CART_ID), '', (USER_ID) from shopping_cart where (
    DATE_MODIFIED >= '2018-01-08 00:00:00' 
    and DATE_MODIFIED < '2018-01-10 00:00:00'
)" | mysql -u <usr_name> -p<password> -h <host> -P 3306 \
    -D <schema< -C -N --default-character-set=utf8 > \ 
      /Users/selumalai/Desktop/520_250_493991738_shopping_cart_\
      2018_01_12_00_00_00.dat &

Currently, there are around 5 shell script files; each containing ~250 similar queries writing into 250 different files. So, there will be ~1000 files everyday. Requirement is not to join the queries to produce unified results.

Is there a better way to do in Java? Googling says its expensive to open mysql connection for each query (thread) and java program to run mysql command shell script is not working as expected.

Any advice?

O. Jones
2#
O. Jones Reply to 2018-01-12 23:37:00Z

Generally, it's better to write fewer SQL commands that return more rows of information in the result set, then use a program that reads that data.

You might try this kind of query:

select DATE(DATE_MODIFIED), CART_ID, USER_ID 
 from shopping_cart
where DATE_MODIFIED >= CURDATE() - INTERVAL 60 DAY
  and DATE_MODIFIED < CURDATE()
  ORDER BY DATE(DATE_MODIFIED)

This will get you a result set with 60 days' data in it, with the first column giving the date. Then your Java program can work through the result set and pull out each day's information.

The idea of running thousands of queries from a shell script will, as you know, work. But it is inherently slow. And, it is difficult to parallelize. You're much better off running one query, especially if the thousands of queries can be reduced to one.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.369296 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO