Funny SQL Procedure

Groom writes:

CREATE PROCEDURE

MyMarriage BrideGroom Male (25) , Bride Female(20) AS BEGIN

SELECT

Bride FROM Bangladesh_ Brides

WHERE FatherInLaw = 'Millionaire'

AND Count(Car) > 20 AND HouseStatus ='ThreeStoreyed'

AND BrideEduStatus IN (BSc ,BA ,Degree ,CA ,MBA)

AND Having Brothers= Null

AND Sisters =Null

SELECT

Gold ,Cash,Car,BankBalan ce

FROM FatherInLaw

UPDATE MyBankAccout

SET MyBal = MyBal + FatherInLawBal

UPDATE MyLocker

SET MyLockerContents = MyLockerContents + FatherInLawGold

INSERT INTO MyCarShed VALUES ('BMW')

END GO

Then the Bride writes the following query:

CREATE PROCEDURE DROP HUSBAND;

Commit;

Data Manipulation Language(DML)

Data Manipulation Language (DML) is a family of computer languages used by computer programs database users to retrieve, insert, delete and update data in a database.

Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases (such as IDMS), and others.

Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people, as well.

Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

• Select
• Insert
• Update
• Delete

Each SQL statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative, in that they describe what the program should accomplish, rather than describing how to go about accomplishing it.

Most SQL database implementations extend their SQL capabilities by providing imperative, i.e., procedural, languages. Examples of these are Oracle’s PL/SQL and DB2’s SQL PL.

Data Manipulation Languages tend to have many different flavors and capabilities between database vendors. There has been a standard established for SQL by ANSI, but vendors still provide their own extensions to the standard while not implementing the entire standard.

There are two types of Data Manipulation languages:

1. Procedural
2. Declarative

Which one is Faster ….InnoDB Or MyISAM ?

In web development MySQL is nowadays widely used database.So speed is a major issue for all operations of MySQL . But which DB engine should be used to execute a insert operation in MySQL. MyISAM or InnoDB ? Well i had a test to track the execution time for this two types of DB engine. I was amazed with seeing the performance of these two engines.MyISAM is extremly faster than the InnoDB to execute a insert query.I executed same query several times and all the times the same thing happened.

I had two table one is with InnoDB and other is with MyISAM.Both of the table was same except the table name and DB engine used while i created these two tables.

Here is my table definitions…..

Table 1:

CREATE TABLE data(

id int  not null auto_increment primary key,

name varchar(120) not null,

text varchar(120) not null) ENGINE=InnoDB;

Table 2 :

CREATE TABLE dataisam(

id int  not null auto_increment primary key,

name varchar(120) not null,

text varchar(120) not null) ENGINE=MyISAM;

Here is the PHP codes to track the executing time :

<?php
    ini_set('max_execution_time',600);

    $connection = mysql_connect('localhost','root','') or die('error in connection!');
    mysql_select_db('timetest',$connection) or die('cannot connect to database!');

    $let = "abcdefghijklmnopqrstuvwxyz";
    $row = 1000;

    $sttime = time();

    for($i=0;$i<$row;$i++)

    {
        $s = rand(0,25);
        $l = rand(4,10);
        $name = substr($let,$s,$l);
        $query=mysql_query("insert into dataisam(id,name,text) values('','$name','text')");
    }

    $end1 = time();

    for($i=0;$i<$row;$i++)
    {
        $s = rand(0,25);
        $l = rand(4,10);
        $name = substr($let,$s,$l);
        $query=mysql_query("insert into data(id,name,text) values('','$name','text')");
    }

    $end2 = time();

    $isam = $end1 - $sttime;
    $inno = $end2 - $end1;

    echo "MyIsam takes:".$isam."sec";
    echo "Innodb takes:".$inno."sec";
?>

Now let see the execution time for these two engine:

Execution 1:

MyIsam takes:1 sec
Innodb takes:33 sec

Execution 2:

MyIsam takes:0 sec
Innodb takes:29 sec

Execution 3:

MyIsam takes:1 sec
Innodb takes:30 sec

The computer used for this test is DELL inspirion 1501(Processor: AMD Turion X2 1.6 GHz, 1024 MB ram)