February 9, 2009 at 5:19 pm
Hi
In one of my queries , i am fetching out the data from one of my partioned table ( 9 partions) . I am using UNION for select data from all the of partions . The Query structure is like :
SELECT
EC as ECR,
COUNT(EC) as O,
qt = @qtyt,
qtf = @qtf
FROM ABC1 WITH(NOLOCK)
WHERE RID in (SELECT RID FROM #result_ids WHERE passed = 0) AND test_passed = 0
GROUP BY EC
UNION
SELECT
EC as ECR,
COUNT(EC) as O,
qt = @qtyt,
qtf = @qtf
FROM ABC2 WITH(NOLOCK)
WHERE RID in (SELECT RID FROM #result_ids WHERE passed = 0) AND test_passed = 0
GROUP BY EC
Above "ABC" is the partioned table . Now even after i am using the NOLOCK , still in thebackgroud locks are occuring on this table and query is taking lot of time to execute.
Please advise what could be the alternate to avoid locking here.
Nikhil
February 9, 2009 at 6:33 pm
I believe that the NOLOCK hint still requires at least a schema stability lock on the target table. The real benefit of using a NOLOCK hint is that you don't have to wait for data modifications to be committed to the table before you read from it, and the lock that you do acquire is much lighter and uses fewer resources.
The performance hit might be coming from the use of the IN operator since it uses an iterative process on each of the items in the list. Try using an INNER JOIN to the temp table instead.
UNION ALL is much faster because it does not guarantee uniqueness of the resulting records. If you want to include all of the results from each select statement, or if all of the results would be unique already, then you might try the UNION ALL operator. If not, you might want to try to use the UNION ALL to put the data into a temp table, and then do a SELECT DISTINCT from it and see if that is faster or not.
I would recommend trying one of these things at a time and checking that the result set is not affected by the change.
February 10, 2009 at 12:35 pm
Can you please help remodifying the query using any of the above mentioned recommendation.
It would be great help !!!
February 11, 2009 at 2:43 pm
Analyze the execution plan of the query and see if indexing can be of help here.
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply