April 20, 2018 at 10:44 am
Hello All,
I've seen this behavior now on 2 different queries on different databases, but still can't figure out anything on the Database side of things. A query being initiated from a Java App (JDBC driver I think) is taking in excess of 30 minutes, and then the App hits its 30 minute timeout threshold and cancels the query; however, when I take the exact same query and provide the one parameter value it uses and execute it as normal using SSMS, it takes about 1 second to return.
Anyone have any ideas of what I can check on the App side or even the DB/Instance side? I thought I read a while back that sometimes Driver and/or Driver settings could be the cause of Prepared Statements exhibiting this behavior, but I can't find that article again. I'm stumped at this point and not really sure what kind of direction I can give the developer (I am the DBA).
Any help or advice is appreciated.
Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64) Standard Edition (64-bit)
April 20, 2018 at 11:18 am
chris_barnhart - Friday, April 20, 2018 10:44 AMHello All,
I've seen this behavior now on 2 different queries on different databases, but still can't figure out anything on the Database side of things. A query being initiated from a Java App (JDBC driver I think) is taking in excess of 30 minutes, and then the App hits its 30 minute timeout threshold and cancels the query; however, when I take the exact same query and provide the one parameter value it uses and execute it as normal using SSMS, it takes about 1 second to return.Anyone have any ideas of what I can check on the App side or even the DB/Instance side? I thought I read a while back that sometimes Driver and/or Driver settings could be the cause of Prepared Statements exhibiting this behavior, but I can't find that article again. I'm stumped at this point and not really sure what kind of direction I can give the developer (I am the DBA).
Any help or advice is appreciated.
Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64) Standard Edition (64-bit)
How about encapsulating the code inside a stored procedure? Or is that what you meant by "Prepared Statement" ? I'd take a look at the execution plans to see if they're different, and also look for blocking. Also check the session settings for the SQL connection from Java.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 20, 2018 at 4:47 pm
Its a true Prepared Statement with only one parameter (@P0) as BigInt type...when I've asked in the past, the Developer states that
I could try putting in an STP, but I'd really like to figure out why this and one other query from this system behave this way. Certainly no blocking going on and the behavior is very consistent. The Wait Type is SOS Scheduler Yield when its running the whole time.
I believe the developer said that the prepared statement is done by the Driver as a setting. I'll see if I can get the session settings.
Just weird it runs in 1 second via SSMS as normal, without initiating the st prepare and st execute
Thanks
April 23, 2018 at 2:21 pm
Franklly, all the effort to play the games with PrepareStatement just doesn't make much sense to me, when you could just initiate a connection and execute a stored procedure. The time and trouble just to ensure that "prepare" alternative just don't seem worth the effort to me. If I were managing that developer, I'd require him or her to use a stored procedure, and explain in detail to me why he/she needs a "prepare statement" and what, exactly, the benefit of doing so is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 23, 2018 at 3:07 pm
There are numerous settings for the drivers that can affect things like this - and it depends on exactly what driver is used. And then when comparing execution times to how in runs in SSMS, there are a lot of variables with that no matter what the application, driver. Refer to:
Slow in the Application, Fast in SSMS?
It's highly unlikely to be anything with SQL Server. It's more likely to be the driver settings or which driver is being used.
There is an interesting discussion on whether it is even worth it to use sp_prepare, execute, etc:
What is the sense and benefit of using SqlCommand.Prepare()?
I agree with Steve - I don't get why the developer is going this route either. With the changes to SQL Server over the years, it's become a bit obsolete when interfacing with just SQL Server.
Sue
April 23, 2018 at 4:10 pm
I appreciate the feedback and will check driver settings...we just noticed that in the Trace, the Prepared version was being put into an explicit transaction, so I'm thinking SQL Server never got the COMMIT or ROLLBACK since we can't find it. We've seen this in other areas of this same code set when a query runs a long time and seems to abandon the transaction, but never gives the signal to SQL Server to let it go.
On a side note, the primary reason they are using Prepared Statements is to mitigate SQL Injection from a Compliance Perspective if I'm not mistaken.
Again, I appreciate the feedback.
Thanks,
Chris
April 23, 2018 at 4:31 pm
chris_barnhart - Monday, April 23, 2018 4:10 PMI appreciate the feedback and will check driver settings...we just noticed that in the Trace, the Prepared version was being put into an explicit transaction, so I'm thinking SQL Server never got the COMMIT or ROLLBACK since we can't find it. We've seen this in other areas of this same code set when a query runs a long time and seems to abandon the transaction, but never gives the signal to SQL Server to let it go.
On a side note, the primary reason they are using Prepared Statements is to mitigate SQL Injection from a Compliance Perspective if I'm not mistaken.Again, I appreciate the feedback.
Thanks,
Chris
You don't need to use prepared statements to mitigate SQL Injection. Not at all.
But on the abandoning transactions, there is also the problem with prepares not having matching sp_unprepare. It's mentioned in that second link I posted. In that link is another link...the link Watch out those prepared SQL statements
Getting to know everything about your driver is probably needed.
Sue
April 24, 2018 at 6:26 am
Sue_H - Monday, April 23, 2018 4:31 PMchris_barnhart - Monday, April 23, 2018 4:10 PMI appreciate the feedback and will check driver settings...we just noticed that in the Trace, the Prepared version was being put into an explicit transaction, so I'm thinking SQL Server never got the COMMIT or ROLLBACK since we can't find it. We've seen this in other areas of this same code set when a query runs a long time and seems to abandon the transaction, but never gives the signal to SQL Server to let it go.
On a side note, the primary reason they are using Prepared Statements is to mitigate SQL Injection from a Compliance Perspective if I'm not mistaken.Again, I appreciate the feedback.
Thanks,
ChrisYou don't need to use prepared statements to mitigate SQL Injection. Not at all.
But on the abandoning transactions, there is also the problem with prepares not having matching sp_unprepare. It's mentioned in that second link I posted. In that link is another link...the link Watch out those prepared SQL statements
Getting to know everything about your driver is probably needed.Sue
And to pile on to what Sue is saying, remember that with a prepare, you become dependent on the unprepare, which is not terribly different than adding transaction use to your query and forgetting to either COMMIT or ROLLBACK the transaction, leaving it hanging. It's just another dependency that you don't need. And frankly, anyone claiming that you need a "prepare" to avoid SQL injection is either 1) avoiding the work of doing it in the query, or 2) trying to buffalo you into their way of thinking so that you'll be easier to manipulate in the future, or possibly both. They are most certainly NOT doing you or company any favors.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply