search

microsoft / mssql-jdbc

The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity with SQL Server through the standard JDBC application program interfaces (APIs).
MIT License
1.04k stars 421 forks source link

Slowness in MS SQL Server JDBC driver #2432

Open paragpandit123 opened 1 month ago

paragpandit123 commented 1 month ago

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 commented 1 month ago

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.

the sample we mentioned where there are 71 M records , it use to complete under 30 mins and not it takes around 6 -7 hrs

Thanks

Parag Pandit

Application Architect

@.**@.> 732.789.7326

[Logo Description automatically generated]http://www.lumeris.com/ [Icon Description automatically generated] https://www.linkedin.com/company/lumeris [Logo Description automatically generated] https://twitter.com/lumeris

From: Jeff Wasty @.> Sent: Friday, May 24, 2024 12:14 PM To: microsoft/mssql-jdbc @.> Cc: Parag Pandit @.>; Mention @.> Subject: [External] Re: [microsoft/mssql-jdbc] Slowness in MS SQL Server JDBC driver (Issue #2432)

EXTERNAL

Client / Partner / Business Vendor

This email originated from a Lumeris affiliated entity. Please continue to exercise caution when opening links or attachments.

Note: If there is a security concern, please contact the Service Desk immediatelyhttps://lumeris.service-now.com/sp?id=sc_cat_item&sys_id=3f1dd0320a0a0b99000a53f7604a2ef9&sysparm_category=e15706fc0a0a0aa7007fc21e1ab70c2f.

Hi @paragpandit123https://urldefense.com/v3/__https://github.com/paragpandit123__;!!IKIcsDr4!WsNtobhynlN7qZNvhllNrYLin7YsBqw13ORyIeRtItl4pfyIkMOiMpbuikV1QPgOLlpk2NsMR-Nkpd1dZTwYcEs$,

The issue is that there is "slowness" in the driver, how slow? How long does it take to complete the above mentioned query? How much longer is this than what you expect?

When you say "slow", what is this in comparison to? Different queries? Or these same queries but tried with another version of the MSSQL-JDBC driver? Or are you comparing to another JDBC driver?

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https://github.com/microsoft/mssql-jdbc/issues/2432*issuecomment-2129920890__;Iw!!IKIcsDr4!WsNtobhynlN7qZNvhllNrYLin7YsBqw13ORyIeRtItl4pfyIkMOiMpbuikV1QPgOLlpk2NsMR-Nkpd1dYQdM5YI$, or unsubscribehttps://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/A7SAXHWPA75VFMTWGEMAICTZD5RQFAVCNFSM6AAAAABIHXCWCOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMRZHEZDAOBZGA__;!!IKIcsDr4!WsNtobhynlN7qZNvhllNrYLin7YsBqw13ORyIeRtItl4pfyIkMOiMpbuikV1QPgOLlpk2NsMR-Nkpd1dwS0Hc64$. You are receiving this because you were mentioned.Message ID: @.***>

This e-mail and any files accompanying this e-mail contain confidential information belonging to the sender. This information may contain protected health information and other material that is legally protected, privileged or proprietary. The information is intended only for the use of the individual or entity named above. The intended recipient of this e-mail is prohibited from disclosing this information to any other party unless required to do so by law or regulation and is required to delete or destroy the information after its stated need has been fulfilled. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on or regarding the contents of this electronically transmitted information is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately and delete this message.

Jeffery-Wasty commented 1 month ago

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 commented 1 month ago

FYI, this is a production issue and needs a quick resolution.

Parag Pandit Application Architect

@.**@.> 732.789.7326

[Logo Description automatically generated]http://www.lumeris.com/ [Icon Description automatically generated] https://www.linkedin.com/company/lumeris [Logo Description automatically generated] https://twitter.com/lumeris

From: Jeff Wasty @.> Sent: Friday, May 24, 2024 12:21 PM To: microsoft/mssql-jdbc @.> Cc: Parag Pandit @.>; Mention @.> Subject: [External] Re: [microsoft/mssql-jdbc] Slowness in MS SQL Server JDBC driver (Issue #2432)

EXTERNAL

Client / Partner / Business Vendor

This email originated from a Lumeris affiliated entity. Please continue to exercise caution when opening links or attachments.

Note: If there is a security concern, please contact the Service Desk immediatelyhttps://lumeris.service-now.com/sp?id=sc_cat_item&sys_id=3f1dd0320a0a0b99000a53f7604a2ef9&sysparm_category=e15706fc0a0a0aa7007fc21e1ab70c2f.

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.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https://github.com/microsoft/mssql-jdbc/issues/2432*issuecomment-2129935362__;Iw!!IKIcsDr4!WA83AhyiPMhS8t_uAdgyKHsQ0BjIRQRtH43l0yR4WhSyU6Dud2A4Y098AlRzH_InKBiX5TjdGQwoBIntKv7yuUA$, or unsubscribehttps://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/A7SAXHVZFBNH6VS5U63UOL3ZD5SJDAVCNFSM6AAAAABIHXCWCOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMRZHEZTKMZWGI__;!!IKIcsDr4!WA83AhyiPMhS8t_uAdgyKHsQ0BjIRQRtH43l0yR4WhSyU6Dud2A4Y098AlRzH_InKBiX5TjdGQwoBIntFosUmnw$. You are receiving this because you were mentioned.Message ID: @.***>

This e-mail and any files accompanying this e-mail contain confidential information belonging to the sender. This information may contain protected health information and other material that is legally protected, privileged or proprietary. The information is intended only for the use of the individual or entity named above. The intended recipient of this e-mail is prohibited from disclosing this information to any other party unless required to do so by law or regulation and is required to delete or destroy the information after its stated need has been fulfilled. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on or regarding the contents of this electronically transmitted information is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately and delete this message.

Jeffery-Wasty commented 1 month ago

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:

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 commented 1 month ago

I have attached an image with connection details. I guess we need a call / working session. Which will avoid back and forth .

How about sometime Monday , I am on east coast

Thanks

Parag Pandit Application Architect

@.**@.> 732.789.7326

[Logo Description automatically generated]http://www.lumeris.com/ [Icon Description automatically generated] https://www.linkedin.com/company/lumeris [Logo Description automatically generated] https://twitter.com/lumeris

[cid:64ca1470-9719-415e-a462-b11450997b2f]

From: Jeff Wasty @.> Sent: Friday, May 24, 2024 3:28 PM To: microsoft/mssql-jdbc @.> Cc: Parag Pandit @.>; Mention @.> Subject: [External] Re: [microsoft/mssql-jdbc] Slowness in MS SQL Server JDBC driver (Issue #2432)

EXTERNAL

Client / Partner / Business Vendor

This email originated from a Lumeris affiliated entity. Please continue to exercise caution when opening links or attachments.

Note: If there is a security concern, please contact the Service Desk immediatelyhttps://lumeris.service-now.com/sp?id=sc_cat_item&sys_id=3f1dd0320a0a0b99000a53f7604a2ef9&sysparm_category=e15706fc0a0a0aa7007fc21e1ab70c2f.

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:

Also please enable logging (https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16https://urldefense.com/v3/__https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16__;!!IKIcsDr4!X1PcvwDW6vnR1ruL6JWmXScTfDYpTzGQyZJfNqsEwbxrLYUcfE_Qsak1aQSdsEM2BwJCREqdojMmknRLxaLr5XI$) 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.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https://github.com/microsoft/mssql-jdbc/issues/2432*issuecomment-2130230756__;Iw!!IKIcsDr4!X1PcvwDW6vnR1ruL6JWmXScTfDYpTzGQyZJfNqsEwbxrLYUcfE_Qsak1aQSdsEM2BwJCREqdojMmknRLN5nJCS8$, or unsubscribehttps://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/A7SAXHSM3TXZYP4XPZO74ALZD6IGJAVCNFSM6AAAAABIHXCWCOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMZQGIZTANZVGY__;!!IKIcsDr4!X1PcvwDW6vnR1ruL6JWmXScTfDYpTzGQyZJfNqsEwbxrLYUcfE_Qsak1aQSdsEM2BwJCREqdojMmknRLGd0Vyn0$. You are receiving this because you were mentioned.

This e-mail and any files accompanying this e-mail contain confidential information belonging to the sender. This information may contain protected health information and other material that is legally protected, privileged or proprietary. The information is intended only for the use of the individual or entity named above. The intended recipient of this e-mail is prohibited from disclosing this information to any other party unless required to do so by law or regulation and is required to delete or destroy the information after its stated need has been fulfilled. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on or regarding the contents of this electronically transmitted information is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately and delete this message.

Jeffery-Wasty commented 1 month ago

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 commented 1 month ago

I will work with DBA's and try to get that log .

[cid:d9e5e708-4130-44dd-be01-cabfd109b6a0]

Parag Pandit Application Architect

@.**@.> 732.789.7326

[Logo Description automatically generated]http://www.lumeris.com/ [Icon Description automatically generated] https://www.linkedin.com/company/lumeris [Logo Description automatically generated] https://twitter.com/lumeris

From: Jeff Wasty @.> Sent: Friday, May 24, 2024 3:49 PM To: microsoft/mssql-jdbc @.> Cc: Parag Pandit @.>; Mention @.> Subject: [External] Re: [microsoft/mssql-jdbc] Slowness in MS SQL Server JDBC driver (Issue #2432)

EXTERNAL

Client / Partner / Business Vendor

This email originated from a Lumeris affiliated entity. Please continue to exercise caution when opening links or attachments.

Note: If there is a security concern, please contact the Service Desk immediatelyhttps://lumeris.service-now.com/sp?id=sc_cat_item&sys_id=3f1dd0320a0a0b99000a53f7604a2ef9&sysparm_category=e15706fc0a0a0aa7007fc21e1ab70c2f.

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.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https://github.com/microsoft/mssql-jdbc/issues/2432*issuecomment-2130255936__;Iw!!IKIcsDr4!Vlsa2qYrNr0wLgySdJ4L7Ws7WOnm8z2B_o9nNOn42qjH9idc2BywPGniYVkhR7EyPX_fl_1lKMnXUpfpjhrWdiE$, or unsubscribehttps://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/A7SAXHWNDTR3VEPQASAKHJTZD6KSZAVCNFSM6AAAAABIHXCWCOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMZQGI2TKOJTGY__;!!IKIcsDr4!Vlsa2qYrNr0wLgySdJ4L7Ws7WOnm8z2B_o9nNOn42qjH9idc2BywPGniYVkhR7EyPX_fl_1lKMnXUpfpjcDyJnI$. You are receiving this because you were mentioned.Message ID: @.***>

This e-mail and any files accompanying this e-mail contain confidential information belonging to the sender. This information may contain protected health information and other material that is legally protected, privileged or proprietary. The information is intended only for the use of the individual or entity named above. The intended recipient of this e-mail is prohibited from disclosing this information to any other party unless required to do so by law or regulation and is required to delete or destroy the information after its stated need has been fulfilled. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on or regarding the contents of this electronically transmitted information is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately and delete this message.

paragpandit123 commented 1 month ago

This log should be enabled on SQL server right ?

paragpandit123 commented 1 month ago

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

David-Engel commented 1 month ago

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 commented 1 month ago

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

David-Engel commented 1 month ago

Contact Snaplogic support for assistance with logging in their application.

paragpandit123 commented 1 month ago

We are working with snaplogic and will get back

AlBundy33 commented 1 month ago

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 commented 4 weeks ago

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 commented 4 weeks ago

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

paragpandit123 commented 4 weeks ago

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 commented 4 weeks ago

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 commented 4 weeks ago

We are already working on getting those logs .

AlBundy33 commented 4 weeks ago

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.

Jeffery-Wasty commented 3 weeks ago

Hi @paragpandit123,

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

paragpandit123 commented 3 weeks ago

We are getting close and will be able to send the logs soon.

Parag Pandit Application Architect

@.*** 732.789.7326

[Logo Description automatically generated]http://www.lumeris.com/ [Icon Description automatically generated] https://www.linkedin.com/company/lumeris [Logo Description automatically generated] https://twitter.com/lumeris

From: Jeff Wasty @.> Sent: Tuesday, June 11, 2024 5:36:35 PM To: microsoft/mssql-jdbc @.> Cc: Parag Pandit @.>; Mention @.> Subject: [External] Re: [microsoft/mssql-jdbc] Slowness in MS SQL Server JDBC driver (Issue #2432)

EXTERNAL

Client / Partner / Business Vendor

This email originated from a Lumeris affiliated entity. Please continue to exercise caution when opening links or attachments.

Note: If there is a security concern, please contact the Service Desk immediatelyhttps://lumeris.service-now.com/sp?id=sc_cat_item&sys_id=3f1dd0320a0a0b99000a53f7604a2ef9&sysparm_category=e15706fc0a0a0aa7007fc21e1ab70c2f.

Hi @paragpandit123https://urldefense.com/v3/__https://github.com/paragpandit123__;!!IKIcsDr4!VHgPEbmXJY9gpA63Grogqpriax_aH8lXLqTDna3m6GnzE5S4qSS4ObEmVXzUUEdVBZ7rZLa6ScwZ8FwBDxR8kgg$,

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

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https://github.com/microsoft/mssql-jdbc/issues/2432*issuecomment-2161639134__;Iw!!IKIcsDr4!VHgPEbmXJY9gpA63Grogqpriax_aH8lXLqTDna3m6GnzE5S4qSS4ObEmVXzUUEdVBZ7rZLa6ScwZ8FwBDjCXBJk$, or unsubscribehttps://urldefense.com/v3/__https://github.com/notifications/unsubscribe-auth/A7SAXHUI6SBPWZYTOZARKIDZG5UWHAVCNFSM6AAAAABIHXCWCOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNRRGYZTSMJTGQ__;!!IKIcsDr4!VHgPEbmXJY9gpA63Grogqpriax_aH8lXLqTDna3m6GnzE5S4qSS4ObEmVXzUUEdVBZ7rZLa6ScwZ8FwBTsJBYwU$. You are receiving this because you were mentioned.Message ID: @.***>

This e-mail and any files accompanying this e-mail contain confidential information belonging to the sender. This information may contain protected health information and other material that is legally protected, privileged or proprietary. The information is intended only for the use of the individual or entity named above. The intended recipient of this e-mail is prohibited from disclosing this information to any other party unless required to do so by law or regulation and is required to delete or destroy the information after its stated need has been fulfilled. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on or regarding the contents of this electronically transmitted information is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately and delete this message.

paragpandit123 commented 2 weeks ago

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

Jeffery-Wasty commented 2 weeks ago

You can email us the logfile - mssqljdbc@microsoft.com

paragpandit123 commented 2 weeks ago

the file size is in GB

Jeffery-Wasty commented 2 weeks ago

Is there some storage account you can upload the log to, and then give us access to download?

paragpandit123 commented 2 weeks ago

we compressed it and now its 200Mb

paragpandit123 commented 2 weeks ago

usually MS provide us the upload site

Jeffery-Wasty commented 2 weeks ago

We don't have such a site available. You originally reached out to us by email, through MS Support. Can you contact the MS support personnel you were originally in contact with and ask them to upload the file to their file sharing site, and then share the link with us? This is how we normally receive external files in other cases.

paragpandit123 commented 2 weeks ago

We have uploaded the file on to Case number : 2404240010002890

Jeffery-Wasty commented 2 weeks ago

Do you have a link you can share with us?

paragpandit123 commented 2 weeks ago

We dont have the link .People on that case would be able to help you with that

Jeffery-Wasty commented 2 weeks ago

Ok, thank you.

paragpandit123 commented 2 weeks ago

Any update on this ?

Jeffery-Wasty commented 2 weeks ago

I haven't had a chance to fully look over the log, but from what I see nothing stands out as taking too long. How many records are being processed during this ~30 min time interval, and what was the processing time with jTDS?

If possible, could similar logs be captured with the default FINER level logging? FINER provides enough information for us to find a problem.

Petro36 commented 1 week ago

We had the same issue with batch inserts. A batch of 1000 records went from 2s to 30s for execution. We had the same driver version and the same configuration both in integration and in production envs and the issue was present only in production. It was caused by the driver executing 1000 single insert queries instead of only one batch insert. Since the only difference between the 2 envs was the java version (21 on integration, 11 on production) we've upgraded prod java version to 21 and the issue was solved.

paragpandit123 commented 1 week ago

In our case we are just doing a simple select , where there is no question of batch size