January 8, 2007 at 4:23 pm
Is there a better way of writing this query?
The @List can be up to 100 consecutively.
The fnSplitString basically returns a table with 1 column that has the integers from the @list.
Whats happening is that its performing a Table Scan on the EmpApp Table on the EmpId Column.
The EmpId Column is a NON-Clustered Index and the Statistics are up to date. In addition, there is little fragmentation on this Index, the fragmentation level is Scan Density [Best Count:Actual Count].......: 95.00%
Any one?
DECLARE @list VARCHAR(200)
SET @list = '1, 2, 3, 4,5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,'
UPDATE EmpApp
SET StartDate = getdate(), StartedBy = 1
WHERE EmpId IN (SELECT VALUE
FROM dbo.fnSplitString(@list,','))
January 8, 2007 at 5:46 pm
I think it's because you have a function call in the where clause.
what does the plan look like if you take the function call out, that is:
UPDATE EmpApp
SET StartDate = getdate(), StartedBy = 1
WHERE EmpId IN (1, 2, 3, 4,5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
---------------------------------------
elsasoft.org
January 8, 2007 at 5:59 pm
UPDATE E
SET StartDate = getdate(), StartedBy = 1
FROM EmpApp E
INNER JOIN dbo.fnSplitString(@list,',') L ON E.EmpId = L.VALUE
_____________
Code for TallyGenerator
January 8, 2007 at 8:11 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply