Home » SQL & PL/SQL » SQL & PL/SQL » Performance Problem
Performance Problem [message #241676] Wed, 30 May 2007 06:53 Go to next message
aadebayo
Messages: 35
Registered: August 2005
Member
Hello

I am having performance problems when I populate a table with more than 2 records.

Here is the stored procedure

PROCEDURE XX_BK_SP_CREATE_FUTURE_SLOTS (p_CalendarID NUMBER, p_FutureDate DATE)
    IS
    -- declare local variables

    ld_LastCreationDate DATE;
    ln_day NUMBER;
    ln_totaldays NUMBER;
    ln_ignoredate NUMBER;
    ld_thisdate DATE;
    ln_cTemplateID NUMBER;
    ln_cCalendarID NUMBER;
    lt_cExternalCode XX_BK_SLOT.SLOTCODE%TYPE;
    lt_cDescription XX_BK_SLOT.DESCRIPTION%TYPE;
    ln_cCapacity NUMBER;
    ld_cDate DATE;
    ld_cStartTime DATE;
    ld_cEndtime DATE;
    ln_cTypeID NUMBER;
    ln_TypeID NUMBER;
    ln_SlotID NUMBER;
    ln_DayOfWeek NUMBER;
 
     -- declare local cursor
    CURSOR  c_TimeEntry_Cursor (ln_DayOfWeek in NUMBER)
    IS
    SELECT  TemplateID,CalendarID,SlotCode,Description,Capacity,ld_thisdate,StartTime,Endtime 
    FROM    XX_BK_SLOT_TEMPLATE
    WHERE   CalendarID = p_CalendarID 
    AND     DayOfWeek = ln_DayOfWeek; 

    BEGIN
        SELECT LastCreationDate INTO ld_LastCreationDate 
        FROM XX_BK_CALENDAR
        WHERE CalendarID = P_CalendarID;

    
        IF (ld_LastCreationDate < SYSDATE)THEN
            ld_LastCreationDate := to_date(SYSDATE);
        END IF; 

         ln_totaldays := (p_FutureDate - ld_LastCreationDate);
         ln_day := 1;
   
              
   
        WHILE (ln_day <= ln_totaldays)
        
        
        LOOP
        
   
             ln_DayOfWeek := to_char(ld_thisdate,'d');
             
             
	       
SELECT count(nonworkingdate) INTO ln_ignoredate              
FROM XX_BK_NON_WORKING_DATE
WHERE CalendarID = p_CalendarID AND (nonworkingdate) = ld_thisdate; 
            -- ignore bank holidays
  IF (ln_ignoredate = 0) THEN
  -- create a cursor to step through template records

 OPEN c_TimeEntry_Cursor(ln_DayOfWeek);
		
LOOP
               	
FETCH c_TimeEntry_Cursor INTO ln_cTemplateID, ln_cCalendarID, lt_cExternalCode, lt_cDescription, ln_cCapacity, ld_cDate, ld_cStartTime, ld_cEndtime;
);
                -- get first cursor line
                
          EXIT WHEN c_TimeEntry_Cursor%notfound; 
         
 -- create XX_BK_SLOT recods from XX_BK_SLOT_TEMPLATE
INSERT INTO XX_BK_SLOT (SLOTID,CalendarID,SlotCode,Description,Capacity,SlotDate,StartTime,Endtime)
                VALUES (SLOT_SEQ.NEXTVAL, ln_cCalendarID,lt_cExternalCode,lt_cDescription,ln_cCapacity,ld_thisdate,to_char(ld_cStartTime,'HH24:MI:SS'),to_char(ld_cEndtime,'HH24:MI:SS'));
                COMMIT; 
 
		       
                                SELECT max(SLOTID) 
                                INTO   ln_SlotID
                                FROM   xx_bk_slot;
         
              

-- create XX_BK_ACTUAL_SLOT from XX_BK_POSSIBLE_SLOT
     insert into XX_BK_ACTUAL_SLOT (SlotID, TypeID)
      select ln_SlotID, TypeID from XX_BK_POSSIBLE_SLOT 
      where TemplateID = ln_cTemplateID;
       commit;

		END LOOP; 

		CLOSE c_TimeEntry_Cursor;

             ln_day := ln_day + 1;
            END IF;
   
        END LOOP;
   
        -- update LastCreationDate
        UPDATE XX_BK_CALENDAR
        SET LastCreationDate = p_FutureDate
        WHERE CalendarID = p_CalendarID;
        
        -- archive slots
        XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_ARCHIVE_SLOTS(p_CalendarID);

        -- archive UpdateLog
        XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_ARCHIVE_UPDATE_LOG;
    COMMIT;

    END XX_BK_SP_CREATE_FUTURE_SLOTS; 


the performance becomes very bad when the records of the this part of the
SELECT count(nonworkingdate) INTO ln_ignoredate              
FROM XX_BK_NON_WORKING_DATE
WHERE CalendarID = p_CalendarID AND (nonworkingdate) = ld_thisdate; 
procedure is more than 3. Please can someone help me.

Ade

[Updated on: Wed, 30 May 2007 06:55]

Report message to a moderator

Re: Performance Problem [message #241700 is a reply to message #241676] Wed, 30 May 2007 07:59 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Run it with trace and post TKPROF report (with explain).

2. Another couple of questions:
2.1. why do you need :
ld_LastCreationDate := to_date(SYSDATE);

both variables are dates anyway.
2.2. where ld_thisdate variable gets it's value assigned before loop starts?

Michael
Re: Performance Problem [message #241707 is a reply to message #241676] Wed, 30 May 2007 08:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't do a count if you only want to know if any record is present.
Re: Performance Problem [message #241710 is a reply to message #241707] Wed, 30 May 2007 08:17 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
I have removed the to_date part of

ld_LastCreationDate := to_date(SYSDATE);



Don't do a count if you only want to know if any record is present.

Also if I do not use a count, what should I use instead?
Re: Performance Problem [message #241718 is a reply to message #241676] Wed, 30 May 2007 08:35 Go to previous message
aadebayo
Messages: 35
Registered: August 2005
Member
Thanks Frank and Michael, I have found a solution. What I did is that I created a cursor instead of the count, and used the cursor to loop through the records that I needed. Here is the final Code

 PROCEDURE XX_BK_SP_CREATE_FUTURE_SLOTS (p_CalendarID NUMBER, p_FutureDate DATE)
    IS
    -- declare local variables

    ld_LastCreationDate DATE;
    ln_day NUMBER;
    ln_totaldays NUMBER;
    ln_ignoredate NUMBER;
    ld_thisdate DATE;
    ln_cTemplateID NUMBER;
    ln_cCalendarID NUMBER;
    lt_cExternalCode XX_BK_SLOT.SLOTCODE%TYPE;
    lt_cDescription XX_BK_SLOT.DESCRIPTION%TYPE;
    ln_cCapacity NUMBER;
    ld_cDate DATE;
    ld_cStartTime DATE;
    ld_cEndtime DATE;
    ln_cTypeID NUMBER;
    ln_TypeID NUMBER;
    ln_SlotID NUMBER;
    ln_DayOfWeek NUMBER;
 
     -- declare local cursor
    CURSOR  c_TimeEntry_Cursor (ln_DayOfWeek in NUMBER)
    IS
    SELECT  TemplateID,CalendarID,SlotCode,Description,Capacity,ld_thisdate,StartTime,Endtime 
    FROM    XX_BK_SLOT_TEMPLATE
    WHERE   CalendarID = p_CalendarID 
    AND     DayOfWeek = ln_DayOfWeek; 

    CURSOR c_non_work_date_cursor (p_CalendarID NUMBER, ld_thisdate DATE) IS
    SELECT * 
    FROM XX_BK_NON_WORKING_DATE
    WHERE CalendarID = p_CalendarID 
    AND  nonworkingdate != ld_thisdate;
    
    BEGIN
        SELECT LastCreationDate INTO ld_LastCreationDate 
        FROM XX_BK_CALENDAR
        WHERE CalendarID = P_CalendarID;

    --    dbms_output.put_line('Conversion is here' || ld_thisdate );
        
      --  dbms_output.put_line('Just Checking the contents before loop' ||ln_cCalendarID||ln_cTemplateID );
    
        IF (ld_LastCreationDate < SYSDATE)THEN
            ld_LastCreationDate := SYSDATE;
        END IF; 

         ln_totaldays := (p_FutureDate - ld_LastCreationDate);
         ln_day := 1;
     --    dbms_output.put_line('Just Checking the contents before while ' ||ln_cCalendarID||ln_cTemplateID );
   
              
              dbms_output.put_line('ln_day is ' || ln_day);
              dbms_output.put_line('ln_totaldays is ' || ln_totaldays);
              dbms_output.put_line('p_futuredate is ' || p_futuredate);
              dbms_output.put_line('lastcreationdate is ' || ld_LastCreationDate);
   
        WHILE (ln_day <= ln_totaldays)
        
        
        LOOP
        
           -- dbms_output.put_line('----------');
             ld_thisdate := (ln_day + ld_LastCreationDate);
 
          --   dbms_output.put_line('ln_day is ' || ln_day);
 
         --    dbms_output.put_line('ln_totaldays is ' || ln_totaldays);
   
             ln_DayOfWeek := to_char(ld_thisdate,'d');
             
             
    --     dbms_output.put_line('in day of week is ' ||ln_DayOfWeek );
	       
            SELECT count(nonworkingdate) INTO ln_ignoredate FROM XX_BK_NON_WORKING_DATE
            WHERE CalendarID = p_CalendarID AND (nonworkingdate) = ld_thisdate;
       --   dbms_output.put_line('Ignore date value ' ||ln_ignoredate );
            -- ignore bank holidays
         FOR non_working_rec in c_non_work_date_cursor(P_CalendarID
                          ,ld_thisdate)
            LOOP
          --  IF (ln_ignoredate = 0) THEN
            	-- create a cursor to step through template records

		OPEN c_TimeEntry_Cursor(ln_DayOfWeek);
		
                LOOP
         --       dbms_output.put_line('Day of the week is  = ' || ln_DayOfWeek);
     
               	
                FETCH c_TimeEntry_Cursor INTO ln_cTemplateID, ln_cCalendarID, lt_cExternalCode, lt_cDescription, ln_cCapacity, ld_cDate, ld_cStartTime, ld_cEndtime;
               --   dbms_output.put_line('Just Checking the contents of cursor before exit' ||ln_cCalendarID||ln_cTemplateID );
                -- get first cursor line
           --      dbms_output.put_line('Just Checking the contents after curosr Ademola');
                
                EXIT WHEN c_TimeEntry_Cursor%notfound; 
         
	 --       dbms_output.put_line('before Inserting records into SLOT and Slot is ' ||ln_SlotID );
			-- create XX_BK_SLOT recods from XX_BK_SLOT_TEMPLATE
   	        INSERT INTO XX_BK_SLOT (SLOTID,CalendarID,SlotCode,Description,Capacity,SlotDate,StartTime,Endtime)
                VALUES (SLOT_SEQ.NEXTVAL, ln_cCalendarID,lt_cExternalCode,lt_cDescription,ln_cCapacity,ld_thisdate,to_char(ld_cStartTime,'HH24:MI:SS'),to_char(ld_cEndtime,'HH24:MI:SS'));
                COMMIT; 
 
            --     dbms_output.put_line('insert into xx_bk_slot values ' || ln_cCalendarID || lt_cExternalCode || lt_cDescription || ln_cCapacity || ld_thisdate || to_char(ld_cStartTime,'HH24:MI:SS') || to_char(ld_cEndtime,'HH24:MI:SS'));
	
              --  dbms_output.put_line('check the time values' ||to_char(ld_cStartTime,'HH24:MI:SS'));
		       
                                SELECT max(SLOTID) 
                                INTO   ln_SlotID
                                FROM   xx_bk_slot;
         
              
           --             dbms_output.put_line('Slot is '||ln_SlotID);
           --             dbms_output.put_line('Template is '||ln_cTemplateID);
           --             dbms_output.put_line('Type is '||ln_TypeID);

			-- create XX_BK_ACTUAL_SLOT from XX_BK_POSSIBLE_SLOT
			insert into XX_BK_ACTUAL_SLOT (SlotID, TypeID)
			select ln_SlotID, TypeID from XX_BK_POSSIBLE_SLOT 
			where TemplateID = ln_cTemplateID;
                        commit;

		END LOOP; 

		CLOSE c_TimeEntry_Cursor;

             ln_day := ln_day + 1;
       END LOOP;
          --  END IF;
      --  dbms_output.put_line('Just Checking the contents before leaving loop ' ||ln_cCalendarID||ln_cTemplateID );
   
        END LOOP;
      --  dbms_output.put_line('Just Checking the contents before update ' ||ln_cCalendarID||ln_cTemplateID );
   
        -- update LastCreationDate
        UPDATE XX_BK_CALENDAR
        SET LastCreationDate = p_FutureDate
        WHERE CalendarID = p_CalendarID;
        
        dbms_output.put_line('Just Checking the contents after loop ' ||ln_cCalendarID||ln_cTemplateID );
   
        -- archive slots
        XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_ARCHIVE_SLOTS(p_CalendarID);

        -- archive UpdateLog
        XX_LGS_BK_CALENDAR_ADMIN_PKG.XX_BK_SP_ARCHIVE_UPDATE_LOG;
    COMMIT;

    END XX_BK_SP_CREATE_FUTURE_SLOTS; 
Previous Topic: Find the spaces in a word
Next Topic: Trigger problem
Goto Forum:
  


Current Time: Sun Dec 04 06:22:24 CST 2016

Total time taken to generate the page: 0.07894 seconds