Styjun

STM Microcontroller burns every time

Why is the Turkish president's surname spelt in Russian as Эрдоган, with г?

The impact of an intelligent and (mostly) hostile flying race on weapons and armor

First-year PhD giving a talk among well-established researchers in the field

How dangerous are set-size assumptions?

Are neural networks the wrong tool to solve this 2D platformer/shooter game? Is there a proven way to frame this problem to a neural network?

What determines the "strength of impact" of a falling object on the ground, momentum or energy?

Singing along to guitar chords (harmony)

Why is C++ initial allocation so much larger than C's?

Architecture of networked game engine

How to append a matrix element by element?

In the Marvel universe, can a human have a baby with any non-human?

Should I include salary information on my CV?

How come I was asked by a CBP officer why I was in the US?

Was touching your nose a greeting in second millenium Mesopotamia?

How to positively portray high and mighty characters?

Is adding a new player (or players) a DM decision, or a group decision?

Cascading Repair Costs following Blown Head Gasket on a 2004 Subaru Outback

Do French speakers not use the subjunctive informally?

Short story with brother-sister conjoined twins as protagonists?

How to perform Login Authentication at the client-side?

Is it OK to bottle condition using previously contaminated bottles?

Alphabet completion rate

Content builder HTTPS

Java mssql-jdbc: retrieving data slow from table with xml column

Fetching millions of records in javaHow to pretty print XML from Java?Retrieve column names from java.sql.ResultSetHow can I get column names from a table in SQL Server?How to remove a column from an existing table?Date columns in SQL-Server (MSSQL-JDBC 3.0) running under Java 1.7.0 retrieved as 2 days in the pastSQL query return data from multiple tablesjdbc export data into xmlcannot connect to mssql server from java jdbcJava JDBC cannot connect to MSSQL ServerRetrieving Data from JDBC preparedstatement

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a moderately big table on Sql Server, about 40 million rows.
In particular this table has an xml column which could be large in size.

I am experiencing very poor performance on retrieving data from this table using last version of mssql-jdbc.
I am looking for ways to improve performance both on java side and database side.

On java side this is the relevant code for fetching data:

String connectionUrl = "jdbc:sqlserver://10.10.10.28:1433;databaseName=MYDB;user=MYUSER;password=MYPWD;selectMethod=direct;sendStringParametersAsUnicode=false;responseBuffering=adaptive;";

String query = "SELECT * FROM MYTABLE WHERE DateTimeField > '2019-03-25 00:00:00.0' AND DateTimeField < '2019-03-25 13:00:00.0'";

try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
 Statement stmt = sourceConnection.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY) ) 

 stmt.setFetchSize(100); 
 resultSet = stmt.executeQuery(query);

 while (resultSet.next()) 

 // do something

 

I've set the fetch size on 100 rows because I've noticed problems if I set the fetch size too big, probably because of the xml column which is heavy to send on the network.

Does anyone have any suggestion to improve performance for fetching data in this scenario?

java sql sql-server xml jdbc

share|improve this question

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  On DB side use indexes, it will help to boost the performance at the server end. On Java side, you could use pagination to limit the data on page size and fetch limited data. have a look at this: stackoverflow.com/questions/22998281/…
  
  – Ali Azim
  Mar 25 at 10:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi @AliAzim, I am using fetchSize to specify the number of rows retrieved each time. Is it enough or are there other parameters I could use?
  
  – revy
  Mar 25 at 11:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, it's but you should do some research on it. BTW you could use caches to optimize your performance and instead of querying the DB table you could use a stored procedure.
  
  – Ali Azim
  Mar 25 at 11:11
  

  
  
  
  

add a comment | 

0

I have a moderately big table on Sql Server, about 40 million rows.
In particular this table has an xml column which could be large in size.

I am experiencing very poor performance on retrieving data from this table using last version of mssql-jdbc.
I am looking for ways to improve performance both on java side and database side.

On java side this is the relevant code for fetching data:

String connectionUrl = "jdbc:sqlserver://10.10.10.28:1433;databaseName=MYDB;user=MYUSER;password=MYPWD;selectMethod=direct;sendStringParametersAsUnicode=false;responseBuffering=adaptive;";

String query = "SELECT * FROM MYTABLE WHERE DateTimeField > '2019-03-25 00:00:00.0' AND DateTimeField < '2019-03-25 13:00:00.0'";

try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
 Statement stmt = sourceConnection.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY) ) 

 stmt.setFetchSize(100); 
 resultSet = stmt.executeQuery(query);

 while (resultSet.next()) 

 // do something

 

I've set the fetch size on 100 rows because I've noticed problems if I set the fetch size too big, probably because of the xml column which is heavy to send on the network.

Does anyone have any suggestion to improve performance for fetching data in this scenario?

java sql sql-server xml jdbc

share|improve this question

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  On DB side use indexes, it will help to boost the performance at the server end. On Java side, you could use pagination to limit the data on page size and fetch limited data. have a look at this: stackoverflow.com/questions/22998281/…
  
  – Ali Azim
  Mar 25 at 10:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi @AliAzim, I am using fetchSize to specify the number of rows retrieved each time. Is it enough or are there other parameters I could use?
  
  – revy
  Mar 25 at 11:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, it's but you should do some research on it. BTW you could use caches to optimize your performance and instead of querying the DB table you could use a stored procedure.
  
  – Ali Azim
  Mar 25 at 11:11
  

  
  
  
  

add a comment | 

I have a moderately big table on Sql Server, about 40 million rows.
In particular this table has an xml column which could be large in size.

I am experiencing very poor performance on retrieving data from this table using last version of mssql-jdbc.
I am looking for ways to improve performance both on java side and database side.

On java side this is the relevant code for fetching data:

String connectionUrl = "jdbc:sqlserver://10.10.10.28:1433;databaseName=MYDB;user=MYUSER;password=MYPWD;selectMethod=direct;sendStringParametersAsUnicode=false;responseBuffering=adaptive;";

String query = "SELECT * FROM MYTABLE WHERE DateTimeField > '2019-03-25 00:00:00.0' AND DateTimeField < '2019-03-25 13:00:00.0'";

try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
 Statement stmt = sourceConnection.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY) ) 

 stmt.setFetchSize(100); 
 resultSet = stmt.executeQuery(query);

 while (resultSet.next()) 

 // do something

 

I've set the fetch size on 100 rows because I've noticed problems if I set the fetch size too big, probably because of the xml column which is heavy to send on the network.

Does anyone have any suggestion to improve performance for fetching data in this scenario?

java sql sql-server xml jdbc

share|improve this question

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

String connectionUrl = "jdbc:sqlserver://10.10.10.28:1433;databaseName=MYDB;user=MYUSER;password=MYPWD;selectMethod=direct;sendStringParametersAsUnicode=false;responseBuffering=adaptive;";

String query = "SELECT * FROM MYTABLE WHERE DateTimeField > '2019-03-25 00:00:00.0' AND DateTimeField < '2019-03-25 13:00:00.0'";

try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
 Statement stmt = sourceConnection.createStatement(SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY) ) 

 stmt.setFetchSize(100); 
 resultSet = stmt.executeQuery(query);

 while (resultSet.next()) 

 // do something

 

java sql sql-server xml jdbc

java sql sql-server xml jdbc

share|improve this question

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

share|improve this question

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

asked Mar 25 at 10:56

revyrevy

6981010 silver badges1919 bronze badges

1 Answer
1

active

oldest

votes

0

Since you have a filter that is quite narrow, the performance issue is most likely due to lack of index vs size of column.

Do a simple

Select count(*) from ......

And see performance. Most likely that will be slow too and will thus eliminate the issue of XML column.

share|improve this answer

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It took about 20 minutes for the select count to complete. So are you suggesting to create an index for the DateTimeField column?
  
  – revy
  Mar 26 at 9:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, by creating index and if selectivity is good it would be fine. Remember number of rows does not affect performance it is access path
  
  – Saad Ahmad
  Mar 26 at 18:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a general blog entry around misconception that #rows by itself affects performance. [link]saadwmsblog.blogspot.com/2014/05/…
  
  – Saad Ahmad
  Mar 26 at 19:52
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55336224%2fjava-mssql-jdbc-retrieving-data-slow-from-table-with-xml-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Since you have a filter that is quite narrow, the performance issue is most likely due to lack of index vs size of column.

Do a simple

Select count(*) from ......

And see performance. Most likely that will be slow too and will thus eliminate the issue of XML column.

share|improve this answer

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It took about 20 minutes for the select count to complete. So are you suggesting to create an index for the DateTimeField column?
  
  – revy
  Mar 26 at 9:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, by creating index and if selectivity is good it would be fine. Remember number of rows does not affect performance it is access path
  
  – Saad Ahmad
  Mar 26 at 18:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a general blog entry around misconception that #rows by itself affects performance. [link]saadwmsblog.blogspot.com/2014/05/…
  
  – Saad Ahmad
  Mar 26 at 19:52
  

  
  
  
  

add a comment | 

0

Since you have a filter that is quite narrow, the performance issue is most likely due to lack of index vs size of column.

Do a simple

Select count(*) from ......

And see performance. Most likely that will be slow too and will thus eliminate the issue of XML column.

share|improve this answer

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It took about 20 minutes for the select count to complete. So are you suggesting to create an index for the DateTimeField column?
  
  – revy
  Mar 26 at 9:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, by creating index and if selectivity is good it would be fine. Remember number of rows does not affect performance it is access path
  
  – Saad Ahmad
  Mar 26 at 18:29
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is a general blog entry around misconception that #rows by itself affects performance. [link]saadwmsblog.blogspot.com/2014/05/…
  
  – Saad Ahmad
  Mar 26 at 19:52
  

  
  
  
  

add a comment | 

Since you have a filter that is quite narrow, the performance issue is most likely due to lack of index vs size of column.

Do a simple

Select count(*) from ......

And see performance. Most likely that will be slow too and will thus eliminate the issue of XML column.

share|improve this answer

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges

Select count(*) from ......

share|improve this answer

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges

answered Mar 25 at 11:40

Saad AhmadSaad Ahmad

36011 silver badge66 bronze badges