Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes how much fetchSize(??) should i set? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how much fetchSize(??) should i set?" Watch "how much fetchSize(??) should i set?" New topic
Author

how much fetchSize(??) should i set?

naved momin
Ranch Hand

Joined: Jul 03, 2011
Posts: 692

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.


The Only way to learn is ...........do!
Visit my blog http://inaved-momin.blogspot.com/
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Jul 03, 2011
Posts: 692

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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how much fetchSize(??) should i set?