Hi

I'm using java servlets with jdbc to query MS sqlserver.

Some of the queries can be large i.e. querying a table of 1 billion
rows and resultsets of 1 million rows. The queries
are all read only looping through the resultset up to a maximum
of 5 million rows. Sometimes the query is performed using
joined tables (select * from a,b where a.ib=b.id). A full
table scan on our hardware takes 20-30 minutes.

I'm using the sqlserver supplied driver and more recently trying the
jtds one.

So what are the best settings I should use for my connection
and statement objects.

Specifics:
i) When doing a statment.execute() does this only
return once the query has finished or are results passed
to the client on the fly.

ii) selectMethod - this option only seems to apply to the
sqlserver supplied driver. Default is "direct" but the docs
say this caches the enire result set to memory, so for large
queries use "cursor". However using direct I don't seem
to witness more memory being used up than when using cursor.
Speed performance seems about the same if the setFetchSize is not
set too small.

iii) Is there a way of roughly knowing waht setFetchSize to use.

iv) I want to use the statement.setQueryTimeout to halt long running
queries and put them into a queue. The queryTimeout only
seems to have an effect if the statement.execute has not
yet returned in the time set but this goes back to i). If the execute
returns has the query completely finished and are all the results
stored somewhere or is the query still running.  Logic tells me the
former but I've run a query that takes several minutes but results
start appearing after 30 secs or so.

Thanks
 Mike