Re: What am I doing wrong here?

From	DFS <nospam@dfs.com>
Newsgroups	comp.lang.c
Subject	Re: What am I doing wrong here?
Date	2017-05-15 20:18 -0400
Organization	A noiseless patient Spider
Message-ID	<ofdga6$s8b$3@dont-email.me> (permalink)
References	<of62cn$4ji$3@dont-email.me>

Show all headers | View raw

On 5/13/2017 12:39 AM, DFS wrote:

> Just trying to get some random chars and ints:

Thanks for your help guys.

That question about sprintf() was related to this program I wrote 
yesterday, which does performance tests on the PostgreSQL dbms.

usage: progname baserows exponent
ex:    bigdata 1000 10

It will add baserows of random data, then copy it exponent times
Ex: 1000 10 means you end up with 1000*(2^10) = 1024000 rows

------------------------------------------------------------------------------
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <C:\Program Files\PostgreSQL\9.6\include\libpq-fe.h>
#include <Windows.h>

// compile on Windows:
// copy libpq.dll to source directory
// tcc bigdata.c libpq.dll -o bigdata.exe

LARGE_INTEGER 
frequency,startMaster,startDrop,startRows,startDistinct,start,end;

double elapsedtime(LARGE_INTEGER startingtimer)
{
   QueryPerformanceCounter(&end);
   return (end.QuadPart - startingtimer.QuadPart) / 
(double)frequency.QuadPart;
}

int main(int argc, char *argv[])
{

   QueryPerformanceFrequency(&frequency);
   QueryPerformanceCounter(&startMaster);

   //vars
   int i=0,cnt1=0,cnt2=0;
   PGresult *rs;
   char cSQL[200];
   int colcount = 4;
   char *columns[] = {"HIREDATE","REG","SALARY","JOB"};
   char *display[] = {"Hire Dates","Regs","Salaries","Jobs"};

   //random data sources
   char *regs[]  = {"Al","Bill","Chris","Diana","Evan"};
   int  salary[] = {100,150,200,250,300};
   char *jobs[]  = {"programmer","DBA","analyst","architect","contractor"};

   //char *regs[]  = {"Al","Bill","Chris","Diana","Evan"};
   //int  salary[] = {100,150,200,250,300};
   //char *jobs[]  = 
{"programmer","DBA","analyst","architect","contractor"};

   //db connect, drop table, recreate table
   QueryPerformanceCounter(&startDrop);
   char *conninfo = "host='localhost' dbname='COLATEST' user='COLA' 
password='evermore'";
   PGconn *conn = PQconnectdb(conninfo);
   PQexec(conn, "BEGIN TRANSACTION;");
   PQexec(conn, "DROP TABLE IF EXISTS BIGDATA;");
   PQexec(conn, "CREATE TABLE BIGDATA (ROWSID SERIAL PRIMARY KEY, 
HIREDATE DATE NOT NULL, REG VARCHAR(15) NOT NULL,SALARY INTEGER NOT 
NULL,JOB VARCHAR(20) NOT NULL);");
   PQexec(conn, "COMMIT;");
   printf("%.2fs to drop and recreate table\n",elapsedtime(startDrop));

   //show counts of random source data
   int regcount    = sizeof(regs)   / sizeof(regs[0]);
   int salarycount = sizeof(salary) / sizeof(salary[0]);
   int jobcount    = sizeof(jobs)   / sizeof(jobs[0]);
   printf("Random Data: 365 Hire Dates, %d Regs, %d Salaries, %d 
Jobs\n",regcount,salarycount,jobcount);

   //inputs	
   int baserows = strtol(argv[1], NULL, 10);
   int exponent = strtol(argv[2], NULL, 10);

   QueryPerformanceCounter(&startRows);

   //add base rows
   QueryPerformanceCounter(&start);
   struct tm sdt = {0,0,0,1,0,(2017-1900)}; // starting date of 1/1/2017
   time_t sdts = mktime(&sdt);  //convert start date/time to seconds 
since epoch
   PQexec(conn, "BEGIN TRANSACTION;");
   srand(time(NULL));
   for(i=0; i<baserows; i++)
   {
     time_t randSecs = sdts + (86400 * (rand() % 365));	
	char* randDate  = asctime(gmtime(&randSecs));
	char* randReg   = regs  [rand() % regcount];
	int randSalary  = salary[rand() % salarycount];
	char* randJob   = jobs  [rand() % jobcount];	
	sprintf(cSQL,"INSERT INTO BIGDATA (HIREDATE,REG,SALARY,JOB) 
VALUES('%s','%s',%d,'%s');",randDate,randReg,randSalary,randJob);
	PQexec(conn, cSQL);
   }
   PQexec(conn, "COMMIT;");
   rs = PQexec(conn, "SELECT COUNT(ROWSID) FROM BIGDATA;");
   cnt1 = strtol(PQgetvalue(rs,0,0),NULL,10);
   printf("added %d rows in %.2fs (total = %d rows)\n",cnt1, 
elapsedtime(start),cnt1);

   //copy all existing rows: row count doubles each loop
   PQexec(conn, "BEGIN TRANSACTION;");
   for(i=0; i < exponent; i++)
   {
	QueryPerformanceCounter(&start);
	PQexec(conn, "INSERT INTO BIGDATA (HIREDATE,REG,SALARY,JOB) SELECT 
HIREDATE,REG,SALARY,JOB FROM BIGDATA;");
	rs = PQexec(conn, "SELECT COUNT(ROWSID) FROM BIGDATA;");
	cnt2 = strtol(PQgetvalue(rs,0,0),NULL,10);
	printf("%d. added %d rows in %.2fs (total = %d rows)\n",i+1,cnt2-cnt1, 
elapsedtime(start),cnt2);
  	cnt1 = cnt2;
   }	
   PQexec(conn, "COMMIT;");
   printf("%.2fs to add all data\n",elapsedtime(startRows));

   //create indexes
   QueryPerformanceCounter(&start);
   PQexec(conn,"CREATE INDEX IDXHIREDATE ON BIGDATA (HIREDATE);");
   PQexec(conn,"CREATE INDEX IDXREG      ON BIGDATA (REG)     ;");
   PQexec(conn,"CREATE INDEX IDXSALARY   ON BIGDATA (SALARY)  ;");
   PQexec(conn,"CREATE INDEX IDXJOB      ON BIGDATA (JOB)     ;");
   printf("%.2fs to create indexes\n",elapsedtime(start));

   //count distinct values in each column
   QueryPerformanceCounter(&start);
   printf("Random data contains:");
   for(i = 0; i < colcount; i++)
   {
	sprintf(cSQL,"SELECT COUNT(%s) FROM (SELECT DISTINCT %s FROM BIGDATA) 
AS CNT;",columns[i],columns[i]);
	rs = PQexec(conn,cSQL);
	printf(" %s %s",PQgetvalue(rs,0,0),display[i]);
   }	
   printf("\n%.2fs to count distinct\n",elapsedtime(start));

   //get db size
   rs = PQexec(conn, "SELECT 
pg_size_pretty(pg_database_size(current_database())) AS dbsize");
   printf("db size is %s\n",PQgetvalue(rs,0,0));

   //close
   PQclear(rs);
   PQfinish(conn);

   double elapsed = elapsedtime(startMaster);
   printf("Finished: %.2fs total (%.2fm)",elapsed,elapsed/60);

   return 0;
}
------------------------------------------------------------------------------

If you use a small exponent, sometimes the dbsize doesn't register - I 
guess the OS hasn't caught up to the code/dbms.

Back to comp.lang.c | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-13 00:39 -0400
  Re: What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-13 01:03 -0400
  Re: What am I doing wrong here? jt@toerring.de (Jens Thoms Toerring) - 2017-05-13 06:55 +0000
    Re: What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-13 08:59 -0400
      Re: What am I doing wrong here? bartc <bc@freeuk.com> - 2017-05-13 14:15 +0100
      Re: What am I doing wrong here? Gareth Owen <gwowen@gmail.com> - 2017-05-13 14:19 +0100
        Re: What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-13 11:06 -0400
          Re: What am I doing wrong here? Manfred <noname@invalid.add> - 2017-05-13 17:38 +0200
            Re: What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-13 13:38 -0400
              Re: What am I doing wrong here? bartc <bc@freeuk.com> - 2017-05-13 19:03 +0100
          Re: What am I doing wrong here? bartc <bc@freeuk.com> - 2017-05-13 16:43 +0100
      Re: What am I doing wrong here? Barry Schwarz <schwarzb@dqel.com> - 2017-05-13 07:44 -0700
        Re: What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-13 11:25 -0400
          Re: What am I doing wrong here? Ben Bacarisse <ben.usenet@bsb.me.uk> - 2017-05-13 19:23 +0100
    Re: What am I doing wrong here? Keith Thompson <kst-u@mib.org> - 2017-05-13 13:58 -0700
      Re: What am I doing wrong here? jt@toerring.de (Jens Thoms Toerring) - 2017-05-13 23:43 +0000
        Re: What am I doing wrong here? Tim Rentsch <txr@alumni.caltech.edu> - 2017-05-14 13:43 -0700
  Re: What am I doing wrong here? DFS <nospam@dfs.com> - 2017-05-15 20:18 -0400

csiph-web