CODEWITHSUNDEEP
javamysqlsql
cardician
cardician

Reputation: 2491

Possible to batch sql queries?

I'm a novice MySQL user so could really use a little advice here. I've inherited a program that currently processes text files consisting of approximately 100,000 records each. Each line is it's own record and gets processed. A file like this can be completed in about a minute as nothing to intense is being done, mostly just string comparisons.

Anyway, now some SQL queries have to be added to the process without slowing things down too much. Essentially, it's possible that each of those 100,000 lines will now need to make a SQL select statement to query a DB and see if more data is accessible. I threw together a rough start and it takes the processing time from a minute to about an hour. Definitely unacceptable. My question is, is there even way to speed this up? (I'm sure there is)

I'm only opening one connection object but I don't see any way around making that individual SQL query for each record. Is there a way to batch select statements? Any advice would be very helpful, thank you.

UPDATE

Sorry, I should have been more explicit. This is for SQL select statements. I know how to do batch Inserts, that's no problem. I don't understand if batch select statements are possible or how to speed them up.

Also, someone mentioned Indexes, but I don't know anything about indexes. Could someone provide me some info on them?

Upvotes: 2

Views: 23383

Answers (3)

ZnArK
ZnArK

Reputation: 1541

If you're doing a select statement, you could use the in keyword.

http://www.w3schools.com/sql/sql_in.asp

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Upvotes: 0

Randy
Randy

Reputation: 16677

this might be a good use for PRE INSERT TRIGGER code. - in other words, let the database do the work when presented with an incoming row.

Upvotes: 0

Graham Borland
Graham Borland

Reputation: 60681

Speed up SELECT by creating an index for the columns which commonly appear in the WHERE clause.

Speed up batch inserts by wrapping them all in a transaction.

Upvotes: 4

Related Questions