March 1, 2014 at 9:45 am
I am working with a 2008 R2 database with a BOBJ frontend. I am trying to figure out how make the queries from the front end to be treated as parameterized queries.
The set up is something like this: The UI uses a data access layer called Universe which in turn uses a QLEDB driver (sql nacl10; the one with 2008 r2) to make calls to the database. There is a fixed set of different queries being called (no ad-hoc stuff here) with different parameters. For example, one set of queries might be like "select col_a1, col_a2 from table_aa where col_aid = ?"; another set of queries might be like "select col_b1, col_b2 from table_bb where col_bid = ?" and so on. So in essence, there are a limited number of known queries being called; except with a different parameters.
The driver follows a prep-exec model; so the first call to the database for any query is to first prepare the execution plan (sp_prepare), followed by execution (sp_execute), followed by sp_unpreprare.
example:
declare @p1 int
exec sp_prepare @p1 output, NULL, N'select col_a1, col_a2 from table_aa where col_aid = 1' (please note that the second parameter of exec sp_prepare is NULL and the input parameter is a literal in the where clause.)
exec sp_execute @p1
exec sp_unprepare @p1
The sp_unprepare is not really removing the plan from the cache (which is good in this case); it is merely unassociating the plan handle (@p1) from the generated plan.
However, the database engine is always spending time to prepare a plan for the same statement when the parameter is not the same. If a call was made again with the same parameter, then it is skipping plan generation. Basically, the queries are not being parameterized for plan generation purposes.
The issue is; these queries are spending a lot of time on plan generation rather than execution. Typical numbers on our dev box is around 300 ms for plan generation and 20-30 ms for execution. And for some more complex queries, the paln generation is around 1000 - 1500 ms whereas the execution is around 40-50 ms.
SQL Server can re-use the same plan; however, I am not able to figure out how to make that happen in this set up. Have tried ForcedParameterization. Also, if the prepare call takes the form of exec sp_prepare @p1 OUTPUT, @input int, "select col_aa, col_bb from table_aa where col_aid = @input", then SQL Server is not spending to generate a duplicate plan.However, I do not know how I can configure the OleDB driver to make calls in that fashion.
In essence, I am looking to see how I can prevent "duplicate" plan generations from happening.
Any help is greatly appreciated.
March 1, 2014 at 10:33 am
Is the use of sp_prepare and sp_unprepare required by the driver?
In my past experience with this, doing this in separate steps typically increases the total time involved because you are forcing more network round-trips to the statement execution: 1) for the prepare, 1 for the statement, and 1 for the unprepared. While I am no expert, it is my opinion that with using sp_executeSQL, the other two steps are not needed here.
I think the issue you are describing here might be covered in the link below, it describes possible issues with the sp_unprepare statement and how it doesn't always do a good job of "cleaning up" - http://blogs.msdn.com/b/sqlcat/archive/2010/12/21/watch-out-those-prepared-sql-statements.aspx
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 3, 2014 at 5:59 am
Not sure if I know the answer to the question of sp_prepare being "required" by the driver. But that is how the queries are being called.
I am looking for the ways I can tell SQL Server to treat them as parameterized queries or change how the OLEDB driver makes the calls to send the queries in parameterized fashion. (as exec sp_preprate @p1 int output, @input int.....)
March 3, 2014 at 9:40 am
They are already parameterized queries and the execution plans are going to be the same either way. To get around the cache bloat (and you may have already configured this), ensure you have the "optimize for ad hoc workloads" option enabled on your SQL Server. You can run this by executing SP_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE WITH OVERRIDE
GO
To properly define your parameterized query try:D ECLARE @sql NVARCHAR(125), @p1 INT
SET @sql = N'select col_a1, col_a2 from table_aa where col_aid = @p1'
SET @p1 = 1
EXEC sp_exe cutesql @sql, N'@p1 int', @p1
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 3, 2014 at 9:42 am
MyDoggieJessie (3/3/2014)
To get around the cache bloat (and you may have already configured this), ensure you have the "optimize for ad hoc workloads" option enabled on your SQL Server.
OR... start using stored procedures.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2014 at 12:54 pm
Hi Jessie and Jeff...thanks for taking the time...I might have not explained the scenario correctly and I apologise...
Here is how things go on:
The BOBJ developers design a "Universe" that contains various tables and views (no stored procedures...thats another story) from the database that the end user wants to query on. This Universe also contains the required table relationships. So the end user looks at the tables and columns that are exposed to him by way of the Universe and creates a particular report. The Universe then creates a query string based on the relationships and what the user is looking for. This query is then passed on to the database by the OLEDB driver that the BOBJ Universe is using.
I have very limited control on how this query string is generated and practically no control on how it is called to the database.
That is, based on the end user report selections, the BOBJ universe might come up with the string "select col_a from dbo.table_a where id = 2". Then the OLEDB takes this string and communicates with the database as follows:
declare @p1 int
exec sp_prepare @p1 output, NULL, N 'select col_a from dbo.table_a where id = 2'
exec sp_execute @p1
exec sp_unprepare @p1
And, for another end user scenario, the BOBJ universe might come up with the string "select col_a from dbo.table_a where id = 5". Then the OLEDB driver takes this string and communicates with the database as follows:
declare @p1 int
exec sp_prepare @p1 output, NULL, N'select col_a from dbo.table_a where id = 5'
exec sp_execute @p1
exec sp_unprepare @p1
In the above examples, the only difference is the value of id in the where clause. In both cases, because of how the sp_prepare statement is not parameterized, sql server generates a new plan. The only case it doesn't generate a new plan is when the same parameter value comes back in.
I cant use stored procedures because the Universe (which is the bridge between the end user and the database objects) cannot put Stored Procedures in the same Universe that houses tables/views. If you want to use Stored Procedures in a Universe, then you need to create a different type of Universe called "Stored Procedure Universe" which (as you would guess) cannot host tables or views. Besides this incompatibility, there are other constraints with using a Stored Procedure Universe, so using a Stored Procedure Universe is not typically an option the management wants to take.
So the OLEDB driver is the one making the calls in the fashion mentioned above (I cant use sp_executesql or even make sp_prepare calls in a parameterized fashion). Or if I can make the OLEDB driver use sp_executesql or parameterized sp_prepare calls, how would I do it is the question. And if that cant be done, what would I do in SQL Server to treat the above examples as parameterized queries. ForcedParameterization did not do what I was hoping for. Niether did "optimize for ad-hoc" setting. I just wanted to see if I was doing some thing wrong or if there is a different way to do this.
And by "parameterized queries", I mean that SQL Server would not have to spend time generating a new plan just because the parameter is different but rest of query is the same. Cos this extra time that SQL Server is spending to create new plans for every query is what I am trying to eliminate.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply