home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: Product
/
Product.zip
/
DBDEMO.ZIP
/
DEMOFLS.ZIP
/
INDICES.SQC
< prev
next >
Wrap
Text File
|
1991-06-30
|
8KB
|
223 lines
/************************************************************************/
/* DATABASE PERFORMANCE CONCEPTS AND TECHNIQUES DEMONSTRATION PROGRAM */
/* MODULE: INDEX USE */
/* source file: INDICES.SQC */
/************************************************************************/
/************************************************************************/
/* HEADER FILES - USER DEFINED */
/************************************************************************/
#include "db.h"
/************************************************************************/
/* FUNCTION PROTOTYPES */
/************************************************************************/
short far indices(void);
short indx(void);
short noindex(void);
/**************************/
double q1time, q2time;
struct timeb dbeg, dend;
/***************************************************************************/
/* indx query */
/***************************************************************************/
/*************************************************/
/* SQL HOST VARIABLE DECLARATIONS */
/*************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char st_cu_ssn[12];
char st_cu_name[31];
double s_bal;
char c_accnumb[9];
char st_cu_addr1[26];
char st_cu_addr2[26];
char st_cu_zip[6];
EXEC SQL END DECLARE SECTION;
short num_rows;
/************************************************************************/
/* FUNCTION indx() */
/************************************************************************/
short indx(void)
{
q1time = 0.0;
num_rows = 0;
/*** SQL error handling ***/
EXEC SQL WHENEVER SQLERROR GO TO error;
EXEC SQL WHENEVER NOT FOUND GO TO ext;
/****** DECLARE CURSOR *********/
ftime (&dbeg);
EXEC SQL DECLARE Cindx CURSOR FOR
SELECT CU_SSN FROM BMCHUGH.PERFDAT3
WHERE CU_SSN BETWEEN '111-11-1020' AND '111-11-1140';
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
get_error( &sqlca, " declare cursor");
/****** OPEN CURSOR *********/
ftime (&dbeg);
EXEC SQL OPEN Cindx;
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
// get_error( &sqlca, " open cursor");
/****** FETCH ROWS till no more left *********/
while (sqlca.sqlcode == 0) {
ftime (&dbeg);
EXEC SQL FETCH Cindx
INTO :st_cu_ssn;
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
if ((sqlca.sqlcode == 0) ) num_rows++;
}/*end while more rows */
ext:
/****** CLOSE CURSOR *********/
ftime (&dbeg);
EXEC SQL CLOSE Cindx;
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
return(0);
error:
error_sql = sqlca.sqlcode; // copy the sql code into external variable
// to be printed on user screen by dbdemo
return(-1);
}/* end indx */
/************************************************************************/
/* FUNCTION noindex() */
/************************************************************************/
short noindex()
{
/* the same queries executed with no index */
q2time= 0.0;
num_rows = 0;
/*** SQL error handling ***/
EXEC SQL WHENEVER SQLERROR GO TO error;
EXEC SQL WHENEVER NOT FOUND GO TO ext;
/****** DECLARE CURSOR *********/
ftime (&dbeg);
EXEC SQL DECLARE CNOINDEX CURSOR FOR
SELECT * FROM BMCHUGH.PERFDAT3
WHERE CU_SSN BETWEEN '111-11-1020' AND '111-11-1140';
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
get_error( &sqlca, " prepare cursor");
ftime (&dbeg);
EXEC SQL OPEN CNOINDEX;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
get_error( &sqlca, " open cursor");
while (sqlca.sqlcode == 0) {
/****** FETCH ROWS till no more left *********/
ftime (&dbeg);
EXEC SQL FETCH CNOINDEX
INTO :st_cu_ssn;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
if ((sqlca.sqlcode == 0) ) num_rows++;
}/*end while more rows */
ext:
/****** CLOSE CURSOR *********/
ftime (&dbeg);
EXEC SQL CLOSE CNOINDEX;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
/* printf("\nnoindex time = %7.3f", q2time);
*/
return(0);
error:
error_sql = sqlca.sqlcode;// copy the sql code into external variable
// to be printed on user screen by dbdemo
return(-1);
}/* end noindex */
/***************************************************************************/
/***************************************************************************/
/* MAIN PROGRAM */
/* 1. indx */
/* 2. noindex */
/***************************************************************************/
/************************************************************************/
/* FUNCTION indices() */
/* This function calls the functions indx() and noindex(), */
/* num_run times. */
/* gets the timing info from external variables q1time and */
/* q2time and applies the averaging logic, depending upon */
/* user entered variable num_runs. */
/* i.e. if num_runs = 1 then q1avg = q1time and q2avg=q2time */
/* otherwise, it disregards the first value and averages the rest */
/************************************************************************/
/************************************************************************/
short far indices()
{
short i;
q1avg=0.0;
q2avg=0.0;
// SET THE NUMBER OF DEMOS FOR THIS SECTION 1 OR 2.. 2 IS MAX!!!!
Timetext[0].test_num = 2;
// call the functions num_runs time
for (i=1; i<=num_runs; i++)
{
if (indx())
return(3);
if (i!=1)
q1avg =q1avg + q1time;
}/*end for */
if (num_runs==1)
q1avg = q1time;
else
q1avg = q1avg/(num_runs-1);
// FILL UP TIMETEXT STRUCTURE FOR DEMO 1
strcpy(Timetext[0].demoname,"with index ");
Timetext[0].demotime = q1avg;
Timetext[0].rows = num_rows;
// call the functions num_runs time
for (i=1; i<=num_runs; i++)
{
if (noindex())
return(3);
if (i!=1)
q2avg = q2avg+ q2time;
}/*end for */
if (num_runs==1)
q2avg = q2time;
else
q2avg = q2avg/(num_runs-1);
// FILL UP TIMETEXT STRUCTURE FOR DEMO 2
strcpy(Timetext[1].demoname,"no index ");
Timetext[1].demotime = q2avg;
Timetext[1].rows = num_rows;
return(0);
}/* end main */