Stored Procedure Execution Time


SYSDATE

SYSDATE returns the number of seconds after midnight. Thus, the maximum value that SYSDATE can return is 86699 seconds.
SYSDATE regresa el número de segundos después de media noche. Así el valor máximo que SYSDATE puede regresar es 86699 segundos.

Problem 1
Create a Wintempla dialog application called FillNames to insert 10,000 employees in the employee table of the motorola database.
Cree una aplicación de Wintempla de diálogo FillNames para insertar 10,000 empleados en la tabla employee de la base de datos de motorola.

FillNames.h
#pragma once //______________________________________ FillNames.h
#include "Resource.h"
class FillNames: public Win::Dialog
{
public:
     FillNames()
     {
     }
     ~FillNames()
     {
     }
     wstring CreateRandomName();
protected:
     ...
};


FillNames.cpp
...

void FillNames::Window_Open(Win::Event& e)
{
     ::srand(::GetTickCount());
     const int numNames = 10000;
     Sql::SqlConnection conn;
     wstring cmd;
     int emp_id;
     int rows = 0;
     int phone = 0;
     try
     {
          //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase
          conn.OpenSession(hWnd, CONNECTION_STRING);
          //___________________________________________________________ DELETE
          conn.ExecuteNonQuery(L"DELETE FROM emp_skill");
          conn.ExecuteNonQuery(L"DELETE FROM employee");
          //___________________________________________________________ INSERT
          for (emp_id = 1; emp_id < numNames; emp_id++, phone++)
          {
               Sys::Format(cmd,
                    L"INSERT INTO employee (emp_id, last_name, first_name, phone, dept_id) VALUES(%d, '%s', '%s', '345-678-%04d', 60)",
                    emp_id, CreateRandomName().c_str(), CreateRandomName().c_str(), phone);
               rows = conn.ExecuteNonQuery(cmd);
               if (rows != 1)
               {
                    this->MessageBox(Sys::Convert::ToString(rows), L"Error: number of inserted rows", MB_OK | MB_ICONERROR);
               }
          }
          Sys::Format(cmd,
               L"INSERT INTO employee (emp_id, last_name, first_name, phone, dept_id) VALUES(%d, '%s', '%s', '345-678-%04d', 60)",
               emp_id, L"Huerta", L"Alix", phone);
          rows = conn.ExecuteNonQuery(cmd);
          if (rows != 1)
          {
               this->MessageBox(Sys::Convert::ToString(rows), L"Error: number of inserted rows", MB_OK | MB_ICONERROR);
          }
     }
     catch (Sql::SqlException e)
     {
          this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR);
     }
}

wstring FillNames::CreateRandomName()
{
     const int len = 2 + ( 6*rand() )/ RAND_MAX;
     const int numChar = 'Z' - 'A' +1;
     wstring randomName;
     randomName.resize(len);
     int randomNumber;
     for (int i = 0; i < len; i++)
     {
          randomNumber = (numChar * rand()) / RAND_MAX;
          if (i == 0)
          {
               randomName[i] = 'A' + randomNumber;
          }
          else
          {
               randomName[i] = 'a' + randomNumber;
          }
     }
     return randomName;
}


Problem 2
motorola > Modify the motorola.sql script to add p_emp stored procedure. Discuss and execute the stored procedure using: (a) Oracle, (b) Microsoft SQL Server.
Modifique el script motorola.sql para agregar el procedimiento almacenado p_emp. Discuta y ejecute el procedimiento almacenado usando: (a) Oracle, (b) Microsoft SQL Server.

motorola.sql
-- _______________________________________________________ Oracle
CREATE OR REPLACE PROCEDURE p_emp
IS
     p_last_name VARCHAR2(20);
     p_count INTEGER:=0;
     MAX_VALUE CONSTANT INTEGER:=10000;
BEGIN
     FOR p_count IN 1..MAX_VALUE
     LOOP
          SELECT last_name INTO p_last_name
           FROM employee
          WHERE last_name='Huerta';
     END LOOP;
END p_emp;
/
-- ________________________________________________________ Microsoft SQL Server
CREATE PROCEDURE p_emp
AS
     DECLARE @p_last_name NVARCHAR(25);
     DECLARE @p_count INT;
     DECLARE @MAX_VALUE INT;
     -- _________________________
     SET @p_count = 0;
     SET @MAX_VALUE = 10000;
BEGIN
     WHILE (@p_count < @MAX_VALUE)
     BEGIN
          SELECT @p_last_name = last_name
          FROM employee
          WHERE apellido='HUERTA';
     END
END
GO


Problem 3
motorola > Create the mot_emp.sql script in Oracle as shown below. Discuss and execute the stored procedure.
Cree el script mot_emp.sql en Oracle como se muestra debajo. Discuta y ejecute el procedimiento almacenado.

mot_emp.sql
-- DROP INDEX indx_employee_last_name;
TIMING START;
EXECUTE p_emp;
TIMING STOP;

CREATE INDEX indx_employee_last_name
ON employee(last_name);

TIMING START;
EXECUTE p_emp;
TIMING STOP;

MSDOS: cmd.exe
SQL> @C:\mot_emp.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32

Index created.


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18


Tip
You cannot use SYSDATE to measure runtime if the procedure runtime crosses over midnight.
No es posible usar SYSDATE para medir el tiempo de ejecución si el procedimiento corre a través de media noche.

Problem 4
motorola > Modify the motorola.sql script to add p_motem stored procedure. Discuss and execute the stored procedure. When done, do not forget to restore the database running the original SQL script of the database. Using: (a) Oracle, (b) Microsoft SQL Server.
Modifique el script motorola.sql para agregar el procedimiento almacenado p_ motem. Discuta y ejecute el procedimiento almacenado. Cuando termine, no se olvide de restaurar la base de datos ejecutando el script de SQL de la base de datos. Usando: (a) Oracle, (b) Microsoft SQL Server.

motorola.sql (Oracle)
CREATE OR REPLACE PROCEDURE p_motem
IS
     p_last_name VARCHAR2(20);
     p_count INTEGER:=0;
     MAX_VALUE CONSTANT INTEGER:=100000;
     p_start_time CHAR(5):=0;
     p_end_time CHAR(5):=0;
     p_duration NUMBER(10,5):=0;
BEGIN
     -- We execute 100 000 SELECT statements
     p_start_time:=TO_CHAR(SYSDATE, 'SSSSS');

     FOR p_count IN 1..MAX_VALUE
     LOOP
           SELECT last_name INTO p_last_name
          FROM employee
          WHERE last_name='Huerta';
     END LOOP;

     p_end_time:=TO_CHAR(SYSDATE, 'SSSSS');
     p_duration:=TO_NUMBER(p_end_time)-TO_NUMBER(p_start_time);
     dbms_output.put_line('Running time is: ' || p_duration || ' sec');

END p_motem;
/

MSDOS: cmd.exe
SQL> DROP INDEX indx_employee_last_name;

Index dropped.

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE p_motem;
Running time is: 4 sec

PL/SQL procedure successfully completed.

SQL> CREATE INDEX indx_employee_last_name
  2  ON employee(last_name);

Index created.

SQL> EXECUTE p_motem;
Running time is: 2 sec

PL/SQL procedure successfully completed.






© Copyright 2000-2019 Wintempla selo. All Rights Reserved. Sep 05 2019. Home