Performance tuning with Prepared Statements | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance tuning with Prepared Statements

Hi experts out there, I am a newbie with SQL Server and would like to seek your advice. Currently, I am re-writing a bunch of queries for an existing product. The size of my DB can be pretty huge and can easily grow to a few hundred thousand records (500K to a million). The existing queries use "Prepared Statements", but not quite in the way it was designed for. It basically dynamically creates the sql without any parameterization and execute with a "prepared" statement every time the use case was triggered by the user. e.g. pst = connection.prepareStatement(strQuery);
resultSet = pst.executeQuery(); My understanding is that it would be better off using a statement in this case, as the overhead of creating the prepared statement is bigger. With the possibility of a pretty huge DB, i would want to optimize my queries wherever & whenever I can at the application level. The problem I faced here is that my queries are very much dependent on user selection and inputs. For example, The user can retrieves a list of cars that are avaiable from Company A or a list of companies or from any companies. e.g. "…… AND (CARS_LIST.COMPANY_ID = ? OR CARS_LIST.COMPANY_ID = ?) …."
e.g. "…… AND (CARS_LIST.COMPANY_ID = ?) …." How am I going to use Prepared Statements in this case? The total combination of queries in my application can grow very large (e.g. 200-300). I was thinking of creating the prepared statements at runtime on demand and caching them in memory. Pls advice. Thanks. Regards.
This is more of a JDBC/data access layer question than a SQL Server question. If you need functionality of prepareStatement() use it instead of createStatement(). I’m not sure how prepareStatement() works with SQL Server and if you see any benefits. For example the caching that occur of the statments are they on the client side (web server) or the server side (sql server)? I would probably start off by letting SQL Server handle as much of the processing that is database related and then try to tune it further if you run into any issues. At least SQL Server 2000 has become a lot better at caching dynamic queries. An example of a performance issue that is common in web applications in the Windows world when using ADO/ODBC/OLEDB is that the client application is using a lot of slow server side cursors instead of client side. If there are similar cursor options when using JDBC as your data access layer I would probably look into those.