Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Slowness in MS SQL Server JDBC driver #2432

Open
paragpandit123 opened this issue May 24, 2024 · 55 comments
Open

Slowness in MS SQL Server JDBC driver #2432

paragpandit123 opened this issue May 24, 2024 · 55 comments
Labels
Performance-related The desired fix involves increasing the performance of a process.

Comments

@paragpandit123
Copy link

JDBC VERSION: mssql-jdbc-12.6.1.jre11.jar
On Prem SQL Server Version : Query is getting data from databases on SQL 2017 and SQL 2019
How big is the query? I mean how many records it fetches and tables it query from SQL: In the sample we’ve used the return set is 71 million rows
image
@paragpandit123
Copy link
Author

paragpandit123 commented May 24, 2024 via email

@Jeffery-Wasty
Copy link
Contributor

Hi,

Yes, sorry, I deleted my response as I realized this was a follow-up to a previous email issue that was sent to the team. Thank you for the additional information, we'll look into this and get back to you with our response.

@paragpandit123
Copy link
Author

paragpandit123 commented May 24, 2024 via email

@Jeffery-Wasty
Copy link
Contributor

I read the email thread again. You are using the same connection string options and the same machines and servers in both cases, correct? You mention you are already using prepareMethod=prepare, there were 2 more questions:

  • What are the encryption settings you are using (it would be best if you could share the connection string)? As mentioned in the email, even with identical settings JDBC and jTDS can have different performance based on how they handle encryption.
  • Is Unicode data being used? If not, then try setting sendStringParametersAsUnicode=false

Also please enable logging (https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16) and allow logging to run for a set amount of time while waiting on the query. The timestamps will allow us to find out exactly what part of the driver is causing the long times.

@paragpandit123
Copy link
Author

paragpandit123 commented May 24, 2024 via email

@Jeffery-Wasty
Copy link
Contributor

I asked for additional connection settings because I was not sure whether the image was comprehensive. For instance, you have mentioned in the email you are already using prepareMethod=prepare, but I do not see this as part of the connection options.

Prior to any meeting, I would like to see where the slowness is happening in the driver. To do that, I will need to see the logs. Please capture the logs as described in the link above and either attach it to this issue or to the email thread. If a solution is not clear from the logs, then we can have a meeting to discuss this issue further.

@paragpandit123
Copy link
Author

paragpandit123 commented May 24, 2024 via email

@Jeffery-Wasty Jeffery-Wasty self-assigned this May 24, 2024
@paragpandit123
Copy link
Author

This log should be enabled on SQL server right ?

@paragpandit123
Copy link
Author

I guess we need to meet to understand the log request better.

@David-Engel
Copy link
Collaborator

There is client-side logging for the JDBC driver. The JDBC driver uses standard Java logging that can be configured through your application or via a config in your Java home. See the link previously provided for details: https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16

@paragpandit123
Copy link
Author

We are using Snaplogic tool to connect to SQL server. Not sure what can be done there.

@David-Engel
Copy link
Collaborator

Contact Snaplogic support for assistance with logging in their application.

@paragpandit123
Copy link
Author

We are working with snaplogic and will get back

@AlBundy33
Copy link

I'm not related to the project but here are my two cents...

We used to complete our data load of around 200 tables under 2 hrs with other driver and when we moved to SQL JDBC drivers , the load is not completing even after 7 hrs.

what means "other driver" - older version of mssql-jdbc, other vendor (e.g. jTDS), ...?
have you tried different commection-settings (e.g. disabled encryption, datbase-user instead of domain-user, ...)?
have you tried to create a standalone-testcase that repeoduces the problem?
You can also try tools like JProfiler to find hotspots.

you also wrote that this is an production issue - does this mean that this issue does not exist in your development and integration-systems? 🤔

@paragpandit123
Copy link
Author

what means "other driver" - older version of mssql-jdbc, other vendor (e.g. jTDS), ...? JTDS
have you tried different commection-settings (e.g. disabled encryption, datbase-user instead of domain-user, ...)? Yes
have you tried to create a standalone-testcase that repeoduces the problem? Yes

This issue is there in all environment , the reason I mentioned production is just to get the urgency.

@AlBundy33
Copy link

Maybe you want to share your testcase and your measured times.
I think this would help the devs.

@paragpandit123
Copy link
Author

Thats exactly , we wanted to meet , but we were told to wait till we get the logs. Thats what we are trying to get it with snaplogic environment we have.

@Jeffery-Wasty
Copy link
Contributor

Hi @paragpandit123,

I asked for the logs so that from the logs, we can see exactly what part of the driver is causing the slowness. To enable logging, you should follow the instructions in the provided link (https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16).

You commented you are using SnapLogic to connect to SQLServer. I have not used SnapLogic so I'm not sure how it interacts with our logging, so you either need to either (a) work with SnapLogic to produce JDBC logs using the above link, or (b) provide us with SnapLogic logging the shows us the same information. Once we have a clear idea of which part of the driver is causing the "slowness" we can work on resolving it.

@paragpandit123
Copy link
Author

We are already working on getting those logs .

@AlBundy33
Copy link

Thats exactly , we wanted to meet , but we were told to wait till we get the logs. Thats what we are trying to get it with snaplogic environment we have.

sorry, that was probably a misunderstanding - with testcase I ment a unit-test or at least a code-snippet that everyone can run to analyze the performance difference.

@lilgreenbird lilgreenbird added the Question Used when a question is asked, as opposed to an issue being raised label Jun 4, 2024
@Jeffery-Wasty
Copy link
Contributor

Hi @paragpandit123,

Are you able to provide an update on the requested logs?

@paragpandit123
Copy link
Author

paragpandit123 commented Jun 12, 2024 via email

@paragpandit123
Copy link
Author

The logfile is huge , how do you wants us to send it ?

@Jeffery-Wasty
Copy link
Contributor

You can email us the logfile - [email protected]

@Jeffery-Wasty
Copy link
Contributor

Thank you for the logs, but we were not able to get any information from them. We're currently working with a repro that reproduces the issue described above. There have been some issues with running the repro and we're currently waiting on a reply from the author.

@AlBundy33
Copy link

I'm just curious but what does the executed query look like?

join or condition sequence may influence performace.
I experienced this at least with jtds.

also don't forget that with many million rows it's possible that sequential scans are used instead of index scans.
maybe you can check the query plan.

what do you do with snaplogic?
is it just a tool to display your data like smms, dbeaver, ... or is it something to migrate data from one database to another or something else?

@paragpandit123
Copy link
Author

The select statement is select <column_list> from table , no join or anything else.
Snaplogic is used to transfer data.
The same query executes very very fast using JTDS driver .
e.g Total load use to complete in 1:30 mins and after changing to MS SQL jdbc driver , loads does not complete it even after 6/7 hours.

I have stated the same thing over and over to multiple people. Not sure how many times I have to explain that

@AlBundy33
Copy link

AlBundy33 commented Jul 3, 2024 •
edited
Loading

As mentioned before I'm not part of the project and only try to help based an on the informations in this issue... 🤔

some ideas:

  • check stacktrace for deadlocks
  • check activity monitor in management studio
  • check cpu and memeory usage on the machine runnig snaplogic (maybe excessive garbage collection)
  • if your also transfer data during your test try to do a read only test
  • run your select with only one column (e.g. primary key - btw. how many columns do you select and what types do they have?)
  • play with fetchSize (not sure what it does but it's set to 100 according to your screenshot) -> I'm not sure if jtds set a fetchsize
  • compare mssql-jdbc defaults with jtds defaults (https://jtds.sourceforge.net/faq.html)
  • btw. if I remeber correctly both(?) drivers have a threshold to decide whether a column is loaded into memory or saved to disk (not sure if only blobs and clobs affected)
  • don't forget that jtds is over 10 years old and therefore may only support a subset of the features that mssql-jdbc supports

that said, you can also try older versions of mssql-jdbc with "lesser" features.
for example mssql-jdbc handles timestamps different than jtds.
see #1843
you can also try different database compatibility levels.

@paragpandit123
Copy link
Author

What this status means? What are you waiting for from me ?

@Jeffery-Wasty
Copy link
Contributor

I've changed the status. As mentioned above, we're waiting on a repro from another party that should demonstrate this "slowness" issue, as we're unable to replicate the issue ourselves.

@paragpandit123
Copy link
Author

i can show you the issue if we can get on call

@Jeffery-Wasty
Copy link
Contributor

Jeffery-Wasty commented Aug 6, 2024 •
edited
Loading

Hi @paragpandit123,

After testing out the sample code, and talking with another user, I believe this issue may be due to the "flexible callable statements" feature introduced in 12.5.0.

If possible, I would like you to test out two scenarios:

without.flex.calls.zip

  • Attached is a copy of the 12.5.0 driver without the above feature: this should produce performance more closely in line with jTDS
  • Please try the same 12.6.1 driver you were using above, but this time set useFlexibleCallableStatements to false

This is what I expect to happen:

  • The first result will resolve the issue, pointing to the feature as the culprit
  • The second result will keep the performance issue, even though the feature should be disabled, indicating there is an issue with disabling the feature, and this is what we need to fix.

Let us know if you're able to test the above, and what the results are.

@Jeffery-Wasty Jeffery-Wasty added the Performance-related The desired fix involves increasing the performance of a process. label Aug 6, 2024
@paragpandit123
Copy link
Author

Thanks for the response, I will check both the options.

@paragpandit123
Copy link
Author

I tried both the options

  1. 12.5.0
  2. 12.6.1 with useFlexibleCallableStatements to false

I did not see any improvement in the performance.

@Jeffery-Wasty
Copy link
Contributor

I see, thanks.

@github-project-automation github-project-automation bot moved this to Under Investigation in MSSQL JDBC Aug 27, 2024
@Jeffery-Wasty Jeffery-Wasty added Backlog The topic in question has been recognized and added to development backlog and removed Question Used when a question is asked, as opposed to an issue being raised labels Sep 11, 2024
@Jeffery-Wasty
Copy link
Contributor

An update - we've identified what is causing the slowness, but do not have a fix ready at the moment. The work has been put into our backlog with high priority. Our goal is to have this fixed by the next GA, and we will provide updates here as work continues.

@AlBundy33
Copy link

Sounds good - can you describe what's slow so others can check whether they are affected or not?

@paragpandit123
Copy link
Author

Thanks Jeffery , when is the next GA ?

@Jeffery-Wasty
Copy link
Contributor

@AlBundy33 I'll update my comment.

@paragpandit123 January 31, 2025

@Jeffery-Wasty Jeffery-Wasty removed their assignment Nov 6, 2024
@thomasnikolay
Copy link

@Jeffery-Wasty Can you please give more information this issue? What is causing the issue? What are the symptoms of the error?

We also using mssql-jdbc 11.2.0.jre and MSSQL 19 on Windows OS.

We have cases in which queries are executed quickly and cases in which queries are executed very slowly. The DBAs couldn't find a reason on Database. It looks like there is a problem in the driver.

@Jeffery-Wasty
Copy link
Contributor

Hi @thomasnikolay,

The symptoms are slow performance when reading large amounts of data from a database (on the order of 1,000,000 rows with 500 columns). This performance was thought to be abnormally slow, as, when compared to jTDS, the JDBC driver takes 60 - 80% longer.

It's still not clear what the cause of the issue is. We were provided with 2 instances of customers reporting this issue (which includes this GitHub issue), with each customer providing us with a repro. Using the repros we were able to reproduce the issue, though only on high performance machines (this is in line with what the customers were experiencing).

However, an issue remains that while one of the customers was seeing this issue pop up with data sets with a large amount of NULL values only, we are seeing the performance issue regardless of the data issue. It's important that we're lined up on exactly how to reproduce this issue as if the issue is isolated only for large amounts of NULL values, that will greatly help us in nailing down the exact problem, and fix.

Currently the above is where I'm at, trying to get an accurate repro. I can provide further updates as my work continues on the issue.

Hope this helps clear up what exactly this issue is and how work is progressing on it!

Regarding your issue - I would encourage the same check as I described above. Compare the performance on our driver vs. the same performance using the jTDS driver. If they are the same, this may be a different issue, and it would be recommended to open a new GitHub issue. If there is a significant difference, then please provide more information on what queries are being executed and how the tables involved look like (many nulls, no nulls, size, etc.). Thanks!

@Jeffery-Wasty Jeffery-Wasty removed the Backlog The topic in question has been recognized and added to development backlog label Nov 22, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Performance-related The desired fix involves increasing the performance of a process.
Projects
MSSQL JDBC
Status: Under Investigation
Milestone
No milestone
Development

No branches or pull requests
7 participants
@thomasnikolay @AlBundy33 @lilgreenbird @Petro36 @Jeffery-Wasty @David-Engel @paragpandit123