Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how much fetchSize(??) should i set?

 
naved momin
Ranch Hand
Posts: 692
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i am using ODBC-JDBC bridge for database connectivity with MSSQL 2005, sometimes my application greets me with a exception called connection is busy with the result of another hstm
i have done some research on that and i figured out that it is because of the fetch size , so if i set the fetchSize in Statement like will it solve my problem
also what should i specify in the fetchSize(???) so that i should not get any performance hit?
any suggestion are most welcome.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd say you should get rid of the ODBC-JDBC bridge first. Sun/Oracle had always suggested to use native drivers when available, and there certainly are native drivers for MS SQL. There were known issues with ODBC-JDBC last time I checked, and since virtually every database has a native driver, nobody is going to fix them.

So my suggestion is to switch to native drivers and see whether the issue persists.

Setting fetch size is a good idea anyway, I'd say a reasonable one-size-fits-all value could be around 100. Only if you read really wide tables (several dozens of columns), a lower value might be better. As always, you should test the new setting thoroughly.
 
naved momin
Ranch Hand
Posts: 692
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:I'd say you should get rid of the ODBC-JDBC bridge first. Sun/Oracle had always suggested to use native drivers when available, and there certainly are native drivers for MS SQL. There were known issues with ODBC-JDBC last time I checked, and since virtually every database has a native driver, nobody is going to fix them.

So my suggestion is to switch to native drivers and see whether the issue persists.

Setting fetch size is a good idea anyway, I'd say a reasonable one-size-fits-all value could be around 100. Only if you read really wide tables (several dozens of columns), a lower value might be better. As always, you should test the new setting thoroughly.

can you name some good native drivers that works with MSSQL 2005
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
naved momin wrote:can you name some good native drivers that works with MSSQL 2005

Did you already try this one? It atually comes up first if you google up ms sql 2005 jdbc driver.

I know some people prefer the jTDS driver to connect to MS SQL Server. I don't use MS SQL Server, so I can't advice which one to choose. Maybe someone will comment further on this.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Naved, I'm very sorry for the confusion I've caused. By "native driver" I actually did mean the "type 4 driver". I noticed the confusion when you asked the question about native drivers.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic