EffiProz Database Blog

EffiProz Vs SQLite File Database Performance

2011-04-06T04:05:00.000-07:00

EffiProz Vs SQLite File Database Performance. In-Memory Database performance figures available at http://blog.effiproz.com/2011/04/effiproz-vs-sqlite-in-memory-database.html

EffiProz 1.6 beta2 (http://www.effiproz.com/downloads/EffiProz_1_6_4113_20476_beta2.zip) Vs SQLite In-Memory Database Performance.
Benchmark (http://www.effiproz.com/downloads/Benchmark.zip) is similar to the one found in SQLite speed comparison page (http://www.sqlite.org/speed.html).

Below is the same graph without the first column,

EffiProz Vs SQLite In-Memory Database Performance

2011-04-06T00:28:00.000-07:00

EffiProz 1.6 beta2 (http://www.effiproz.com/downloads/EffiProz_1_6_4113_20476_beta2.zip) Vs SQLite In-Memory Database Performance.
Benchmark (http://www.effiproz.com/downloads/Benchmark.zip) is similar to the one found in SQLite speed comparison page (http://www.sqlite.org/speed.html).

Multi-path cascaded deletes & updates

2011-03-12T04:29:00.000-08:00

EffiProz 1.6 beta allows you to manually control if a cascaded delete of a row that is being cascaded update by the same operation generate an error (default behavior).
Use SET DATABASE SQL TDC DELETE statement to control this behavior as show in the following example,

create table Tbl1(col1 int primary key, col2 int);
insert into Tbl1 values(1,100);
insert into Tbl1 values(2,200);

create table Tbl2(col1 int foreign key references Tbl1(col1) on delete cascade, col2 int unique);
insert into Tbl2 values(1,300);
insert into Tbl2 values(2,400);

create table Tbl3(col1 int primary key foreign key references Tbl1(col1) on delete cascade, col2 int foreign key references Tbl2(col2) on delete SET NULL, col3 varchar(100) );
insert into Tbl3 values(1,300,'car');
insert into Tbl3 values(2,400,'bus');

/*ERROR*/delete from Tbl1 where col1=1;

SET DATABASE SQL TDC DELETE FALSE;
delete from Tbl1 where col1=1;

SET DATABASE SQL TDC DELETE TRUE;
/*ERROR*/delete from Tbl1 where col1=2;

Unique NULLs and Concat NULLs

2011-03-11T23:15:00.000-08:00

Two new configuration options, SQL UNIQUE NULLS and SQL CONCAT NULLS, in EffiProz 1.6 can be used to control how EffiProz treat NULL values in unique constrain evaluations and in string concatenation operation.
Following example shows how SQL UNIQUE NULLS works,

create table uqtest(col1 int, col2 int, unique(col1,col2));

SET DATABASE SQL UNIQUE NULLS TRUE;
insert into uqtest values(1,null);
insert into uqtest values(1,null);
delete from uqtest;

SET DATABASE SQL UNIQUE NULLS FALSE;

insert into uqtest values(1,null);
/*ERROR*/insert into uqtest values(1,null);

Following example shows how SQL CONCAT NULLS works,

SET DATABASE SQL CONCAT NULLS TRUE;
/*result NULL*/select 'efz' ||convert( null,varchar(100));

SET DATABASE SQL CONCAT NULLS FALSE;
/*result efz*/select 'efz' || convert( null,varchar(100));

Grouping JOIN Clauses

2011-03-10T22:21:00.000-08:00

EffiProz 1.6 will fully support Parenthesis Grouped Join Clauses.
Download EffiProz 1.6 beta from http://www.effiproz.com/downloads/EffiProz_1_6_4087_16609_beta.zip

A sample query with Parenthesis Grouped Join Clause on Northwind database,

SELECT "Invoices2" AS SELECT "Orders"."ShipName", "Orders"."ShipAddress", "Orders"."ShipCity", "Orders"."ShipRegion", "Orders"."ShipPostalCode", 
 "Orders"."ShipCountry", "Orders"."CustomerID", "Customers"."CompanyName" AS "CustomerName", "Customers"."Address", "Customers"."City", 
 "Customers"."Region", "Customers"."PostalCode", "Customers"."Country", 
 ("FirstName" + ' ' + "LastName") AS "Salesperson", 
 "Orders"."OrderID", "Orders"."OrderDate", "Orders"."RequiredDate", "Orders"."ShippedDate", "Shippers"."CompanyName" As "ShipperName", 
 "Order Details"."ProductID", "Products"."ProductName", "Order Details"."UnitPrice", "Order Details"."Quantity", 
 "Order Details"."Discount", 
 (CONVERT(("Order Details"."UnitPrice"*"Quantity"*(1-"Discount")/100),decimal)*100) AS "ExtendedPrice", "Orders"."Freight"
 FROM  "Shippers" INNER JOIN 
 ("Products" INNER JOIN 
 (
 ("Employees" INNER JOIN 
 ("Customers" INNER JOIN "Orders" ON "Customers"."CustomerID" = "Orders"."CustomerID") 
 ON "Employees"."EmployeeID" = "Orders"."EmployeeID") 
 INNER JOIN "Order Details" ON "Orders"."OrderID" = "Order Details"."OrderID") 
 ON "Products"."ProductID" = "Order Details"."ProductID") 
 ON "Shippers"."ShipperID" = "Orders"."ShipVia";

Which is equivalent to the following query,

SELECT "Invoices" AS
 SELECT "Orders"."ShipName", "Orders"."ShipAddress", "Orders"."ShipCity", "Orders"."ShipRegion", "Orders"."ShipPostalCode", 
 "Orders"."ShipCountry", "Orders"."CustomerID", "Customers"."CompanyName" AS "CustomerName", "Customers"."Address", "Customers"."City", 
 "Customers"."Region", "Customers"."PostalCode", "Customers"."Country", 
 ("FirstName" + ' ' + "LastName") AS "Salesperson", 
 "Orders"."OrderID", "Orders"."OrderDate", "Orders"."RequiredDate", "Orders"."ShippedDate", "Shippers"."CompanyName" As "ShipperName", 
 "Order Details"."ProductID", "Products"."ProductName", "Order Details"."UnitPrice", "Order Details"."Quantity", 
 "Order Details"."Discount", 
 (CONVERT(("Order Details"."UnitPrice"*"Quantity"*(1-"Discount")/100),decimal)*100) AS "ExtendedPrice", "Orders"."Freight"
 FROM "Shippers" ,"Products","Employees","Customers","Orders","Order Details"
 WHERE
 "Shippers"."ShipperID" = "Orders"."ShipVia" 
 AND "Products"."ProductID" = "Order Details"."ProductID"
 AND "Orders"."OrderID" = "Order Details"."OrderID"
 AND "Customers"."CustomerID" = "Orders"."CustomerID"
 AND "Employees"."EmployeeID" = "Orders"."EmployeeID";

ROWNUM pseudo-column

2011-03-10T20:09:00.000-08:00

EffiProz 1.6 is to support ROWNUM pseudo-column.
Download EffiProz 1.6 beta from http://www.effiproz.com/downloads/EffiProz_1_6_4087_16609_beta.zip

select name, price  from items where rownum < 6;

DatAdmin for EffiProz 5.0.2.1

2011-02-23T01:56:00.000-08:00

DatAdmin for EffiProz 5.0.2.1 is now available with Effiproz-DB zip distribution http://www.effiproz.com/downloads/effiproz_db_1_5_4016_30245.zip or as a standalone download http://datadmin.com/brands/effiproz/5.0.2.1.zip

Simego Data Synchronization Studio for EffiProz

2011-02-19T06:21:00.000-08:00

EffiProz databse is now supported in Simego Data Synchronization Studio 3 Beta (http://www.simego.com/) .
You can download Data Synchronization Studio 3 Beta from http://www.simego.com/Beta

Connection Dialog,

Select Database Tables

Main Window,

EffiProz Systems in 2010 Red Herring Global 100

2011-01-28T09:06:00.000-08:00

EffiProz Systems selected as a Winner for Red Herring's top 100 Global award. http://www.herring100.com/rhg2010/winners/2010winners.html

EffiProz to bundle DatAdmin Tool

2011-01-10T02:06:00.000-08:00

EffiProz and DatAdmin (http://datadmin.com/) have come to an agreement to bundle DatAdmin Database Administration Tool (Special Edition) with EffiProz distributions.

EffiProz uses can separately buy DatAdmin Profession Edition if they need more functionality, but even the free DatAdmin Tool bundled with EffiProz has far more functionality than EffiProz Query Tool.

In future we will stop supporting Windows Query Tool but continue to enhance & support Silverlight Query Tool.

EffiProz Systems in 2010 RED HERRING GLOBAL FINALISTS

2010-12-23T16:46:00.000-08:00

EffiProz Systems has been selected as Finalist for Red Herring's top 100 Global award, a prestigious list honoring the year’s most promising private technology ventures from the Asia business region. http://www.herring100.com/rhg2010/finalists/finalists.html

EffiProz 1.5RC

2010-12-09T00:45:00.000-08:00

Download EffiProz 1.5RC from http://www.effiproz.com/downloads/EffiProz_1_5_3995_25329.zip.
Whats New in 1.5RC,

Recursive CTEs http://blog.effiproz.com/2010/10/recursive-ctes-common-table-expressions.html
Table Variables http://blog.effiproz.com/2010/10/table-variables-and-recursive-functions.html
Recursive Functions http://blog.effiproz.com/2010/10/table-variables-and-recursive-functions.html
Table Parameters in Stored Procedures http://blog.effiproz.com/2010/10/table-parameters-in-stored-procedures.html
ADO.NET Provider support for passing Table Parameters http://blog.effiproz.com/2010/10/invoke-stored-procedures-with-table.html
Auto Commit mode enabled by default
Performance Enhancements
Bug Fixes

Move existing EffiProz Database to Windows Phone 7

2010-12-06T20:37:00.000-08:00

Following example, contributed by Manfred (http://manniat.pp-p.net), shows how to move an existing EffiProz Database to Windows Phone 7 emulator/device,
http://www.effiproz.com/downloads/WPTest1.zip

ManniAT Partners with EffiProz

2010-12-06T18:11:00.000-08:00

ManniAT, a Partner of EffiProz Systems, has opened a new website (http://effiproz.pp-p.net/) to sell EffiProz products and Solutions.

Sync Framework Demo

2010-12-04T09:34:00.000-08:00

This demo illustrates how to Sync a EffiProz database with a SQL CE database via Microsoft Sync Framework 2.1.
Download: http://www.effiproz.com/downloads/SyncFxDemo.zip

EffiProz 1.5 beta dll and a provisioned sample database is included with the demo.

This demo is adapted from MSDN Database Sync - Oracle and SQL Compact 2-Tier (http://code.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=3761 )

Entity Framework Demo

2010-11-29T03:13:00.000-08:00

Entity Framework Demo similar to MSDN EF Quickstart ( http://msdn.microsoft.com/en-us/library/bb399182.aspx) http://www.effiproz.com/downloads/CourseManager.zip

Need to install EffiProz Entity Framework Provider and VS2010 extention before running the sample (http://www.effiproz.com/downloads/EffiProz_1_4.msi).

EnitySpaces Demo

2010-11-27T02:39:00.000-08:00

EntitySpaces (www.entityspaces.net ) Windows_Forms_C# demo modified to work with EffiProz Database (zip includes converted Northwind database) http://www.effiproz.com/downloads/Windows_Forms_EffiProz_EntitySpaces.zip.

Before running the sample add EffiProz.dll version 1.4.3926.17773 to GAC.

Chinook Sample Database

2010-11-12T00:09:00.000-08:00

Popular Chinook Sample Database (http://chinookdatabase.codeplex.com/) is now available for EffiProz.
Pre-Build Chinook EffiProz database preview is available for download at http://www.effiproz.com/downloads/Chinook.zip.
Chinook SQL scripts will be available from Chinook site (http://chinookdatabase.codeplex.com/) after they formally release it.

Free Database Master 3 Licenses for EffiProz Customers

2010-11-03T05:44:00.000-07:00

Neucleons Software (http://nucleonsoftware.com/) has generously offered FREE Database Master 3 licenses (limited number) for EffiProz customers.
Contact EffiProz support (support@effiproz.com ) with your License Key to redeem this offer.

EffiProz Systems is a Finalist for the 2010 Red Herring 100 Asia Award

2010-10-25T02:46:00.000-07:00

EffiProz Systems has been selected as a Finalist for Red Herring's top 100 Asia award, a prestigious list honoring the year's most promising private technology ventures from the Asia business region.
http://herringevents.com/rha2010/finalists/2010finalists.html

Expressions with long AND/OR chains

2010-10-25T00:00:00.000-07:00

Logical expressions with long AND/OR chains, like the one shown below (where EffiProz 1.4 had performance issues), now works smoothly with new optimizations in EffiProz 1.5.
(Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3950_22178.zip)

SELECT ORDERID , CUSTOMERID FROM PUBLIC.ORDERS WHERE ORDERID=10248 OR ORDERID=10249 OR ORDERID=10250 OR ORDERID=10251 OR ORDERID=10252 OR ORDERID=10253 OR ORDERID=10254 OR ORDERID=10255 OR ORDERID=10256 OR ORDERID=10257 OR ORDERID=10258 OR ORDERID=10259 OR
                        ORDERID=10260 OR ORDERID=10261 OR ORDERID=10262 OR ORDERID=10263 OR ORDERID=10264 OR ORDERID=10265 
                        OR ORDERID=10266 OR ORDERID=10267 OR ORDERID=10268 OR ORDERID=10269 OR ORDERID=10270 OR ORDERID=10271
                        OR ORDERID=10272 OR ORDERID=10273 OR ORDERID=10274 OR ORDERID=10275 OR ORDERID=10276 OR ORDERID=10277 
                        OR ORDERID=10278 OR ORDERID=10279 OR ORDERID=10280 OR ORDERID=10281 OR ORDERID=10282 OR ORDERID=10283 
                OR ORDERID=10284 OR ORDERID=10285 OR ORDERID=10286 OR ORDERID=10287 OR ORDERID=10288 OR ORDERID=10289 
                OR ORDERID=10290 OR ORDERID=10291 OR ORDERID=10292 OR ORDERID=10293 OR ORDERID=10294 OR ORDERID=10295 OR
                ORDERID=10296 OR ORDERID=10297 OR ORDERID=10298 OR ORDERID=10299 OR ORDERID=10300 OR ORDERID=10301 OR 
                ORDERID=10302 OR ORDERID=10303 OR ORDERID=10304 OR ORDERID=10305 OR ORDERID=10306 OR ORDERID=10307 OR 
                ORDERID=10308 OR ORDERID=10309 OR ORDERID=10310 OR ORDERID=10311 OR ORDERID=10312 OR ORDERID=10313 OR
                ORDERID=10314 OR ORDERID=10315 OR ORDERID=10316 OR ORDERID=10317 OR ORDERID=10318 OR ORDERID=10319 OR
                ORDERID=10320 OR ORDERID=10321 OR ORDERID=10322 OR ORDERID=10323 OR ORDERID=10324

Invoke Stored Procedures with Table Parameters via ADO.NET provider

2010-10-23T06:44:00.000-07:00

EffiProz 1.5 ADO.NET provider supports passing either a DataTable or a DbDataReader to a Stored Procedure Table Parameter. Following example demonstrates how to pass a DataTable to a Stored Procedure. (Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3948_33802.zip )

string connString = "Connection Type=Memory ; auto commit=true; Initial Catalog=TestSPTabularParameter; User=sa; Password=;";
            string sql = @"CREATE TABLE MyTable (
                         col1 int NOT NULL PRIMARY KEY,
                         col2 varchar(20) NULL,                       
                         UserID varchar(20) NOT NULL
                         );";
            using (DbConnection conn = new EfzConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                conn.Open();
                command.ExecuteNonQuery();

                sql = @"CREATE TYPE MyType AS TABLE 
                         (
                         col1 int  , 
                         col2 varchar(20)                        
                         );";
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = @"CREATE PROCEDURE AddRowsToMyTable( @MyTableParam MyType,@UserID varchar(20))
                         BEGIN
                         INSERT INTO MyTable(col1,col2,UserID)SELECT col1,col2,@UserID FROM @MyTableParam;
                         END;";

                command.CommandText = sql;
                command.ExecuteNonQuery();

                DataTable table = new DataTable("@MyTableParam");
                DataColumn col1 = new DataColumn("col1", System.Type.GetType("System.Int32"));
                DataColumn col2 = new DataColumn("col2", System.Type.GetType("System.String"));
              
                table.Columns.Add(col1);
                table.Columns.Add(col2);
           
                for (int i = 1; i < 10; i++)
                {
                    var vals = new object[2];
                    vals[0] = i;
                    vals[1] = "TEST"+i;                 
                    table.Rows.Add(vals);
                }

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "AddRowsToMyTable";

                DbParameter p1 = command.CreateParameter();
                DbParameter p2 = command.CreateParameter();
                command.Parameters.Add(p1);
                command.Parameters.Add(p2);
                p1.ParameterName = "@MyTableParam";
                p2.ParameterName = "@UserID";
                p1.Value = table;             
                p2.Value = "admin";
                command.ExecuteNonQuery();

                command.CommandText = "SELECT * FROM MyTable";
                command.CommandType = CommandType.Text;
                DbDataReader reader = command.ExecuteReader();
  }

Table Parameters in Stored Procedures

2010-10-19T08:38:00.000-07:00

Following examples demonstrate how-to use Table type parameters in Stored Procedures in EffiProz 1.5 (Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3944_36929.zip ),

CREATE TYPE MyType AS TABLE 
 (
 col1 int  , 
 col2 varchar(20) , 
 col3 datetime
 );

DECLARE MyTableVar MyType;

INSERT INTO MyTableVar(col1,col2,col3)
 VALUES (1,'pat','1/1/2000'),
 (2,'mat','1/1/2011'),
 (3,'bat','1/1/2011'),
 (4,'cat','1/1/2023'),
 (5,'rat','1/1/2034');


CREATE TABLE MyTable (
 col1 int NOT NULL PRIMARY KEY,
 col2 varchar(20) NULL,
 col3 datetime NULL,
 UserID varchar(20) NOT NULL
 );

CREATE PROCEDURE usp_AddRowsToMyTable( @MyTableParam MyType,@UserID varchar(20))
 BEGIN
 INSERT INTO MyTable(col1,col2,col3,UserID)SELECT col1,col2,col3,@UserID FROM @MyTableParam;
 END;

CALL usp_AddRowsToMyTable(  MyTableVar,'Admin');

SELECT * FROM MyTable;

Following example uses a Procedure local Table as an intermediate store,

CREATE PROCEDURE usp_AddRowsToMyTable3( @MyTableParam MyType,@UserID varchar(20))
 BEGIN
 DECLARE @localtable MyType;
 INSERT INTO  @localtable(col1,col2,col3) SELECT col1,col2,col3 FROM @MyTableParam;
 Call usp_AddRowsToMyTable(@localtable,@UserID);
 END;

CALL usp_AddRowsToMyTable3(  MyTableVar,'Admin');

/*r5*/SELECT count(*) FROM MyTable;

Table Variables and Recursive Functions

2010-10-19T08:20:00.000-07:00

Following example (Similar to the one provided by HSQL) demonstrates how use Recursive Functions with Table variables in upcoming EffiProz 1.5 (Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3944_36929.zip),

CREATE TABLE ptree (pid INT, id INT);
INSERT INTO ptree VALUES (NULL, 1) ,(1,2), (1,3),(2,4),(4,5),(3,6),(3,7);


CREATE FUNCTION child_table(p_pid INT) RETURNS TABLE(r_pid INT, r_id INT)
  SPECIFIC child_table_one
  READS SQL DATA
  SIGNAL SQLSTATE '45000';

ALTER SPECIFIC ROUTINE child_table_one SET BODY
  BEGIN ATOMIC
    DECLARE TABLE child_tree (pid INT, id INT);
    for_loop:
    FOR SELECT pid, id FROM ptree WHERE pid = p_pid DO
      INSERT INTO child_tree VALUES pid, id;
      INSERT INTO child_tree SELECT r_pid, r_id FROM TABLE(child_table(id));
    END FOR for_loop;
    RETURN TABLE(SELECT * FROM child_tree);
  END

SELECT * FROM TABLE(child_table(1));

Recursive CTEs (Common Table Expressions)

2010-10-15T23:52:00.000-07:00

EffiProz 1.5 development release with Recursive CTE support http://www.effiproz.com/downloads/effiproz_db_1_5_3941_21958.zip

EffiProz supports connecting non-recursive and recursive elements of the CTE with either UNION or UNION ALL operators. Maximum number of recursions allowed is limited to 10000.

Few examples on different types of CTEs supported by EffiProz: http://www.effiproz.com/examples/cte.aspx.