From: jfr@glitter.RedBrick.COM ()
Subject: Re: WATCOM SQL  (Opinions sought)
Organization: Red Brick Systems, Los Gatos, CA
Date: Sun, 13 Sep 1992 19:02:38 GMT

In article <BuE700.9oA@watserv1.uwaterloo.ca> rpeterse@watserv1.uwaterloo.ca (Raymond Petersen) writes:
>In article <THOMASG.915.715985512@epx.cis.umn.edu> THOMASG@epx.cis.umn.edu (ThomasD.Gasser) writes:
>[munch]
>4) The part that I use most is Embedded SQL (in my C code).  I then use a
>   pre-processor which converts the SQL in my C code to C function calls that
>   are resolved at link time by library that comes in the developer's edition.
>   (It comes with Borland,Microsoft,and WATCOM C compilers.  If you need it for
>   another compiler - ask.)  The one thing that some people may find unusual is
>   that in WATCOM SQL all Embedded Sql is dynamic.  None of it is precompiled,
>   it is only pre-processed.  There are a LOT of advantages to doing it this
>   way.  The reason most products precompile SQL into a PLAN table, is
>   execution speed, but lack of speed isn't one of WATCOM SQLs shortcomings.

   This is NOT unusual.  In fact, with the exception of IBM's and DEC's
   precompilers for DB2, SQL/DS and RDB, most of the SQL engines that 
   have precompilers use dynamic SQL for execution (i.e., they pass
   the SQL statement as a string from the program at execution time).
   Sybase supports stored procedures but no precompiler (that may have
   changed recently but I am not sure whether the precompiler uses
   the stored procedures).  For most SQL engines, "static" SQL is
   just a precompiled cover on dynamic SQL.  

   The reason that *some* products (like DB2 and RDB) precompile SQL
   is because the cost of optimizing large queries (particularly
   multi-way joins) at execution time can be very high.  Doing it
   once at precompile (or bind) time is much cheaper in machine
   resources and also permits the optimizer to investigate far more
   potential choices than a run-time optimizer can if you expect it
   to be responsive.  All of this depends on how complex your queries
   are and how big your databases are.  Any PC-based database is
   going to be relatively small (maybe tens of megabytes or so)
   compared to mainframe and large server databases of gigabytes
   and upwards.  What you consider good speed on a "large" (your
   view) database of 50,000 rows will degenerate rapidly on a
   truly large database of 50,000,000 rows.  At that point, the  
   more time an optimizer has, the more likely it is to find the
   best and fastest path through the query. 

>[munch]
>   - ANSI standard SQL transaction logs

   Huh?  ANSI provides no standard for SQL transaction logs that I
   know of.  Is this a claim of the software vendor or your editorial
   commentary?  Inquiring minds wish to know :-)

>   - Transaction processing with a checkpoint log and a forward transaction log
>   - Comprehensive security capabilities
>   - Updatable views
>   - Easily imports data files from other popular data formats.
>   - Automatically uses expanded and extended memory to improve performance
>   - Will run on 640K PCs (I do)
>   - Royalty free run-time - $99.
>
>6) Other interesting facts.
>   a) All strings are variable length.

   This is not a feature I consider good.  Specifically, it must impact
   performance in SOME way to not have fixed length strings when varchars 
   are unnecessary.  Why is it that practically no one gets this right?
   
>   b) NULL values in a row only use 1 bit to store.

   I assume you mean 1 bit per field.  This is common but limiting if
   you ever want to support multi-valued NULLs (ala SQL2) or have other
   uses for a column-oriented flag.  I prefer at least 2 bits per column.

>   c) The developers spent a lot of time comparing the speed of WATCOM SQL
>      (the 386 database engine) and Microsoft SQL Server, so that their product
>      would outperform Microsoft's on the most common operations.

   Not very difficult, IMHO :-)  I would be more interested in how they
   compare to Oracle System 7.

   Jon Rosen

Newsgroups: comp.databases