RE: SQL vs. traditional I/O?

On Fri, 23 Jul 2004, Joe Pluta wrote:

> > From: David Gibbs
> >
> > In the spirit of peer review ... could you post the source code for
> the
> > test programs?
>
> Fill the file with 800,000 records (use a write loop).  Then add keys
> with the following SQL (see, I know when to use SQL! <grin>):

I decided to take your program and make a similar for MySQL in C.  I must
say I'm very surprised with the results!  I don't get anywhere near your
results (for SQL - obviously I can't test RPG on MySQL since it doesn't
exist).  Either you have much more powerful hardware than me, or DB/400
rocks all over MySQL, or I'm an idiot and am doing something wrong.  My
test is only 500 iterations.  My results are:

james@universe:~/progs> ./testsql
Starting select loop at 2004-07-26 12:20:39
Complete at 2004-07-26 12:29:08

Nearly 9 minutes!  The performance remains almost unchanged over several
iterations.  Hardware is 1.5GHz Athlon, 256MB RAM, 1 standard IDE disk.
OS is linux 2.6.3 and MySQL version is 3.23.54.

Throughout the run the mysqld process averaged 99.5% of the CPU usage.  So
it is unlikely that the slow results I got are due to sloppy C code.

I realize that this isn't a C forum, but I felt like this is still on
topic.  I hope if people are upset about me posting C code they will
forgive me.

This is how I created the table:

create table joepluta (
key1 char (10) not null,
key2 decimal(10,0) not null,
data1 text not null,
data2 dec (15,0),
unique (key1, key2));

And here is the C code (setting dodbinit = 1 initializes the database):

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include <string.h>
#include <time.h>

#define SQL_SERVER "localhost"
#define SQL_DB "james"
#define SQL_USER "james"


void filldb (MYSQL mysql);

int
main (int argc, char *argv[])
{
  int i;
  int iterations, seed;
  int randomkey;
  int dodbinit = 0;
  char query[256];
  time_t curtime;
  struct tm *curdate;
  MYSQL mysql;
  MYSQL_RES *res;
  MYSQL_ROW row;


  /*
  if (argc < 2)
    {
      printf ("Usage: testsql <iterations> <seed>\n");
      return (-1);
    }

  iterations = atoi (argv[1]);
  seed = atoi (argv[2]);
  */
  iterations = 500;

  if (!(mysql_connect (&mysql, SQL_SERVER, SQL_USER, NULL)))
    {
      printf ("%s\n", mysql_error (&mysql));
      return (-1);
    }

  if (mysql_select_db (&mysql, SQL_DB))
    {
      printf ("%s\n", mysql_error (&mysql));
      return (-1);
    }

  if (dodbinit)
    {
      filldb (mysql);
    }

  curtime = time (NULL);
  curdate = localtime (&curtime);
  printf ("Starting select loop at %04d-%02d-%02d %02d:%02d:%02d\n",
          curdate->tm_year + 1900, curdate->tm_mon + 1, curdate->tm_mday,
          curdate->tm_hour, curdate->tm_min, curdate->tm_sec);

  for (i = 0; i < iterations; i++)
    {
      randomkey = 1 + (int) (800000.0*rand()/(RAND_MAX+1.0));
      memset (query, 0, sizeof (query));
      sprintf (query, "SELECT * FROM joepluta WHERE key2=%d", randomkey);
      /*
      printf ("%s\n", query);
      */

      if (mysql_query (&mysql, query))
        {
          printf ("%s\n", mysql_error (&mysql));
          return (-1);
        }

      if (!(res = mysql_store_result (&mysql)))
        {
          printf ("%s\n", mysql_error (&mysql));
          return (-1);
        }

      row = mysql_fetch_row (res);
    }

  curtime = time (NULL);
  curdate = localtime (&curtime);
  printf ("Complete at %04d-%02d-%02d %02d:%02d:%02d\n",
          curdate->tm_year + 1900, curdate->tm_mon + 1, curdate->tm_mday,
          curdate->tm_hour, curdate->tm_min, curdate->tm_sec);
  return 0;
}


void filldb (MYSQL mysql)
{
  char query[256];
  long i;

  printf ("Starting DB init...\n");
  memset (query, 0, sizeof (query));
  sprintf (query, "delete from joepluta");

  if (mysql_query (&mysql, query))
    {
      printf ("%s\n", mysql_error (&mysql));
      exit (-1);
    }

  memset (query, 0, sizeof (query));
  for (i = 0; i < 800000; i++)
    {
      sprintf (query, "insert into joepluta set key2=%ld", i);

      if (mysql_query (&mysql, query))
        {
          printf ("%s\n", mysql_error (&mysql));
          exit (-1);
        }
    }
  return;
}

James Rich

Vs lbh cynl n Zvpebfsg PQ  onpxjneqf, lbh pna urne fngnavp zrffntrf. Ohg
rira jbefr, vs lbh cynl vg sbejneq, vg vafgnyyf gurve fbsgjner!
        -- Fcbgvphf ba /.