Use PreparedStatement rather than Statement.Īlways try to minimize number of sql statements you send to database. Here you can find some benchmarks and patterns for using fetch size: JdbcTemplate jdbc = new JdbcTemplate(datasource) This reduces number of physical hits to database socket and speeds your process. When you load a lot of records from database it is very, very important to set proper fetch size on your jdbc connection. However you can mark transaction as Read-only to help your database. If your queries only read - there is no problem. On each new inserted record you can have something like RUN_ID and if everything went well you can mark this RUN_ID as successful. Use approach 1) and commit transaction for each batch. This is too much for most database configurations. If you have to do some updates or inserts than 4 mln records is too much to handle in on transactions. calling str.intern() for each string fetched from DB.storing Date objects as integer (yymmdd).storing 3 integers (with limited range) in 1 long variable (using util for bit shifting).This is why you can use that method only if you hava such resources.Īnother topic is how to write MyData and MyDetail classes to be as small as possible. , rs.getString(4) ) Īfter this you have map filled with all data collected. MyData record = new MyData( rs.getInt(1), rs.getString(2). final TIntObjectMap map = new TIntObjectHashMap(10000, 0.8f) Which is much better that java standard library maps. To do that you need efficient map implementation, i.e. 100 MB - 1 GB) - but is much faster that approach 1). Such approach may need much heap memory (i.e. I used this approach many times for night processes (with no normal users). If your records are no so big you can store them all at once event for 4 mln table. Using efficient maps to store content from many selects. Stat1.executeQuery(sql) // query for all IDs in bufferĢ. Automatically started transactions are committed when the last SQL statement finishes. Any command that accesses the database (basically, any SQL command, except a few PRAGMA statements) will automatically start a transaction if one is not already in effect. where X and id in (" + getIDs(buffer) + ")" No reads or writes occur except within a transaction. MyData record = new MyData( rs.getString(1). This significantly reduces number of SQL statements to execute. You can read your big query and store results in some kind of buffer.Īnd only when buffer is full you should run subquery for all data collected in buffer. To improve JDBC performance for your scenario you can apply some modifications.Īs you will see, all these modifications can significantly speed your task. PS : I am unable to post the query here, but I am assured that all queries are optimized. I am using about 200 threads which process the above code and stores the records in linked blocking queue.ĭoes increasing the thread count blindly helps increase the performance or is there some other way in which I can increase the performance of the result sets? But I may have to run the code daily, so I want to process all the records in 20 hours. My multi threaded application now can process 90,000 in one hour. Each small query has 3 inner join statements. The BIGQUERY returns around 4.5 million records and for each record, I have to execute the smaller queries, which are 14 in number. ResultSet rs2 = stat1.executeQuery(SMALLQ2) ResultSet rs1 = stat1.executeQuery(SMALLQ1) ResultSet rs = stat.executeQuery(BIGQUERY) Hints.IgnoreIndex( "idx_user_name").I am executing the following set of statements in my java application. Hints.ForceIndex( "idx_user_name", "idx_user_id").ForOrderBy(), SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)" GORM performs write (create/update/delete) operations inside a transaction to ensure data consistency, which is bad for performance, you can disable it during initialization db, err := gorm.Open(sqlite.Open( "gorm.db"), &gorm.Config) GORM optimizes many things to improve the performance, the default performance should be good for most applications, but there are still some tips for how to improve it for your application.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |