Speed.ORM - Born to be fast

Text translated with Google translator

Introduction


Speed.ORM was developed with three prerequisites:

  • Be the fastest in the world ORM. NET;
  • Productivity: Sql generate all the code, freeing the developer to focus on what matters;
  • In a few minutes, generates the data layer and business, 90% by automating this process. It will be up to the developer to write some methods in the business layer.

 

        Has no point of having all resources that have some frameworks such as Entity Framework and Hibernate, but aims to be much faster and use much less memory


        There is a Framework, DAPPER, which is almost as fast as the speed, but still have to write the code Sql, which ends up making its use very unproductive.

 

1 - History


                Working as a specialist in performance of applications and databases for over 10 years. Chemical analysis detected that one of the problems was the ORM framework that was being used or have been developed as the data layer and business, using procedures and ADO.NET.


                Using Entity Framework as ORM, try making a loop to insert records 10000000: it uses several gigabytes of memory, and if the computer does not have enough memory for it will hang. Moreover, despite the Entity Framework has several interesting features, such as LINQ TO SQL is not suitable for a system that needs to be fast and scalable. The same goes for NHibernate


                On the other hand, there are purists who believe the entire system, including routines CRUD should be made of procedures. Who have speech that is faster, and that's how it has to be done. I am not against the use of Stored Procedures not, but in its use only when needed. For example, it makes sense to bring a huge volume of database for the ORM to implement a price increase of 10% to 100 000 products. In this case you must use procedure. But to do the basic CRUD this is not necessary due to the following factors:


  • Cost: account how many hours are spent to make the routines and procedures of crud to one database average of 300 tables for instance? Whereas if you spend 15 minutes to do each procedure Insert, Select, Update and Delete, plus 15 minutes for the DTO class and 60 minutes for the class of business, at a cost of 30 / h, we have the value 20250 , 00. As a developer I know that the underestimation. Actually this value is much, much higher. On the other hand, using an ORM just click a button and instantly CRUD is generated for all tables, including relationships. In addition, the cost of software maintenance procedures exclusively is much higher.
     
  • Maintenance: It is very common in everyday development, the case of putting a new column in a table. The developer, due to the short-term, just putting in procedure "Insert", and does not put the procedure of "Update". Among many other cases that happen
     
  • Logic: Suppose, to save a record in the Customers table using procedure takes 10ms, while using ORM takes 12ms. This causes some impact on system performance? The purist will say yes, but if you ask who will pay for the software (which usually does not know it), surely that does not respond

 

So, I favor the following philosophy:

 


                             The ORM ORM what is and what is Procedure Procedure

 

 



                Speed ​​The library has been developed for 8 years, since 2005. Initially started using Reflection, to move to "build on Runtime", and there are a number of optimizations in the generated code.
I hope that this library can be useful to the community.


 

2- Results of simple performance test


INSERT 10000 records
INSERT Speed ​​- 5195 ms - 100.00%
INSERT Dapper - 5895 ms - 113.47%
Speed ​​was 13.47% faster then Dapper
================================================== ===============
SELECT 10000 records
SELECT Speed ​​- 151 ms - 100.00%
SELECT Dapper - 175 ms - 115.89%
Speed ​​was 15.89% faster then Dapper
================================================== ===============
INSERT 10000 records
INSERT Speed ​​- 4686 ms - 100.00%
INSERT Stored Procedure - 4826 ms - 102.99%
Speed ​​was 2.99% faster then Stored Procedure
================================================== ===============
INSERT 10000 records
INSERT Speed ​​- 4406 ms - 100.00%
INSERT Entity Framework - 654 935 ms - 14864.62%
Speed ​​was 14764.62% faster then Entity
================================================== ===============
SELECT 10000 records
SELECT Speed ​​- 64 ms
SELECT Dapper - 71 ms
SELECT DataReader - 78 ms
SELECT DataTable - 104 ms
SELECT Entity Framework - 463 ms
Faster: SELECT Speed
Speed ​​was 21.88% faster then DataReader
Dapper was 9.86% faster then DataReader
Speed ​​was 10.94% faster then Dapper
Speed ​​was 623.44% faster then Entity
Dapper was 552.11% faster then Entity
 

3- Supported Databases


  • Sql Server
  • Oracle
  • PostgreSQL
  • MySql
  • Firebird
  • SqlServerCe
  • SQLite
  • Access

 

If there is need to support other databases, takes no more than a few hours to make this implementation


4- Structure of Speed


Speed ​​The library is composed of two DLLs:


  • Speed.Common - library with useful classes. Eg
    • CollectionUtil: Class that some additional methods and IEnumerable Dictionary
    • CompressionUtil: Class with methods of compression (zip). Uses SharpZipLib and Ionic
    • Conv: the most used class in Speed. It has conversion methods, among others 
    • Cryptography: Encryption DataTimer <T>: a class is a simple cache in memory. One of his defining a constructor function that will load values, and Timeout. So, you can read the value of the Value property. If Burst Timeout, DataTimer reloads values, otherwise it returns the value of memory 
    • CloneCompiler: Generates a proxy compiled into memory to clone classes (properties and fields). Thousands of times faster than reflection
    • ETC
  • Speed. Data - Classes handling of data and metadata to multiple databases
    • The main class of the Speed.Data is the Database, which encapsulates a connection to the selected database, and also has several useful methods of data access. Ex: ExecutaDataTable, ExecuteScalar, ExecuteInt32, etc..
    • The Database class has a property called "Provider", which has methods for extracting metadata specific to each database

 

 

5- Program Speed


        Program that allows configuration and code generation database.
The program allows the configuration (using database, directories) and the selection of tables and views to be generated classes. The part of code generation for Stored Procedures will be released soon


It consists of two tabs:


5.1 - Tab Configuration



Database:

 

  • Database Type: Select the type of database: Firebird, MySQL, Oracle, PostgreSql and Sql Server. Speed ​​also supports SqlServerCe, but still needs more testing
  • Build Connection String: Connection String is generated through the values ​​of Server, Database, Integrated Security, User ID and Password
  • Enter Connection String: allows you to enter the value of the connection string
  • Host: Server
  • Database: database nameIntegrated Security: using integrated security. In this case no values ​​are entered User ID and Password
  • User ID: User name of the database
  • Password: password
  • Port: Port of the database. Let 0, to use the default port


Global Parameters:

  • Entity Namespace: namespace classes of data
  • Business Namespace: namespace classes of business
  • Entity Directory: directory where the classes will be writed data
  • Business Directory: directory where the classes will be writed business· Suggestion: button that lets you select the solution file (. Sln), which are suggested values ​​for the above parameters. This is not mandatory. The developer can you put your desired values ​​in the parameters

 

5.2 - Tab “Object Browser”

 

                On the left, you can select objects in the database that will be generated classes. Only codes will be generated for the selected objects in the treeview

 
                On the right side appears "Tables" and "Views". For each line, we have the following columns:

 

  • Schema name: Name of the schema in the database. Can not be changed
  • Table name: Name of the table / view in the database. Can not be changed
  • Date Class Name: Name of the data class corresponding to the table / view
  • Business Class Name: name of the class corresponding to the business table / view
  • Enum column name: sometimes we need to generate an enum data from a table. Column name corresponds to the column that has the text values ​​of the enum
  • Enum Id column: The column that has numeric values ​​of the enum
  • Enum name: class name of the enum

 

  • Naming: GroupBox with functions that facilitate the generation of names for classes. Its use is not mandatory. It is only a facilitator. If desired, the developer can enter the values ​​of the classes and enums.
  • Name case: the type of case used to generate the names of classes and enums
    • Original name: used the same value comes from the database 
    • Camel: Camel notation. When the values ​​having "_", this is removed 
    • Pascal: Pascal notation. When the values ​​having "_", this is removed 
    • Lowercase: all letters are converted to lowercase
    • Uppercase: all letters are converted to uppercase
    • Prefix: a prefix that will be placed before the name of classes
    • Start names with schema: Schema Name is placed before the name of the class. But this will come after the "Prefix"
    • Remove: list of values ​​that will be removed from the names, separated by ';'. Can contain spaces to get a better formatting, these characters will not be considered

 

Important: For each object (table / view) will be 4 separate files. Ex: consider a table named Customer. Consider that the Data class called "Customer" and Business class called "Customers". Will generate the following files:

  • Data
    • Customer.cs - data class that has the properties with the column names of the database table or view. When there is a change in the object, the next time it is generated, this class will be overwritten. Therefore, any manual changes made in this class will be lost
    • Customer_Ext.cs - extension class customer. Uses the attribute "partial". Put here additional properties if desired. This file will be generated only 1 time, and will never be overwritten. All changes made here will be preserved
  • Business
    • Customers.cs - business class that has the properties with the column names of the database table or view. When there is a change in the object, the next time it is generated, this class will be overwritten. Therefore, any manual changes made in this class will be lost
    • Customers_Ext.cs - extension class customers. Uses the attribute "partial". Put here the methods if you wish. This file will be generated only 1 time, and will never be overwritten. All changes made here will be preserved




5.3 - Example of use

                 Describe in detail how to use the Speed ​​for a new project. Consider a project called MyProject. This example is a Windows Forms, but it can be a web design, console or any other project. Directories and the names used herein are for example only. The developer can use the values ​​you want



  • Create the directory "C:\Projects". I usually put all my projects amongst a default directory called "Projects". And MyProject will be a solution in thisConsider the database, also called MyProjet, has the following tables

 

  • In Visual Studio
    • Select "New Project"



  • The solution will be created in "C:\Projects\MyProject\MyProject.sln"
  • In the File / Add / New Project
    • Select a project of type "Class Library" and name it "MyProject.Data".
      This will be the project data
      Delete Class1.cs
       
  • In File / Add / New Project
    • Select a project of type "Class Library" and name it "MyProject.BL"
      This is the business project
      Delete Class1.cs
    • In the project "MyProject.BL", add a reference to "MyProject.Data"
    • In the project "MyProject", some references to "MyProject.Data" and "MyProject.BL"



  • Run Speed ​​the application and click the "New" button
     
  • Print screens with the settings that I will detail below:





  • Tab "Configuration"
    • Server type: Sql ServerBuild Connection string: mark
    • Host: localhost. This case is the local machine, but can be any remote server until a provider
    • Database: MyProject
    • Integrated Security: check. You can also use User ID and Password if you want
    • Click the "Connect" button to check if the connection is correct with the database

       
    • Global Parameters:
      • Entity Namespace: Repare that use the Data subdirectory. Thus, all classes will be generated within this subdirectory, and not mix with other classes that you can add after the project
      • Business Directory: Repare I use the subdirectory "BL". Thus, all classes will be generated within this subdirectory, and not mix with other classes that you can add after the project

         
  • Tab "Object Browser"

    • In that case, I will not make major changes in the names. Select all the objects you want and click the notation "Apply", that will be generated names "Data class" and "Business class".In the case of "Data Class", the names were generated in the plural, as are tables. I prefer the singular name of Entity. So I change these values ​​manually. Do not reapply "Apply", but those values ​​are changed. In case you have new objects, select only objects to apply the new "Apply", so the values ​​will be kept from other people because "Apply" applies only to the selected objects in the treeview.
       
    • CustomerTypes is a table that is interesting to generate an enum values. This will allow us to make comparisons in code using Enum instead of numeric values. For this, I created CustomerTypes in a column called "Enum" (could have another name). Called the "Enum Name" of EnumDbCustomerType. EnumDb usually use to indicate that it is an Enum which is originating from the database. Enum before putting also facilitates auto complete.
       
    • Everything is ready.

    • Now click the "Generate" button, and your data layer and business will be ready.Note that the Speed ​​only generates files. It will be up to the developer to add them in projects.


  • "Visual Studio"
     
    • How do I add the generated classes by Speed ​​in my project?
      In Visual Studio, for projects "MyProject.Data" and "MyProject.BL" go in "Solution Explorer" and click the "Show All Files" which will appear folders and files that are not part of the solution, as shown in figure below

  •  
    • Click with the right mouse button on the folder "Data" and select "Include in Project". Repeat this procedure for the folder "BL". Later, when generating code for new objects, the developer will have to do for 4 include individual files for each object.

    • I'm planning to do an add-in to automate this process in Visual Studio. This is why, in the Solution Speed, put screens in a separate DLL project Speed ​​(Speed.UI), because I share it with the Add-in project

    • Final steps:Create a directory called "Lib" (can be another name) the root directory MyProject solution. "C: \ Projects \ MyProject \ Lib"Create a directory called "Speed" inside "Lib""C: \ Projects \ MyProject \ Lib \ Speed" and place inside the DLL's "Speed.Common" and "Speed.Data". If your application is a 'Windows Forms', put it here also the DLL "Speed.Windows".

    • In the project "MyProject.Data" add the following references:
      • Speed.Common
      • Speed.Data
      • System.Runtime.Serialization. This DLL is needed, because the "Data class" using the attributes "DataMember" for serialization in WCF or Web Services
         
    • In the project "MyProject.BL" add the following references:
      • Speed.Common
      • Speed.Data
      • MyProject.Data
      • System.Runtime.Serialization

    • In the project "MyProject" add the following references:
      • Speed.Common
      • Speed.Data
      • MyProject.Data
      • MyProject.BL
      • System.Configuration
      • System.Runtime.Serialization

         
  • Pogrammming
     
    • Here I'll describe how to use a simple registration screen using the projects cleared by Speed

    • The projects were with the following structure

 

  • Note that the table "Customer" has 4 files. Files with "_EXT" are files that you can put additional properties or methods. Files without "_EXT" will be overwritten whenever the object changes to the database and use the application for Speed ​​regenerate the code.

  • Class MyProject.Data\Data\Customer\Base\Customer.cs
    Customer base class. Notice the attribute "partial" in class




  • Class MyProject.Data\Data\Customer\Customer.cs
    Extension class of the base class. Notice the attribute "partial" in class



  • Here we will create a property to use Enum, so we can make comparisons using Enum in code instead of numeric values, as shown in the figure below, the same class




  • Class MyProject.Data\BL\Customer\Base\Customers.cs
    Class based business. Notice the attribute "partial" in class

 


  • Note that methods are generated to return a record for PK and tables daughters and parents related
    Every class inherits from BLClass business, which has several useful methods that are shared by all classes


  • Class MyProject.Data\BL\Customer\Customers_Ext.cs
    Extension class business. Notice the attribute "partial" in class




  • As an example, let's create one method for making a "like" by customer name


    note that no one uses the word SQL "WHERE". Put only what will appear after the "WHERE" in Sql command.

 

  • Configuring the application "MyProject"

    • ConnectionString: Put the file App.config or Web.config:



    • Using the Sys class (in namespace Speed.Data) to access the database.The Sys class has properties and methods for working with the database. Only it is not mandatory to use this class, but it makes it much easier. In an application "Windows Forms" can initialize them in the "Main" method and an application "web" can be the method "Application_Start" the Global.asx:



      • Open a connection to the database using Sys:



      • Open a connection to the database without using the class Sys:




      • Doing without using a Select BL:

        var customers = db.Select <Customer> ();


      • Running a command in the database:

        db.ExecuteNonQuery("delete from Customers");


      • Using the method ExecuteInt32:

        int count = db.ExecuteInt32 ("select count (*) from Customers");


      • Using Transaction:



        Note that at the end of the using block is Commit. If, upon exiting the using block, not applied Commit, Rollback automatically be given a

         
      • Returning the value of a field "IDENTITY":

        The Speed ​​can read the value of an IDENTITY field automatically. By default it does not, precisely considering the performance. If you do not have to carry this value, so that the Speed ​​will make it?But if you need, in method "Insert" or "Save", pass the value "EnumSaveMode.Requery" for the parameter "EnumSaveMode saveMode"



      • Returning a DataTable

        Db.ExecuteDataTable DataTable tb = ("select * from Customers");


      • The Database class has many other additional methods are very useful in developing a system

 

6 - Future


                         The library Speed ​​will continue to be improved, both in performance and in features. Any developer who can devote part of their time to improve and / or make suggestions will be welcome.

I know of a way to do Inserts and Updates of large volumes much faster, and this will be the next improvement that will implement
The development of an integrated add-in to Visual Studio is another improvement scheduled soon.

 

 

The library 

 

Last edited Oct 2, 2013 at 7:18 PM by castefani, version 20

Comments

RocketFramework Jan 2, 2014 at 3:21 AM 
I have doubt when you say

INSERT 10000 records
INSERT Speed ​​- 4406 ms - 100.00%
INSERT Entity Framework - 654 935 ms - 14864.62%
Speed ​​was 14764.62% faster then Entity

I believe you have not done the insert the optimum way.. Will you share the exact code you used to do the insert..