Why to use Stored Procedure or Prepared Statement?

In web development, we do face the issue of running a query few thousands to million times everyday. If your website traffic is small then the impact of such queries is not noticed. but if your site attracts lots of users and the query requires to run a good number of time, it might hurt your database performance. every query we run goes through a rigorous cycle to produce the desire result. it goes from parsing, optimizing, executing and returning the result. once a query is written and ran correctly for the first time then the steps of parsing and optimization is not necessarily requires as it is already been parsed and optimized. for the same query it is kind of redundant efforts going on. but what if we can just execute the query and get the result and bypass few of the early steps? well it’s possible and that is where the concept of Stored Procedure and Prepared statements come from. now let’s look at the detail of it

 Prepared statement:

Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner. A typical prepared statement would look something like:

The ? is what is a called a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the ? in the query above.

benefits:

as from Harrison Fisk article on prepared statement

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

The increase in performance in prepared statements can come from a few different features. First is the need to only parse the query a single time. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to a speed increase if you need to run the same query many times, such as when doing many INSERT statements.

following image will illustrate the fact

storproc.jpg

Stored procedure:
A stored procedure is a precompiled executable object that contains one or more SQL statements. Hence you can replace your complex SQL statements with a single stored procedure. Since, stored procedures are precompiled objects they execute faster at the database server. For the consecutive run it will run from the compiled stage and hence boost performance.

Note: You have to choose the when to use to use what. certainly not every query should be transformed to prepared statement  or stored procedures.  🙂

New initiatives for Bangla Language on Web

I am thinking of introducing new Bangla based services for the web. here are few of my ideas which will go development phase very soon

1. bangla spell checker

2. bangla wysiwyg editor (probably tinymce)

3. bangla translation

4. bangla unicode pdf

5. bangla universal writing panel with keyboard support (from somewhereinblog write panel)

if you have some cool idea, you can share with me and also you can participate in any of the projects above if you want to.

WordPress Plugins Development

I had started writing my new book for Packt Publishing titled “WordPress Plugins Development”. But I decided to take the back step as I was very busy with my projects at office and couldn’t manage good amount of time for the book. I believe in quality rather than quantity. So, as i am getting bit free time now, I want to finish the book with a modified outline and publish it online. So that people can review and give me necessary feedback to make it a good one. May be later I will try to publish it but i want to focus on the writings for right now.

Every 2/3 weeks a new chapter will be uploaded online with all the codes and graphics so that user can read and apply the things they will learn. I hope to upload the first chapter in the first week of April.

Certified Scrum Master

Recently I have completed my Certified Scrum Master training from Pete Deemer of Good Agile (http://www.goodagile.com). I have attended the 14 hour training program along with my colleague N.H.M Tanveer Hossain Khan on 5th March, 2008 in New Delhi, India. It’s been a nice experience for us and we believe it will be very helpful for us to implement a good scrum oriented development all over the organization. Thanks to Arild for this opportunity and We hope to deliver the expected result to him.

Me and Hasan is also trying to organize a Scrum Master Training in Bangladesh for the first time in this year. So anybody interested to attend the course can drop me a line.

scrummaster1.jpg

Mediawiki tutorials coming soon…

Currently I am working with Mediawiki version 1.11 and lots of things have changed and new things emerged during last 1 year. So i feel that my book “Mediawiki administrator’s tutorial guide” requires some additional writings as well. From April 2008, i will be writing new tutorials every week on Mediawiki including some hacks, extensions and template design.

I hope it will be helpful for all Mediawiki users and administrators 🙂

Installing CodeIgniter on IIS

I faced a serious problem during deploying one of my CodeIgniter application to a windows server running on IIS. After installing CodeIgniter the index.php page was not visible at all. I tried many ways to configure it but it does not show anything. A problem with SEF (search engine friendly) URL CodeIgniter follows. I searched the internet but couldn’t find a suitable solution or answer to the problem. After two day I found a very good and step by step solution for this problem.  You can have a look at it in the following URL.

Installing CodeIgniter on IIS

Bangla Currency (Taka) Formatting

Today i wrote a simple code to convert a number to Bangladeshi currency (taka) format. the php format_number puts a comma (“,”) after every 3 digits which is not same for Bangladeshi currency. here is the simple function to do the job for yo. it can handle integer and decimal numbers.

function taka_format($amount = 0)
{
$tmp = explode(“.”,$amount); // for float or double values
$strMoney = “”;
$divide = 1000;
$amount = $tmp[0];
$strMoney .= str_pad($amount%$divide,3,”0″,STR_PAD_LEFT);
$amount = (int)($amount/$divide);
while($amount>0)
{
$divide = 100;
$strMoney = str_pad($amount%$divide, 2,”0″,STR_PAD_LEFT).”,”.$strMoney;
$amount = (int)($amount/$divide);
}

if(substr($strMoney, 0, 1) == “0”)
$strMoney = substr($strMoney,1);

if(isset($tmp[1])) // if float and double add the decimal digits here.
{
return $strMoney.”.”.$tmp[1];
}
return $strMoney;
}

an alternate way of doing this is via substr

function taka_format($amount = 0)
{
$tmp = explode(“.”,$amount);  // for float or double values
$strMoney = “”;
$amount = $tmp[0];
$strMoney .= substr($amount, -3,3 ) ;
$amount = substr($amount, 0,-3 ) ;
while(strlen($amount)>0)
{
$strMoney = substr($amount, -2,2 ).”,”.$strMoney;
$amount = substr($amount, 0,-2 );
}

if(isset($tmp[1]))         // if float and double add the decimal digits here.
{
return $strMoney.”.”.$tmp[1];
}
return $strMoney;
}

Downloading PHP 6.0 from CVS :)

just downloaded the php 6.0 files from php.net cvs . i am really curious to see what’s going on with the new development. It is expected that the unicode support will be improved a lot in this version of php. Going to build it and have a look with the new version.

updates to be followed.

Code Igniter & maani.us

Many of us feels that a report without a graph is totally incomplete. I also agree with it completely. We need to represent the data in reports to be meaningful and also representable. A table with rows of data does not give the reader a clear idea about what is actually happening. So it is better to show a chart or a graph to the user before showing the long tables of data.

In PHP, one of the most popular graph tool is php/swf charts from maani.us. This tool provides some cool looking graphs with easy to integrate with your application. The integration becomes much easier if you are using Oode Igniter. In this article I am going to describe you, how to integrate maani.us php/swf charts with your Code Igniter application.

Detail Steps to Install the application:

1. download php/swf charts from http://www.maani.us

2. Create a directory named charts or any appropriate name in your root folder (not inside the Code Igniter system folder). it is better if you keep the CI system folder and charts folder on same level.

3. put the unzipped content (except the charts.php) file of the downloaded file in this folder.

Writing Code Igniter code:

You can integrate the maani.us code by either creating a library in Code Igniter or by simply creating a helper. I am going to show you the process by creating a helper file. In order to create a helper file in Code Igniter, you can either create a helper file in systemhelpers or systemapplicationhelpers file. In the later case you have to create the directory as Code Igniter does not create it by default. It is always better to keep the core files and folders of Code Igniter to be intact. Code Igniter gives you the flexibility to extend any of its helpers, libraries, plugins etc to be extended from the application folder.

here are the detail steps for creating and showing graph in a view:

1. first create a helper named graph_helper.php and copy paste the content of charts.php in this file. save the file.

2. now load the helper file from your controller where you want to show the graph. there are two parts in the helper file. One for inserting the chart and in order to show a chart, we need to define the data and chart properties. the SendChartData function defines the properties of the chart and InsertChart shows the Data. in InsertChart function we have to define few things

  • location of the flash file (.swf) , in our case in the charts folder in base directory.
  • location of the charts library, in our case in the charts folder in base directory.
  • PHP source location for ChartData or SendChartData function, in our case a controller.
  • width, height, background color, transparency and license information

here is the code inside a controller looks like.

$this->load->helper(“graph”);
$url = site_url().”/reports/showsummary/”;
$charturl = base_url().”/charts/charts.swf”;
$chartlib = base_url().”/charts/charts_library”;

$data[‘chart’] = InsertChart( $charturl, $chartlib, $url ,650,300,”cccccc” ); // passing the chart information to view.

$this->load->view(“reports/dashboard”, $data);
now we have to define the controller which will send the data for the graph.

function showsummary()

{

$this->load->helper(“graph”);
$chart[ ‘axis_category’ ] = array ( ‘font’=>”arial”, ‘bold’=>true, ‘size’=>16, ‘color’=>”000000″, ‘alpha’=>60, ‘skip’=>0 ,’orientation’=>”vertical” );
$chart[ ‘axis_ticks’ ] = array ( ‘value_ticks’=>false, ‘category_ticks’=>false, ‘major_thickness’=>2, ‘minor_thickness’=>1, ‘minor_count’=>1, ‘major_color’=>”222222″, ‘minor_color’=>”222222″ ,’position’=>”centered” );
$chart[ ‘axis_value’ ] = array ( ‘font’=>”arial”, ‘bold’=>true, ‘size’=>10, ‘color’=>”000000″, ‘alpha’=>50, ‘steps’=>6, ‘prefix’=>””, ‘suffix’=>””, ‘decimals’=>0, ‘separator’=>””, ‘show_min’=>true );

$chart[ ‘chart_border’ ] = array ( ‘color’=>”000000″, ‘top_thickness’=>0, ‘bottom_thickness’=>0, ‘left_thickness’=>5, ‘right_thickness’=>0 );

$chart[ ‘chart_grid_h’ ] = array ( ‘alpha’=>10, ‘color’=>”000000″, ‘thickness’=>0 );
$chart[ ‘chart_grid_v’ ] = array ( ‘alpha’=>10, ‘color’=>”000000″, ‘thickness’=>20 );
$chart[ ‘chart_rect’ ] = array ( ‘x’=>50, ‘y’=>50, ‘width’=>540, ‘height’=>200, ‘positive_color’=>”ffffff”, ‘positive_alpha’=>30, ‘negative_color’=>”ff0000″, ‘negative_alpha’=>10 );
$chart[ ‘chart_type’ ] = “line”;

$chart[ ‘chart_data’ ] = array ( array ( “”, “2004”, “2005”, “2006”, “2007” ), array ( “region 1”, 48, 55, 80, 100 ), array ( “region 2”, -12, 10, 55, 65 ), array ( “region 3″, 27, -20, 15, 80) );

$chart[ ‘chart_value’ ] = array ( ‘prefix’=>””, ‘suffix’=>””, ‘decimals’=>0, ‘separator’=>””, ‘position’=>”cursor”, ‘hide_zero’=>true, ‘as_percentage’=>false, ‘font’=>”arial”, ‘bold’=>true, ‘size’=>12, ‘color’=>”000000″, ‘alpha’=>100 );
$chart[ ‘draw’ ] = array ( array ( ‘type’=>”text”, ‘color’=>”000033″, ‘alpha’=>25, ‘font’=>”arial”, ‘rotation’=>0, ‘bold’=>true, ‘size’=>30, ‘x’=>0, ‘y’=>5, ‘width’=>650, ‘height’=>295, ‘text’=>”Statistics”, ‘h_align’=>”right”, ‘v_align’=>”bottom” ) );
$chart[ ‘legend_label’ ] = array ( ‘layout’=>”horizontal”, ‘font’=>”arial”, ‘bold’=>true, ‘size’=>13, ‘color’=>”000000″, ‘alpha’=>50 );
$chart[ ‘legend_rect’ ] = array ( ‘x’=>25, ‘y’=>10, ‘width’=>600, ‘height’=>5, ‘margin’=>3, ‘fill_color’=>”ffffff”, ‘fill_alpha’=>10, ‘line_color’=>”000000″, ‘line_alpha’=>0, ‘line_thickness’=>0 );
$chart[ ‘series_color’ ] = array ( “ddaa41”, “88dd11”, “4e62dd”, “ff8811”, “8A2BE2”, “FF1493”, “F0E68C”, “DDA0DD”,”8B4513″,”FFFF00″,”FF6347″,”708090″,”B0E0E6″,”808000″, “20B2AA”, “FF8C00”, “338833” );
$chart[ ‘series_gap’ ] = array ( ‘bar_gap’ => 0, ‘set_gap’ => 35 );

SendChartData ($chart);
}

You can copy paste this part from maani.us graph examples according to your graph choice. Just copy the first line

$this->load->helper(“graph”);

when you run the changes, you will see that the graphs are generated and it is shown in the view as expected. I have shown how to put a chart in a variable and pass it to view. It is a good way to show more than one graph if required.

I hope that shows how easily you can add a cool featured graph tool in your Code Igniter application. Let me know any comments, questions regarding Code Igniter and PHP.

GMAIL new version is painfully slow!!!

Recently GMAIL released its new version and using for few days i found it really annoying as the system is very slow compared to the previous version. So, I have shift back to the old version. Few of the problems of the new GMAIL:

1. very slow in loading in both IE and Firefox.

2. worst with firebug enabled in firefox (GMAIL’s known issue)

3. Anchors in Email page does not work. It opens in a new page and huh!! the content is not loaded. I found it completely irritating. I have to scroll my mails to read that particular section.

4. Another annoying fact is if you are using older version and login next time,  you are logged into the new version. Kind of enforcement!!!!

Usually people goes for performance improvement everyday. Seems that Google taking a step backward. Hope they will solve the issues asap.

I forgot to mention, GMAIL is still in Beta 😉