CODEWITHSUNDEEP
mysqlperformance
user1513073
user1513073

Reputation: 21

Two identical databases on same server with different running times

I have two databases that are identical except that in one I have about 500.000 entries (distributed over several tables) while the other database is empty.

If I run my program in the empty database then execution takes around 10mins while in the database with the 500k entries execution takes around 40mins. I now deleted some of the entries (about 250k entries) and it speeded up the execution by around 10mins. The strange thing is that these tables where not heavily queried (just some very simple inserts), so I wonder how this can have such an effect on the execution.

Also, all SQL statements that I do (I run a lot of them) are rahter simple (no complicated joins mainly inserts), so I wonder why some tables with 250k entries can have such an effect on the performance. Any ideas what could be the reason?

Upvotes: 0

Views: 688

Answers (1)

Nachiket Kate
Nachiket Kate

Reputation: 8571

Following things could be the reason but for actual reasons you should look and profile your queries,

  1. Though you think you are making simple inserts, its not a simple operation from DB perspective. (for every entry you insert following things may change and update

    1. Index
    2. Constraints
    3. Integrity of DB (PK-FK) and there are many things to consider.above things look simple but they take time if volume is high

  2. Check volume of queries (if high no. of insert queries are getting executed then as might be knowing Insert is exclusive operation i.e. it locks the table for updating and volume is high that means more locking time and waiting time.) to avoid this probably you can try chaining or bulk operations Is bulk update faster than single update in db2?

  3. Data Distribution also plays important role. if you are accessing heavily loaded tables then parsing/accessing/fetching data from such tables will also take time (it doesn't matter for single query but it really hurts for large volume of similar queries). Try to minimize that by tuning your queries.

Upvotes: 1

Related Questions