September 16, 2014 at 12:43 pm
Hi,
We are getting very bad performance.
The execution plan it is showing predicate.
The query is
select serialnumber from sampletable where id in (1,2,3)
serialnumber has Index but the id has no index. When I tested by creating the index on id column but it is not giving much performance improvement.
Here serialnumber column is 'varchar' and id column is 'varchar'
The predicate from the execution plan is
convert_implict (int,[dbname].[dbo].[sampletable].(id,0)=3 or convert_implict (int,[dbname].[dbo].[sampletable].(id,0)=2 or convert_implict (int,[dbname].[dbo].[sampletable].(id,0)=1
so I thought id column is varchar and we comparing with int and I changed the query
select serialnumber from sampletable where id in ('1','2','3') but still it is giving predicate
[database].[dbo].[sampletable].[id]='1' or [database].[dbo].[sampletable].[id]='2' or [database].[dbo].[sampletable].[id]='3'
so that means instead of in do I need to compare id individually?
September 16, 2014 at 12:47 pm
Create an index::
keyed on : id
INCLUDE : serialnumber
Use quotes in the WHERE:
WHERE id IN ('1', '2', '3')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2014 at 1:04 pm
Yes. I use the quotes
WHERE id IN ('1', '2', '3'), it is better now
but still there is more cpu time in the message from the execution plan.
Also still I can see predicate in the execution plan from the tool tip
[database].[dbo].[sampletable].[id]='1' or [database].[dbo].[sampletable].[id]='2' or [database].[dbo].[sampletable].[id]='3'
That means it is not completely optimized.
What that predicate means here?
September 16, 2014 at 1:09 pm
It would be easier to make suggestions on how to address the performance issues if you posted the execution plan and the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2014 at 1:13 pm
Hi,
I can't provide the execution plan but the query is
select serialnumber from sampletable where id in (1,2,3)
September 16, 2014 at 1:19 pm
ramana3327 (9/16/2014)
Hi,I can't provide the execution plan but the query is
select serialnumber from sampletable where id in (1,2,3)
CREATE NONCLUSTERED INDEX sampletable__IX_id ON sampletable ( id ) INCLUDE ( serialnumber );
select serialnumber from sampletable where id in ('1','2','3')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply