April 20, 2009 at 12:57 pm
I have a query (too long to paste here), one of the where clauses is this:
1) -- AND dbo.BO_ProjectIDAllPrompt.ProjectFilter IN ('2986','2985','2983','2981','2984','2711','2634','2982') --*GOOD
2)-- AND cast(dbo.BO_ProjectIDAllPrompt.ProjectFilter as varchar(10)) IN ('2711','2634')
3) AND dbo.BO_ProjectIDAllPrompt.ProjectFilter IN ('2711','2634') --*Query Timout
The field ProjectFilter is nchar(10), the table is two columns (ProjectFilter nchar(10), ProjectID int), and there is a primary key on the table for these two columns in this order. This table has about 5500 rows.
When the query is ran using option 3 above, it never returns, if either of the first two options are used, the query returns in under 15 seconds.
Any ideas?
April 20, 2009 at 1:24 pm
You are getting an implicit conversion from char to nchar in your IN clause. Try this:
AND dbo.BO_ProjectIDAllPrompt.ProjectFilter IN (N'2711',N'2634')
Which specifies that the constants are NCHAR.
Also what is the estimated execution plan?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 20, 2009 at 1:30 pm
Yes, I tried that also, that also never comes back.
The only difference I see between the execution plans, is it uses a clustered index seek for the original version (and the N'1234' version), and a clustered index scan for the “cast” version.
April 20, 2009 at 1:45 pm
Are you only querying the single table? What's the rest of the criteria? Without seeing the whole query it's hard to say what is causing the issue. In theory the clustered index seek should be the best operator and should be quick, so it may have something to do with the combination of criteria.
The query with the cast operation would at best do an index scan because it has to read the whole table to apply the cast before matching on the criteria.
What happens if you do an = with just one of the id's instead of the IN.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 20, 2009 at 1:54 pm
the query is based on 17 tables. If I do a straight select from the table in question, it comes back fine. If I use an = in the clause, it doesn't comes back. If I add more values to the IN clause, it comes back fine. When I have only one or two values in the IN clause, it doesn't come back, three or more values in the IN clause works just fine.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply