Splitting Long running SQL query into multiple smaller queries

I am using SQL Server 2008 and Java 6 / Spring jdbc.

We have a table with records count ~60mn.

We need to load this entire table into memory, but firing select * on this table takes hours to complete.

So I am splitting the query as below

String query = "  select * from TABLE where "  ;
        for(int i =0;i<10;i++){
            StringBuilder builder = new StringBuilder(query).append(" (sk_table_id % 10) =").append(i);
            service.submit(new ParallelCacheBuilder(builder.toString(),namedParameters,jdbcTemplate));
        }

basically, I am splitting the query by adding a where condition on primary key column,

above code snippet splits the query into 10 queries running in parallel.this uses java's ExecutorCompletionService.

I am not a SQL expert, but I guess above queries will need to load same data in memory before applyinh modulo operator on primary column.

Is this good/ bad/ bes开发者_开发知识库t/worst way? Is there any other way, please post.

Thanks in advance!!!

If you do need all the 60M records in memory, select * from ... is the fastest approach. Yes, it's a full scan; there's no way around. It's disk-bound so multithreading won't help you any. Not having enough memory available (swapping) will kill performance instantly. Data structures that take significant time to expand will hamper performance, too.

Open the Task Manager and see how much CPU is spent; probably little; if not, profile your code or just comment out everything but the reading loop. Or maybe it's a bottleneck in the network between the SQL server and your machine.

Maybe SQL Server can offload data faster to an external dump file of known format using some internal pathways (e.g. Oracle can). I'd explore the possibility of dumping a table into a file and then parsing that file with C#; it could be faster e.g. because it won't interfere with other queries that the SQL server is serving at the same time.