DB Performance

I ave the following Code in my Stored procedure.

Declare Cursor for table A
WHILE @.@.FETCH_STATUS = 0
Get values from other function based on some business logic.
INSERT Into another table B
(or)
UPDATE to another table B
END

I have to insert/update values to table B, one by one row. So, it is taking more time.
Is there any way to collect the values into a temporary storage and Insert/update or Move the values to table B.Why do you use a cursor ? It's not performant for this type of query...|||While you can certainly do that, in some instances (such as those which don't involve the tables possibly being updated in real-time), you might be able to accomplish a similar effect by wrapping the request in a transaction having an appropriate isolation-level. What you're trying to do here is to delay disk-writes and make more efficient use of in-memory buffers.

You can also sometimes achieve good results by sorting the records that are being updated, so that they are presented to the system (say) in primary-key order. Once again, we're trying to improve the chances that the next record we're going to be updating is nearby to the last one, and thus "still available in our in-memory buffers."

Sorting is an "unexpectedly fast and efficient" thing for a computer to do. "Random-access anything" is inherently slower due to the mechanical delays of moving the read/write head about.
"I/O!... I/O!... it's off to disk we go!" ;)
"Seek, and ye shall wait." :D|||Originally posted by fadace
Why do you use a cursor ? It's not performant for this type of query...

How can avoid Cursor. My Exact query is like this..
DECLARE cur_populate_CachePrograms CURSOR FOR
SELECT e.EmployeeCode,
FROM tbl_Employee e
WHERE e.IsActive = 1
ORDER BY e.EmployeeCode

OPEN cur_populate_CachePrograms

FETCH NEXT FROM cur_populate_CachePrograms INTO @.EmployeeCode

WHILE @.@.FETCH_STATUS = 0
BEGIN
exec usp_PopulateCacheTable_VarPay3 @.EmployeeCode, @.IsVarPay3NeedComments OUTPUT

IF EXISTS(select EmployeeCode from tbl_Program where EmployeeCode = @.EmployeeCode)
BEGIN
UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = @.IsVarPay3NeedComments,
WHERE @.EmployeeCode = EmployeeCode
END
ELSE
BEGIN
INSERT INTO tbl_Program
(EmployeeCode, IsVarPay3NeedComments) VALUES (@.EmployeeCode,@.IsVarPay3NeedComments)
END

FETCH NEXT FROM cur_populate_CachePrograms INTO @.EmployeeCode

END
CLOSE cur_populate_CachePrograms
DEALLOCATE cur_populate_CachePrograms|||Depending on what is going on in usp_PopulateCacheTable_VarPay3, you might be able to change it to a function which returns @.IsVarPay3NeedComments. You can then use the function in-line in you SQL statement.

Something like, and this is sketchy guess-work with what info I have...

UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode)
FROM tbl_CacheEmployeeToProgram
INNER JOIN tbl_Employee

SELECT e.EmployeeCode,
FROM tbl_Employee e
WHERE e.IsActive = 1
ORDER BY e.EmployeeCode|||Sorry, hit the Enter Key accidently. Let's try that again...

Depending on what is going on in usp_PopulateCacheTable_VarPay3, you might be able to change it to a function which returns @.IsVarPay3NeedComments. You can then use the function in-line in you SQL statement.

Something like, and this is sketchy guess-work with what info I have...

UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode)
FROM tbl_CacheEmployeeToProgram
INNER JOIN tbl_Employee
ON tbl_CacheEmployeeToProgram.EmployeeCode = tbl_Employee.EmployeeCode
INNER JOIN tbl_Program
ON tbl_Employee.EmployeeCode = tbl_Program.EmployeeCode|||Oh, and add

WHERE tbl_Employee.IsActive=1|||usp_PopulateCacheTable_VarPay3 is going to return more than one value...
So, I can't use function|||You can have it return a result set (TABLE)|||But if function uFN_PopulateCacheTable_VarPay3 returns TABLE variable which contains more than one variable, how do I set to multiple columns.

UPDATE tbl_program
SET IsVarPay3NeedComments = uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode)|||Use it like a table in your select statement:

SELECT IsVarPay3NeedComments FROM dbo.uFN_PopulateCacheTable_VarPay3(tbl_Employee.Em ployeeCode)

So your statement would be something like:

UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = mytable.IsVarPay3NeedComments
FROM uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode) mytable
INNER JOIN tbl_CacheEmployeeToProgram
ON mytable.EmployeeCode = tbl_CacheEmployeeToProgram.EmployeeCode
INNER JOIN tbl_Employee
ON tbl_CacheEmployeeToProgram.EmployeeCode = tbl_Employee.EmployeeCode
INNER JOIN tbl_Program
ON tbl_Employee.EmployeeCode = tbl_Program.EmployeeCode

In order for that to work (in my statement anyway), you should return a table that is something like:
RETURNS TABLE (EmployeeCode INT, IsVarPay3NeedComments VARCHAR(100))|||and the WHERE statement again!!! DOH!