0

I'm trying to insert some data into my Database.

But unfortunately I've a small problem with double date.

Because of my file representation which represents some football results and games.

e.g. Barcelona.txt

Spa;PRD;Espanyol;A;1;1;0;R;1396105200;14-03-29;Sat
Spa;UCL;Atletico Madrid;H;x;1;1;R;1396377900;14-04-01;Tue <--
Spa;PRD;Real Betis;H;1;3;1;R;1396713600;14-04-05;Sat

I will have some some rows with the same meaning

e.g. Atletico Madrid.txt will have a value 

Spa;UCL;Barcelona;A;x;1;1;R;1396377900;14-04-01;Tue

which is the same. Therefore I will have these 2 values into my database

"1","Spa","UCL","Barcelona","Athletico_Madrid","H","x","1","1","R","1396377900","14-04-01","Tue "
"6","Spa","UCL","Athletico_Madrid","Barcelona","A","x","1","1","R","1396377900","14-04-01","Tue "

With other words, if I change some values, team A is Team B and visa versa. H <=> A, 1<=>0, outscore <=> homescore then i've the same row.

Actually it is easy to avoid these double values, by checking if there is an existing transformed row in the database.

rs = stmt.executeQuery( "SELECT * FROM WEDSTRIJD w WHERE " +
    "w.DATE = '"+ date +"' and " +
    "w.TEAM = '"+ opponent +"' and " +
    "w.OPPONENT = '"+ team +"' and " +
    "w.SCORED = "+ param[6] +" and " +
    "w.AGAINST = "+ param[5] +" ;" );

But by doing this check,and if(rs.getRow() == 0) --> insert row . Everything has become enormously slow. Normally it will take 3 seconds to insert +100.000 rows but now via this check, it takes + 1h 25 minutes.

Thus does any one know a better solution? which isn't this horrible?

Dieter
  • 2,499
  • 1
  • 23
  • 41

1 Answers1

1

When the database needs to search rows based on their values, it has to go through all rows in the table to compare them.

Such searches can be sped up when there is an index on (at least some of) the searched columns. Indexes are the more useful the less results your query returns (because the database can avoid reading more rows), and the more often the query is executed.

For this particular query, you should create an index on the most selective lookup columns:

stmt.execute("CREATE INDEX whatever ON WEDSTRIJD(DATE, TEAM)");
Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for your advise, but it's a pity that there doesn't exist such a thing as insert into database (...) values (...) if (database.value != A and database.value2 != B and ... ) ....... But i've solved my problem via adding a matchId to the database and via a group by, i've my wanted result – Dieter Oct 02 '14 at 10:15