Windows Live Writer

I installed Windows Live Writer today to see how it works with my WordPress blog. So, this is a test post using Windows Live Writer. I’ll post a new blog entry if I find any useful feature of it. Well it has one good feature at least. To add a Bing map to your blog entry. See this one 🙂

Map picture

Meanwhile you can download it using Windows Live Essentials, and read more about Windows Live Writer at Wikipedia.

Using Response.Redirect in try…catch block

Using Response.Redirect in a try catch block will throw an exception “Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.”.

To work around use the overloaded method Response.Redirect(String url, bool endResponse) and pass false to second argument. The detailed reason can be found at http://support.microsoft.com/kb/312629/EN-US/.

REST Web Services calls with C#

Its really easy to call REST based web services from C#.Net. Let’s see how to do it. We’ll be calling Yahoo Web Services as an example here.

Make REST Calls With C#

The .NET Framework provides classes for performing HTTP requests. This HOWTO describes how to perform both GET and POST requests.

Overview

The System.Net namespace contains the HttpWebRequest and HttpWebResponse classes which fetch data from web servers and HTTP based web services. Often you will also want to add a reference to System.Webwhich will give you access to the HttpUtility class that provides methods to HTML and URL encode and decode text strings.

Yahoo! Web Services return XML data. While some web services can also return the data in other formats, such as JSON and Serialized PHP, it is easiest to utilize XML since the .NET Framework has extensive support for reading and manipulating data in this format.

Simple GET Requests

The following example retrieves a web page and prints out the source.

C# GET SAMPLE 1


using System;
using System.IO;
using System.Net;
using System.Text;
// Create the web request
HttpWebRequest request = WebRequest.Create(“http://developer.yahoo.com/”) as HttpWebRequest;
// Get response
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
// Get the response stream
StreamReader reader = new StreamReader(response.GetResponseStream());
// Console application output
Console.WriteLine(reader.ReadToEnd());
}

Simple POST Requests Some APIs require you to make POST requests. To accomplish this we change the request method and content type and then write the data into a stream that is sent with the request. C# POST SAMPLE 1

// We use the HttpUtility class from the System.Web namespace
using System.Web;
Uri address = new Uri("http://api.search.yahoo.com/ContentAnalysisService/V1/termExtraction");
// Create the web request
HttpWebRequest request = WebRequest.Create(address) as HttpWebRequest;
// Set type to POST
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
// Create the data we want to send
string appId = "YahooDemo";
string context = "Italian sculptors and painters of the renaissance"
+ "favored the Virgin Mary for inspiration";
string query = "madonna";
StringBuilder data = new StringBuilder();
data.Append("appid=" + HttpUtility.UrlEncode(appId));
data.Append("&context=" + HttpUtility.UrlEncode(context));
data.Append("&query=" + HttpUtility.UrlEncode(query));
// Create a byte array of the data we want to send
byte[] byteData = UTF8Encoding.UTF8.GetBytes(data.ToString());
// Set the content length in the request headers
request.ContentLength = byteData.Length;
// Write data
using (Stream postStream = request.GetRequestStream())
{
    postStream.Write(byteData, 0, byteData.Length);
}
// Get response
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
    // Get the response stream
    StreamReader reader = new StreamReader(response.GetResponseStream());
    // Console application output
    Console.WriteLine(reader.ReadToEnd());
}

HTTP Authenticated requests

The del.icio.us API requires you to make authenticated requests, passing your del.icio.us username and password using HTTP authentication. This is easily accomplished by adding an instance ofNetworkCredentials to the request.

C# HTTP AUTHENTICATION

// Create the web request
HttpWebRequest request
= WebRequest.Create("https://api.del.icio.us/v1/posts/recent") as HttpWebRequest;
// Add authentication to request
request.Credentials = new NetworkCredential("username", "password");
// Get response
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
    // Get the response stream
    StreamReader reader = new StreamReader(response.GetResponseStream());
    // Console application output
    Console.WriteLine(reader.ReadToEnd());
}

Error Handling

Yahoo! offers many REST based web services but they don’t all use the same error handling. Some web services return status code 200 (OK) and a detailed error message in the returned XML data while others return a standard HTTP status code to indicate an error. Please read the documentation for the web services you are using to see what type of error response you should expect. Remember that HTTP Authentication is different from the Yahoo!Browser-Based Authentication.

Calling HttpRequest.GetResponse() will raise an exception if the server does not return the status code 200 (OK), the request times out or there is a network error. Redirects are, however, handled automatically.

Here is a more full featured sample method that prints the contents of a web page and has basic error handling for HTTP error codes.

C# GET SAMPLE 2

public static void PrintSource(Uri address)
{
    HttpWebRequest request;
    HttpWebResponse response = null;
    StreamReader reader;
    StringBuilder sbSource;
    if (address == null) { throw new ArgumentNullException("address"); }
    try
    {
        // Create and initialize the web request
        request = WebRequest.Create(address) as HttpWebRequest;
        request.UserAgent = ".NET Sample";
        request.KeepAlive = false;
        // Set timeout to 15 seconds
        request.Timeout = 15 * 1000;
        // Get response
        response = request.GetResponse() as HttpWebResponse;
        if (request.HaveResponse == true && response != null)
        {
            // Get the response stream
            reader = new StreamReader(response.GetResponseStream());
            // Read it into a StringBuilder
            sbSource = new StringBuilder(reader.ReadToEnd());
            // Console application output
            Console.WriteLine(sbSource.ToString());
        }
    }
    catch (WebException wex)
    {
        // This exception will be raised if the server didn't return 200 - OK
        // Try to retrieve more information about the network error
        if (wex.Response != null)
        {
            using (HttpWebResponse errorResponse = (HttpWebResponse)wex.Response)
            {
                Console.WriteLine(
                "The server returned '{0}' with the status code {1} ({2:d}).",
                errorResponse.StatusDescription, errorResponse.StatusCode,
                errorResponse.StatusCode);
            }
        }
    }
    finally
    {
        if (response != null) { response.Close(); }
    }
}

Further reading

Related information on the web.

XML with C#

There is an excellent support for XML in Microsoft .Net framework, and there is very good and easy article on it at Yahoo Developer Network.

I am pasting its content here for convenience. Enjoy 🙂

Using Returned XML with C#

Once you have retrieved data from a web service you will need to do something with it. This HOWTO describes the various built-in methods .NET provides to use XML returned by a web service.

Overview

The .NET Framework provides excellent support for XML. Combined with the databinding support of WinForms and ASP.NET applications you have an easy and powerful set of tools. ASP.NET 2.0 takes databinding another step further by providing the DataSource control which lets you declaratively provide data access to data-bound UI controls.

Returned Data to a String

The simplest way to view the returned data is to get the response stream and put it into a string. This is especially handy for debugging. The following code gets a web page and returns the contents as a string.

C# STRING SAMPLE

public class StringGet { public static string GetPageAsString(Uri address) { string result = ""; // Create the web request HttpWebRequest request = WebRequest.Create(address) as HttpWebRequest; // Get response using (HttpWebResponse response = request.GetResponse() as HttpWebResponse) { // Get the response stream StreamReader reader = new StreamReader(response.GetResponseStream()); // Read the whole contents and return as a string result = reader.ReadToEnd(); } return result; } }

USING XMLREADER

XmlReader provides fast forward-only access to XML data. It also allows you to read data as simple-typed values rather than strings. XmlReader can load an XML document without having to use HttpRequest, though you won’t have the same amount of control over the request. If you use HttpRequest, you can just pass the stream returned by the GetResponseStream() method to XmlReader. Fast write-only functions are provided byXmlTextWriter.

With .NET 2.0 you should create XmlReader instances using the System.Xml.XmlReader.Create method. For the sake of compatibility and clarity the next sample uses the .NET 1.1 creation method.

C# XMLREADER SAMPLE

using System.Xml;
// Retrieve XML document
XmlTextReader reader = new XmlTextReader("http://xml.weather.yahoo.com/forecastrss?p=94704");
// Skip non-significant whitespace
reader.WhitespaceHandling = WhitespaceHandling.Significant;
// Read nodes one at a time
while (reader.Read())
{
	// Print out info on node
	Console.WriteLine("{0}: {1}", reader.NodeType.ToString(), reader.Name);
}

USING XMLDOCUMENT

XmlDocument gives more flexibility and is a good choice if you need to navigate or modify the data via the DOM. It also works as a source for the XslTransform class allowing you to perform XSL transformations.

C# XMLDOCUMENT SAMPLE

// Create a new XmlDocument
XmlDocument doc = new XmlDocument();
// Load data
doc.Load("http://xml.weather.yahoo.com/forecastrss?p=94704");
// Set up namespace manager for XPath
XmlNamespaceManager ns = new XmlNamespaceManager(doc.NameTable);
ns.AddNamespace("yweather", "http://xml.weather.yahoo.com/ns/rss/1.0");
// Get forecast with XPath
XmlNodeList nodes = doc.SelectNodes("/rss/channel/item/yweather:forecast", ns);
// You can also get elements based on their tag name and namespace,
// though this isn't recommended
//XmlNodeList nodes = doc.GetElementsByTagName("forecast",
//                          "http://xml.weather.yahoo.com/ns/rss/1.0");
foreach(XmlNode node in nodes)
{
	Console.WriteLine("{0}: {1}, {2}F - {3}F",
	node.Attributes["day"].InnerText,
	node.Attributes["text"].InnerText,
	node.Attributes["low"].InnerText,
	node.Attributes["high"].InnerText);
}

Using XPathNavigator/XPathDocument

XPathDocument provides fast, read-only access to the contents of an XML document using XPath. Its usage is similar to using XPath with XmlDocument.

C# XPATHDOCUMENT SAMPLE

using System.Xml.XPath;
// Create a new XmlDocument
XPathDocument doc = new XPathDocument("http://xml.weather.yahoo.com/forecastrss?p=94704");
// Create navigator
XPathNavigator navigator = doc.CreateNavigator();
// Set up namespace manager for XPath
XmlNamespaceManager ns = new XmlNamespaceManager(navigator.NameTable);
ns.AddNamespace("yweather", "http://xml.weather.yahoo.com/ns/rss/1.0");
// Get forecast with XPath
XPathNodeIterator nodes = navigator.Select("/rss/channel/item/yweather:forecast", ns);
while(nodes.MoveNext())
{
	XPathNavigator node = nodes.Current;
	Console.WriteLine("{0}: {1}, {2}F - {3}F",
	node.GetAttribute("day", ns.DefaultNamespace),
	node.GetAttribute("text", ns.DefaultNamespace),
	node.GetAttribute("low", ns.DefaultNamespace),
	node.GetAttribute("high", ns.DefaultNamespace));
}

Using a DataSet

Using a DataSet from the System.Data namespace lets you bind the returned data to controls and also access hierarchical data easily. A dataset can infer the structure automatically from XML, create corresponding tables and relationships between them and populate the tables just by calling ReadXml().

C# DATASET SAMPLE

using System.Data;
public void RunSample()
{
	// Create the web request
	HttpWebRequest request
	= WebRequest.Create("http://xml.weather.yahoo.com/forecastrss?p=94704") as HttpWebRequest;
	// Get response
	using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
	{
		// Load data into a dataset
		DataSet dsWeather = new DataSet();
		dsWeather.ReadXml(response.GetResponseStream());
		// Print dataset information
		PrintDataSet(dsWeather);
	}
}
public static void PrintDataSet(DataSet ds)
{
	// Print out all tables and their columns
	foreach (DataTable table in ds.Tables)
	{
		Console.WriteLine("TABLE '{0}'", table.TableName);
		Console.WriteLine("Total # of rows: {0}", table.Rows.Count);
		Console.WriteLine("---------------------------------------------------------------");
		foreach (DataColumn column in table.Columns)
		{
			Console.WriteLine("- {0} ({1})", column.ColumnName, column.DataType.ToString());
		}  // foreach column
		Console.WriteLine(System.Environment.NewLine);
	}  // foreach table
	// Print out table relations
	foreach (DataRelation relation in ds.Relations)
	{
		Console.WriteLine("RELATION: {0}", relation.RelationName);
		Console.WriteLine("---------------------------------------------------------------");
		Console.WriteLine("Parent: {0}", relation.ParentTable.TableName);
		Console.WriteLine("Child: {0}", relation.ChildTable.TableName);
		Console.WriteLine(System.Environment.NewLine);
	}  // foreach relation
}

Further reading

Related information on the web is listed below.

MySQL Installation and Configuration

Since I have been involved with MySQL development for last several years, so I constantly look for good resources related to MySQL. I found a very good article on this at IIS site (http://learn.iis.net/page.aspx/610/walkthrough—set-up-mysql-51-for-php-applications/). I thought I should refer that to my blog. I am providing whole article as it is here.

Overview

This article provides a basic step by step guide on how to install and configure MySQL on the Windows Operating System. For more detailed instructions about installing and configuring MySQL on Windows refer to the official MySQL documentation.

Downloading and Installing MySQL

The MySQL binaries and installer can be downloaded from the official MySQL site. The instructions in this article are based on MySQL version 5.1 Community Edition installed with the Windows MSI installer.

Run the installer and choose the installation option. For a majority of the cases, the typical installation is sufficient:

However, if you want to control which components get installed or if you want to use a non-default installation path then choose the “Custom” option.

When the installation is complete, make sure to check the box to “Configure the MySQL Server now”. This will launch the “MySQL Server Instance Configuration Wizard” that will guide you through the configuration process for the MySQL instance.

Configuring MySQL instance

Follow these steps in the “MySQL Server Instance Configuration Wizard” to optimize the MySQL configuration for the kind of tasks you expect it to perform.

On the first page of the wizard choose “Detailed Configuration”:

On the next page choose the server type option:

Choose the “Database Usage” option:

The Database usage options control what kind of database storage engine is used on the server:

  • MyISAM – Optimized for high performance SELECT operations. It has low overhead in terms of memory usage and disk utilization, but at the cost of not supporting transactions
  • InnoDB – Provides fully ACID transactional capabilities, but at the cost of more aggressive usage of disk space and memory

For an in-depth comparison of these database engines, refer to MySQL Storage Engine Architecture. As a general recommendation – if the web applications on your server require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features — use InnoDB. Otherwise, use MyISAM.

Next choose the number of concurrent connections to the server:

On the next page choose networking options :

If you have mysql and web server on the same machine you may consider not enabling TCP/IP networking and instead use named pipes. Note though that some PHP applications may require TCP connection to MySQL. Refer to the application’s documentation to confirm if it supports named pipes connection to MySQL.

Choose the default charset to use when creating new databases:

Next ensure that MySQL will be configured as a Windows Service:

Optionally, you can add the MySQL Bin directory to the Windows PATH environment variable. That will make it easier to launch MySQL tools from the command line.

Finally provide the password for the database administrative account, which in called “root” in MySQL. Make sure that you leave the “Create an Anonymous Account” checkbox cleared:

On the next page click “Execute” to apply all the configuration settings and to start the MySQL service:

Now you can logon to MySQL by opening a command line window and typing:

mysql -u root -p
Enter password: ******If MySQL was configured correctly then the MySQL prompt will be shown:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server Version 5.1.32-community MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

XMS.Net Consumer Application for IBM WebSphere MQ

Currently we are working on IBM WebSphere MQ to be used in our project. One feature that we need from several features of WebSphere MQ was to subscribe to a topic with selector string from a C#.Net application. IBM provides two libraries for .Net. One is MQI classes for .Net and another is XMS.Net (IBM Message Service Client for .Net). Finally we adopted XMS.Net, although we also tried MQI classes for .Net, but we moved to XMS.Net. Its totally a different story why we moved to XMS.Net. I might write another post on it 🙂

So, at first place we tried to run sample consumer application came with XMS.Net library. Our center of the focus was selector string. When we were trying to run the sample consumer application and supplied the topic to subscribe to and selector string to filter the messages, it was getting stuck every time after subscription. Even it wasn’t receiving any messages, although we could see the subscription object being created on the WMQ server. One thing that we were doing was that we were running the sample in ‘client’ mode. When we switched the mode to ‘unmanaged’, everything ran smoothly. We were bit surprised! Why it wasn’t running in client mode? I think there is some bug, since I couldn’t find anything related in the docs. Might IBM will fix this issue in the future release.  But at last, somehow we managed to run the sample consumer application and finally we are at the stage where we can move forward.

So, if you are also trying to run XMS.Net application and want to subscribe to some topic with selector string to filter messages, remember to run the application in ‘unmanaged’ mode. You can find further information in XMS.Net doc (ftp://ftp.software.ibm.com/software/integration/support/supportpacs/individual/ia9h.pdf).

How to setup PHP development environment in Eclipse?

As I am currently working on Joomla (http://www.joomla.org/) a CMS built in PHP, I came across a very nice tutorial about how to setup your development environment if you want to work on Joomla. Although the tutorial is specific to Joomla, PHP, and Eclipse, but in general it is a very good tutorial and one can learn how to setup a development environment for any project irrespective of technology used. Especially novice programmers will learn a lot from it.

Author first explained how to install and configure XAMPP, then configure PHP and XDebug to debug the PHP applications. Then author explained how to install Eclipse, setup your workspace, configure it for debugging. Then author created a test project and ran it in debug mode. Then in the end author explained how to install and configure subclipse (Eclipse SVN plug-in) and how to import a project from SVN repository.

This is a very nice tutorial in general and specifically for those who work in Eclipse and PHP. Wanna read it? go ahead: http://docs.joomla.org/Setting_up_your_workstation_for_Joomla!_development

CVS account change in Eclipse

We were setting up new development environment for our team using eclipse and checked out the code from CVS. At the end we faced an issue when we tested how other developers will change the CVS account to their own one. Eclipse was not allowing us to change the CVS account. Then I found that we have to change the account in CVS meta files. The command I used to replace the account was :

find . -regex .*CVS/Root -print0 | xargs -0 perl -p -i.orig -e “s/olduser/newuser/;”

This replaced the CVS user name in all CVS meta files in the whole project hierarchy 🙂

Database indexes and locks

I was discussing with my friend on the issue I have discussed in my post Referential Integrity (http://mjawaid.wordpress.com/2009/04/01/referential-integrity/) and Mapping tables (http://mjawaid.wordpress.com/2009/04/02/mapping-tables/). My friend told me the scenario that when we create multiple indexes on the table we will get deadlocks. The reason he told me was the bucket lock, or in other words gap lock. Actually what server does is that due to multiple indexes when you lock any one record it locks multiple records, since it searches indexes and all the records it encounters during search, it locks them. That what I understood he was trying to say. I wasn’t convinced and thought of trying to create few tables with indexes and test them. For test I used MySQL4 and InnoDB engine, since the issue we were facing was on it.

So I created five tables indxtest1, indxtest2, indxtest3, indxtest4, and indxtest5.

CREATE TABLE `indxtest1` (

`id` bigint(20) NOT NULL auto_increment,

`name` varchar(255) default NULL,

`fk` bigint(20) default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

CREATE TABLE `indxtest2` (

`id` bigint(20) NOT NULL auto_increment,

`name` varchar(255) default NULL,

`fk` bigint(20) default NULL,

PRIMARY KEY (`id`),

KEY `NewIndex1` (`fk`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

CREATE TABLE `indxtest3` (

`id` bigint(20) NOT NULL auto_increment,

`name` varchar(255) default NULL,

`fk` bigint(20) default NULL,

PRIMARY KEY (`id`),

KEY `NewIndex1` (`id`,`fk`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

CREATE TABLE `indxtest4` (

`id` bigint(20) NOT NULL auto_increment,

`name` varchar(255) default NULL,

`fk` bigint(20) NOT NULL default ‘0’,

PRIMARY KEY (`id`,`fk`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

CREATE TABLE `indxtest5` (

`id` bigint(20) NOT NULL auto_increment,

`name` varchar(255) default NULL,

`fk` bigint(20) default NULL,

PRIMARY KEY (`id`),

KEY `FK_indxtest4` (`fk`),

CONSTRAINT `FK_indxtest4` FOREIGN KEY (`fk`) REFERENCES `indxtest5parent` (`id`) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

And another table indxtest5parent as parent for indxtest5.

CREATE TABLE `indxtest5parent` (

`id` bigint(20) NOT NULL auto_increment,

`test` varchar(255) default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Let me explain what is the difference between the tables. All tables except the indxtest5parent contain three fields: id, name, and fk. Don’t confuse fk with a foreign key. Id is the primary key in all tables. The main difference between the tables is the indexing on the fk field.

The indxtest1 has no index on fk.

The indxtest2 has an index on fk, so fk is indexed.

The indxtest3 has a multi-field-index on id and fk combined, in addition to primary key index on id.

The indxtest4 has a composite primary key id, fk. Therefore there is a primary key index on id and fk i.e multi-field-index.

The indxtest5 has a foreign key fk mapping to id field of indxtest5parent. So it has foreign key index on fk.

After that I inserted some data in these tables, around just 10 records. I inserted that few records since I wasn’t testing performance with huge data, instead I was just testing that how records are searched in table with indexes, multiple indexes, and without indexes, which is useful in knowing how records are locked, implicitly when updating or explicitly.

So all tables look almost like this after inserting data:

id

name

fk

1

One

10

2

Two

12

3

three

13

4

Four

14

5

Five

15

6

Six

15

7

seven

14

8

eight

13

9

Nine

12

10

Ten

10

Now run the following queries on all tables:

select * from indxtest1 where id = 1;

select * from indxtest2 where id = 1;

select * from indxtest3 where id = 1;

select * from indxtest4 where id = 1;

select * from indxtest5 where id = 1;

These all queries will result in same output, i.e the first record of the table. This is very simple, and since result was filtered using the primary key in the where clause so it scanned only one record during search. We can see this by running following queries:

explain select * from indxtest1 where id = 1;

explain select * from indxtest2 where id = 1;

explain select * from indxtest3 where id = 1;

explain select * from indxtest4 where id = 1;

explain select * from indxtest5 where id = 1;

You will notice that in result the rows column will show 1, that means only one record was scanned during the search.

Now let’s filter the result using the fk field in the where clause:

select * from indxtest1 where fk = 10;

select * from indxtest2 where fk = 10;

select * from indxtest3 where fk = 10;

select * from indxtest4 where fk = 10;

select * from indxtest5 where fk = 10;

All these queries will return the same result, two records with id in 1 and 10. But how many records were scanned during search? To find out the answer run the following queries:

explain select * from indxtest1 where fk = 10;

explain select * from indxtest2 where fk = 10;

explain select * from indxtest3 where fk = 10;

explain select * from indxtest4 where fk = 10;

explain select * from indxtest5 where fk = 10;

In the rows column you will notice that for the indxtest1 table it scanned 10 records. That is reasonable since there was no indexing. Now let’s see for indxtest2 table, 2 records were scanned. This is also reasonable since fk was indexed. So far so good. Now for indxtest3 table 10 records were scanned. Hmm… ok we will discuss this in a moment. Let’s check other queries first. For indxtest4 table 10 records were scanned, and for indxtest5 table only 2 records were scanned. Result for indxtest5 table is also reasonable since it has a foreign key index on it.

Now what are the cases with indxtest3 and indxtest4? If you notice both tables have a multi-column index. According to MySQL documentation:

MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns. (http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html)

It is clearly stated in the documentation that when second column is specified, MySQL will not use the index, or even if second column is used with first column with OR condition, it will not use the index. That’s why queries on indxtest3 and indxtest4 scanned all 10 records during search/select.

Now what is the effect of indexing on locking? According to MySQL documentation:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. (http://dev.mysql.com/doc/refman/4.1/en/innodb-locks-set.html)

And what is record lock?

Record lock: This is a lock on an index record. (http://dev.mysql.com/doc/refman/4.1/en/innodb-record-level-locks.html)

Few more points from MySQL documentation (http://dev.mysql.com/doc/refman/4.1/en/innodb-locks-set.html):

1 – For SELECT ... FOR UPDATE or SELECT ... IN SHARE MODE, locks are acquired for scanned rows.

2 – SELECT ... FROM ... FOR UPDATE sets exclusive next-key locks on all index records the search encounters.

3 – UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

4 – DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

Conclusion

So, according to MySQL documentation, either we are explicitly locking records (first two points) or locks are implicit (last two points), locks will be acquired on records the search encounters. So if indexing is proper no locks will be acquired on rows on which we don’t want to. Even MySQL documentation says that:

It is important to create good indexes so that your queries do not unnecessarily need to scan many rows. (http://dev.mysql.com/doc/refman/4.1/en/innodb-locks-set.html)

Scanning many records will result in lock on those records due to which deadlocks can occur, performance can be degraded, or anything bad can happen.

If you are going to test the above scenario then you will also notice the performance differences between the queries if you have reasonable number of records in tables.

Some coding tips!

This article highlights some common pitfalls that are being made by developers. I’ll elaborate it with a simple example.

$obj2->FuncOne( $obj1->GetData() );
$obj2->FuncTwo( $obj1->GetData() );

The result of GetData is passed to FuncOne and FuncTwo in two subsequent calls. What is the benefit of this approach? Simple answer is to save memory, by not using any variable to save the result. This argument was very strong until there were memories with very less capacity. But now even home user has a system with memory in GBs. So this practice is not good in today’s world.

So, what’s the drawback of this approach? Performance degradation. But how can it degrades the performance? Let me explain a bit. Consider a situation where GetData runs a query on database to fetch some data from multiple tables by joining them. Joins thereselves are heavy by nature. So when GetData is called twice it will run query twice, and suppose that this code snippet is a part of a heavy process that can be called by multiple users on the web or in an enterprise application, just imagine what will happen to the database and application itself. The performance of the application will be degraded. Users of your application will get frustrated and at the end you will lose business.

Now let’s look at it from another perspective. This approach will also increase the CPU workload. When GetData will be called it will jump from one branch instruction to another and before that it has to save the current address to the stack so it can pop it back when it returns back to the caller function. This has to be done every time when function is called. And when function performs heavy computation it needs more memory and processing power, increasing the footprint of your application and execution time. So you’re wasting your servant’s (CPU) time and energy by assigning it the same task twice.

Other drawbacks can be that code is more error prone and is difficult to debug and troubleshoot, and code maintenance is high, especially when you have to modify the code to meet new requirements.

You can make your application and code much better and efficient by adhering to few simple best practices. In this case the rule is that

“If result of a function is needed more than once then don’t call that function multiple times. Save the result of that function in a variable and use that variable instead. “

In view of this, above code could be written like this.

my $result = $obj1->GetData();
$obj2->FuncOne( $result );
$obj2->FuncTwo( $result );

There is one extra line of code in above example but is more efficient and is much more readable than the previous one.

Let’s see another example.

my $result = $obj1->GetData();
$result = { %$result, %$someData };
$obj2->FuncOne( $result );
$obj2->FuncTwo( $obj1->GetData() );

In this example developer fetches the result, appends another previously got data to it and passes it to FuncOne. Then he needs the same result, so he called GetData one more time. Now again, we can write this code in much efficient manner.

my $result = $obj1->GetData();
my $result2 = $result;

$result = { %$result, %$someData };

$obj2->FuncOne( $result );
$obj2->FuncTwo( $result2 );

Here we have saved another call to GetData, which might be performing some heavy computation or running a heavy query with joining multiple tables in it.