January 9, 2014 at 7:42 am
Hi,
When I run the following query
CMD A : SELECT top 1 * from [DB].[dbo].[TABLE1] WHERE langue= N'en') and date_publication < ''20131125 00:00:00.000''
I would like SQL server to use the query plan of that query
CMD B : SELECT top 1 * from [DB].[dbo].[TABLE1] WHERE langue= convert(char(2),N''en'') and date_publication < ''20131125 00:00:00.000''
Can someone tell me how to use sp_create_plan_guide for that?
I first get the XML query plan of CMD B with
set statistics xml on
SELECT top 1 * from [DB].[dbo].[TABLE1] WHERE langue= convert(char(2),N''en'') and date_publication < ''20131125 00:00:00.000''
Then
EXEC sp_create_plan_guide @name = N'[MyPlanGuide]',
@stmt = N'SELECT top 1 * from [DB].[dbo].[TABLE1] WHERE langue= N'en') and date_publication < ''20131125 00:00:00.000'' '
, @type = N'SQL'
, @module_or_batch =NULL
, @hints = N'OPTION (USE PLAN N'' .../... XML query plan .../...")
Then I try to validate my plan guide (I only have 1 plan guide)
SELECT plan_guide_id, msgnum, severity, state, message
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
and I get
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
Can anyone help?
regards
eric
January 9, 2014 at 9:46 am
Hmm. Someone with more experience will probably be more useful here, but there's a few things that are out of the ordinary with this question.
First, second-guessing the query optimizer is usually regarded as a bad practice; the optimizer will usually find the most efficient routes for query execution, save for some odd cases. In this case, you're trying to replace a plan with another that uses a CONVERT in the WHERE clause; using functions in the WHERE clause tends to force index scans, which would slow the process down.
Second, the problem you're experiencing stems from that CONVERT function, I'd guess; you're trying to replace a plan for a query that doesn't use functions with the plan for a query that does. Since the first query isn't using functions, asking the optimizer to evaluate it according to the use of a function won't work.
Third, neither query has an ORDER BY clause, but they're both using TOP 1; this will lead to largely unreliable results. There's no inherent ordering to the result set, so the data returned by the queries can (and quite possibly will) change from one execution to the next.
If you could provide more explanation on why you'd like things to run this way, however, there's probably a way to fix things up, either through indexing or a bit of rewriting!
- 😀
January 9, 2014 at 9:53 am
I forget to mentioned that my query ends with "order by date_publication DESC"
This query is send to SQL as ad hoc query from the client and I don't have access to the client code, in fact becaseu the JAVA driver is Unicode compliant all string are unicode and use N'string' and this is why I need to convert the string into char but once again I don't whant to update the table definition and I cannot update tthe ad hoc query
in this situation using sp_create_plan_guide seems to be the best approach
January 9, 2014 at 10:06 am
Ah, I see. That clears quite a few things up!
There's still the question of what you'd like the query to do, however; I'm guessing that the [langue] column is a CHAR(2) type from your posted conversion code. If so, running a query with a N'-prefixed string will result in an index scan, from (I presume) an implicit conversion.
If my reasoning is correct, there's unfortunately little that can be done here, to the best of my knowledge. Without the CONVERT clause, those queries will run with index scans at best, because of the implicit conversion. With the CONVERT clause, they'll still run as scans, because of an explicit conversion.
As it stands, either the query or the table would have to be adjusted, from what I understand, and those don't seem to be workable options in your case, unfortunately. Using a plan guide wouldn't resolve this either, I don't think, because there's still going to be a matter of a conversion of some sort happening along the way.
Someone else will definitely be of more use here, though. My knowledge of these internal matters is still quite shaky, and I'd gladly be proven wrong in the process of learning more about this!
- 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply