SQL Server Performance Forum – Threads Archive
Strange Performance Issues with cursors
Hi, I’m on an assigment of performance tuning a OLTP database of 8 million records.this perticular query
DECLARE cursor1 CURSOR
FOR
SELECT
[FKProfileId],
[FKStudentId],
[LastName] + ‘,’ + [FirstName] AS Stud
FROM[StudProf]
LEFT OUTER JOIN [StudVsProf]
ON[FKStudentId] = [PKStudentId]
ANDFKDistrictId = 21
INNER JOIN @Table
ON[FKProfileId] = [PKProfileId]
WHEREIsDeactivated = 0
ORDER BY [PKProfileId], [FKStudentId]
is tuned for better performance by avoiding scans on the indexes.
case-1: on local machine
Its works fine and takes less time to execute when tested on local m/c.
case-2:
But when executed on the same database on different machine with tables having the same indexes as the local m/c database, it takes longer time to execute. When saw the execution plan for both the cases, case-1 shows QO seeking the indexes. in case-2 show QO scanning indexes. But when only the select statement is executed in case-2 the QO seeks the indexes and with the declare cursor statement, the QO scanns the index. This the first time i’m comming across such situation. Does any one know that is the issue here? Please its urgent!!, so get back ASAP. Thanks in advance
vinoj
The difference perhaps being that the "local machine" is a development server, whereas the "different machine" is the production server? Silly suggestion perhaps, but couldn’t it be that one machine is just a lot busier than the other? Cursors are never good for performance – set-based is usually the way to go (although there are certain things that can only be done using a cursor). What kind of numbers of records are there in your @table variable? And does it have a primary key defined?
Hi Adriaan, @table has only 11 records. Adriaan, as you say cursors are bad for performance. But the task need to use cursor since its on induvidual record. Set based logic will not work in this case. And both the m/c are on the development side. But i don’t get why? when the SELECT stmt alone is seen for the exec. plan the QO seeks for the data. and when given along with the DECLARE CURSOR stmt QO shows using scan. And different scenario of seek and scans are for the permanent table object not for the temp table @table.
>>But the task need to use cursor since its on induvidual record Can you explain the logic? Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, the temp table consists of the parents and their children details. One parent can have many child
The cursor is used to loop through the temporary table to create a CSV for Student Names of each parent in the group. vinoj
Refer this
http://www.nigelrivett.net/RetrieveTreeHierarchy.html Madhivanan Failing to plan is Planning to fail
]]>