1

I need to read a large result set from SQL Server into a java program. I need to read a consistent data state, so its running under a single transaction. I don't want dirty reads.

I can split the read using offset and fetch next and having each set of rows processed by a separate thread.

However, when doing this, it seems that the overall performance is ~30k rows read / sec, which is pretty lame. I'd like to get ~1m / sec.

I've checked that I have no memory pressure using visual VM. There are no GC pauses. Looking at machine utilisation it seems that there is no CPU limitation either.

I believe that the upstream source (SQL Server) is the limiting factor.

Any ideas on what I should look at on the database side? I have full access to the database server.

EDIT: Some more info based on comments:

Version tag updated. select @@version returns

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64) 
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Connection string is:

jdbc:sqlserver://myservername:1433;databasename=myDataBaseName

Driver is

mssql-jdbc-7.0.0.jre8.jar

Columns are 1 int, and 1 varchar(100), so not wide.

Running

SELECT 
    text, last_wait_type, cpu_time, reads, wait_time, wait_type, blocking_session_id 
FROM
    sys.dm_exec_requests 
CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle);

yields

text                                                                                                                                                                                                                                                             last_wait_type                                               cpu_time    reads                wait_time   wait_type                                                    blocking_session_id
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------- -------------------- ----------- ------------------------------------------------------------ -------------------
SELECT 
text,last_wait_type,cpu_time,reads,wait_time,wait_type, blocking_session_id 
from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle);                                                                                                   MISCELLANEOUS                                                0           0                    0           NULL                                                         0
select companyId, companyName from ciqCompany where companyname not like '%|%' and companyname not like '%"%' order by companyid asc offset 6000000 rows  fetch next 1000000 rows only                                                                           SOS_SCHEDULER_YIELD                                          3847        0                    0           NULL                                                         0

(2 row(s) affected)

and sometimes

text                                                                                                                                                                                                                                                             last_wait_type                                               cpu_time    reads                wait_time   wait_type                                                    blocking_session_id
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------- -------------------- ----------- ------------------------------------------------------------ -------------------
SELECT 
text,last_wait_type,cpu_time,reads,wait_time,wait_type, blocking_session_id 
from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle);                                                                                                   MISCELLANEOUS                                                0           0                    0           NULL                                                         0
select companyId, companyName from ciqCompany where companyname not like '%|%' and companyname not like '%"%' order by companyid asc offset 8000000 rows  fetch next 1000000 rows only                                                                           ASYNC_NETWORK_IO                                             11747       0                    13          ASYNC_NETWORK_IO                                             0

(2 row(s) affected)

So it seems that sometimes it is waiting for the client, and sometimes it is doing something else. What does SOS_SCHEDULER_YIELD refer to?

Additionally, I only see one entry in the results of that query, when I have 12 concurrent threads trying to select data (from the same table). Perhaps there is some locking going on here?

Looking at the hardware utilisation of the server and the client, neither seem particularly busy.

Certainly the like clauses are not good for speed. The order by / offset and fetch next is used to shard the table across the threads.

Query looks like this

select 
    companyId, companyName 
from 
    ciqCompany 
where 
    companyname not like '%|%' 
    and companyname not like '%"%' 
order by 
    companyid asc 
    offset 8000000 rows  fetch next 1000000 rows only

There are 12 concurrent requests like this with different offset values.

Query runs in 18s in management studio on the server, and returns 1m rows. Running the query in management studio on my dev machine (not in the same data centre) takes 32s. Running using jdbc it takes up to 283 sec.

Using multiple separate connections does lead to an increase in performance, however, it is important that the selects are reading from a consistent data state.

  • 1
    Please post your code or a sample of it, and update the tags with your version of SQL Server. – George.Palacios Dec 20 '18 at 14:45
  • Could you run this while the query is running? SELECT text,last_wait_type,cpu_time,reads,wait_time,wait_type, blocking_session_id from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) As to try and see what the query is waiting on. For example if it is Async_network_IO it is probably the app not processing fast enough, if it is pageiolatch_sh then you are going to disk to get the results, ... . – Randi Vertongen Dec 20 '18 at 14:45
  • The rows/second rate is meaningless without the actual size of the rows. It makes a difference if you read a row with two integers or one with 50 varchar(500) columns. The JDBC driver version and the connection string used, also influences the speed of the data retrieval – a_horse_with_no_name Dec 20 '18 at 14:53
  • Could you post the estimated query plan, or the actual if possible? where companyname not like '%|%' and companyname not like '%"%' order by companyid asc --> The Like operators are not going to be fun for your indexing. It does not look like you are getting blocked – Randi Vertongen Dec 20 '18 at 15:48
  • Did you measure your network throughput? – mustaccio Dec 20 '18 at 16:22
0

It seems that there is some sort of mutex going on in the jdbc side.

I came up with a workaround. I have a single connection and transaction that copies the data that I want into a set of staging tables in the source database. This transaction then commits. After that, a series of threads using their own connections (and therefore not a single transaction) read the data from the staging tables in parallel.

In other words, I can take a consistent snapshot of the data and then process that in parallel.

| improve this answer | |
  • 1
    JDBC is not intended to use a single connection in multiple threads, and therefore also not a single transaction. So unless you can make it single threaded your snapshot copy approach seems fine. – eckes Dec 28 '18 at 8:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.