Microsoft JDBC driver behavour with Prepared Statments
Hi All,
We have noticed a strange behavior with an application that uses the Microsoft JDBC 4.0 driver, we are unsure if its the application or the driver. This application makes use of the sp_prepexec function for all of the queries. What we are seeing is the application passes the sp_prepexec through to sql, sql executes & returns the results then we se a sp_unprepare almost right away. Annoying we see the exact same query come in a millisecond late and it goes through the same prep and de-prep process every time.
We where wondering if it is the driver that is issuing the de-prep statment, or would that have to be initiated from the application? While vendor is suggesting that it is not their application doing this, I have my doubts due to the half hearted response.
We have noticed a strange behavior with an application that uses the Microsoft JDBC 4.0 driver, we are unsure if its the application or the driver. This application makes use of the sp_prepexec function for all of the queries. What we are seeing is the application passes the sp_prepexec through to sql, sql executes & returns the results then we se a sp_unprepare almost right away. Annoying we see the exact same query come in a millisecond late and it goes through the same prep and de-prep process every time.
We where wondering if it is the driver that is issuing the de-prep statment, or would that have to be initiated from the application? While vendor is suggesting that it is not their application doing this, I have my doubts due to the half hearted response.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I was always under the impression that the purpose of a prepared statment was to be prepared once and then re-executed over and over again without the need to recomplie the process.Yes, what should change are the values of the bind variables. I think the example(s) just might no be very good. Maybe we could find some more ..?
ASKER
It was the only answer...so points for your time and effort
ASKER
I see the fact the application is preparing a query, running, decompling it and then putting the same query through the extact same process as very wastefull. I'd almost suggest that it would be more efficent from a ms-sql point of view to run in-line queries with out the sp_prepexec over head, yes this does lend it's self to be more exposed to sql injection attacks. In the ideal way would be stored proc's but the application has a abstraction layer so it can be DB generic, hence no stored proc's
Cheers
JL