Results 1 to 12 of 12

Thread: DB Performance

  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Why do you use a cursor ? It's not performant for this type of query...

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Wink

    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."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Dec 2003
    Posts
    8
    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

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  6. #6
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Oh, and add

    WHERE tbl_Employee.IsActive=1
    -bpd

  8. #8
    Join Date
    Dec 2003
    Posts
    8
    usp_PopulateCacheTable_VarPay3 is going to return more than one value...
    So, I can't use function

  9. #9
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    You can have it return a result set (TABLE)
    -bpd

  10. #10
    Join Date
    Dec 2003
    Posts
    8
    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)

  11. #11
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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))
    -bpd

  12. #12
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    and the WHERE statement again!!! DOH!
    -bpd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •