April 14, 2014 at 9:46 am
Hi All,
I've been trying to speed up a query that is taking about 8 sec at the moment and runs against a table(dbo.Object) with ~15 million rows. ObjId is the PK of this table (which unfortunately is a VARCHAR and not an identity column/ surrogate key).
exec sp_executesql N'SELECT Obj.ObjId AS ObjId
FROM dbo.Object AS Obj
WHERE ( CAST( Obj.ParentId AS int) = @p__linq__0)
AND (Obj.ObjId IN (N''IH6HNF1'',N''IPE5XK3'',N''IBFDP22'',N''IPE61J0'',N''IPE65V4'',N''IPE6E65'',N''IPE6EC0'',N''IPE6EJ9'',
...........................................................................N''IPE7433'',N''IPE74D7'',N''IPE74H1'',N''IPE74S3'',N''IPE74V7'',N''IDAF3W2'',N''IPE75Q3'',N''IC42Q72'',N''IPE75W7'',N''IPE75Z5'',
N''IQEP0J9'',N''IPVJM90''))'
,N'@p__linq__0 int',@p__linq__0=4602
I changed it to the following, by replacing IN with a JOIN to the list of about 75 ID's (created by a UNION). The new query runs in about 1sec, instead of 7-8sec :
exec sp_executesql N'SELECT Obj.ObjId AS ObjId
FROM dbo.Object AS Obj
JOIN (SELECT N''IH6HNF1'' AS ObjId
UNION SELECT N''IPE5XK3'' UNION SELECT N''IBFDP22''
............................
............................
UNION SELECT N''IQEP0J9'' UNION SELECT N''IPVJM90''
) CT
ON Obj.ObjId = CT.ObjId
WHERE ( CAST( Obj.ParentId AS int) = @p__linq__0)'
,N'@p__linq__0 int',@p__linq__0=4602
The original query is Entity Framewok generated.
My new version does run a few times faster but seems like a very primitive way of doing this ... haven't come up with anything better yet.
Also, EF converting the VARCHAR to NVARCHAR is apparently slowing things down, but not sure if this could be avoided from within the SQL query.
Is there a better, more elegant way to speed up the query?
Any ideas would be much appreciated.
Thanks,
Svet
April 14, 2014 at 10:25 am
Two choices I can think of quickly, 1) figure out how to keep EF from converting the strings from varchar to nvarchar or 2) modify your table so that ObjId is nvarchar instead of varchar.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply