Performance Guide to Tune your Application and db4o Database for Report Generation

Written on Monday, January 14, 2008 by Edwin Sanchez

Reports drive businesses to be better. Behind those humongous data are processes that will need to generate summaries, listings, graphs and more. In addition to what we know on how to supercharge our db4o database to top speed, below are the two main questions I ask myself when I am designing my application using db4o database for speed of generating reports:

• Will the report be produced real-time or not?
  
  This is a question I ask my users before I finalize the data source of the report. If this will be a periodic report (weekly, monthly, etc.), what I find the best design strategy for speed is to persist only the needed information in the particular report in one object, have an automated process run at night (or weekends depending on the requirement) to update the data. When reporting comes, my application will query the summarized data based on user criteria. This way, the user will perceive a faster generation of report based on lots of data. However, if the report is needed on demand real-time based on the latest data, that’s the only way I will need to query directly from the raw objects.
  
• Will the volume of raw data be very large?
  
  Upon initial design stage of a system, I try to find out if the volume of data can be very large that querying it whether native queries or SODA will still be slow and unacceptable to the user. When I perceive this to be the case, I will design my db4o database into separate files. This will increase processing and loading time since the data store is smaller. (Designing which goes to what file is something to be taken seriously, though. It can make things better or worst depending on the design) One example is when you need to generate a monthly report out of humongous raw objects. You can design separate files each based by month and year. If separate monthly files are your way to go, I have found using the following to be effective in my case:
  
  FlushFileBuffers(false) – “Wait a minute! Isn’t this considered a dangerous practice?” Yes, it is. This is included in the Dangerous Practices in the Reference Documentation. You may not agree with me but here’s the idea why. The monthly report files are generated separately each month. So a damage one will not affect the other. Added to that, when it gets corrupted due to some factors, you can still reproduce it by triggering the data processing for the month and year concerned.
  Enable Field Indexes on Key Fields – This should be clear why. This will increase performance when you retrieve your summary data later. But remember, just the key fields only. You may degrade processing performance by adding unnecessary indexes.
  Disable Query Evaluation on Fields Not Used for Querying – as the documentation states, all fields are evaluated by default. Specifying which of the fields will not be evaluated will increase performance
  Unicode(false) – if your report is in plain English and not using double byte characters, turn Unicode support off. This is on by default.
  Query Evaluation Mode to SnapShot – after processing has taken place, it’s time for the users to view the output. Snapshot is best for the client-server setting if your memory.
  Blocksize(8) – I have found following this recommended setting increases performance. Changing it to a higher value makes processing slower.
  When Processing, Open or Create the File Using OpenFile. Use OpenServer to Open it later for shared use – Single-user access is still the fastest when processing data. And since the user doesn’t need the output at processing time, OpenFile will be just right. When the time comes that the users need to view the report in your application, that’s the only time this will be hosted using OpenServer.
  
  Separating data in different files will simplify my query unlike one database for everything. And therefore will increase the performance. A more complex query will more likely to run slowly. Backing up the monthly data is also simpler in this setup. And chances of reaching the database file size limit will be low.
  

I have presented here my general considerations in this type of scenario which is reporting. There are other factors to consider that may only be applicable to your situation. I hope this general guide will help you.

Posted in Databases, db4o, Performance, Programming | Comment Now!

Invoking Web Services the Better Way

Written on Wednesday, January 02, 2008 by Edwin Sanchez

Web services are around for some time and it has been supported by lots of languages and development tools, including Java and the .Net languages. In .Net, invoking web service methods is as easy as calling your own methods from a class library. There are requirements, though, before you can invoke any web service method. Just like your class library, you need to create a reference to the web service from the invoking client application. In .Net, this is a web reference. After that, you can declare any variable for your web service, like the one below:

MyWebService service = new MyWebService();

Calling it is also simple:

returnedvalue = service.MethodName();

In .Net 2.0, Microsoft has added support for the new multithreaded programming with event-based asynchronous pattern when generating the proxy code by the WSDL tool. This opens a new way of invoking web services, better and faster. And this allows the programmer to easily utilize this feature without creating complex multithreading code. In the MSDN magazine, it has been pointed out that many web sites that utilize ASP.Net are not making use of asynchronous web service calls and in the end, experience the “Server Unavailable” error when lots of processes have consumed the thread pool. In order to conserve this thread pool, you need to call web services asynchronously. In my last post, I have pointed out that this is one of the design considerations when developing applications using web services.

Implementing it is easy. You just need to add little code. The first thing to do is to add the Async attribute to the @Page directive to allow asynchronous calls:

<%@ Page Language="C#" Async="true" %>

And then, depending on your requirements, you can utilize what event you need. What I have tried is to use an event handler for the Completed event of my proxy. Below is an example:

service.MethodNameCompleted += new WebServiceProxy.MethodNameCompletedEventHandler(service_MethodNameCompleted);

The Completed is automatically generated by the WSDL tool where the methodname is the name of the method you are trying to invoke. When the method you call has completed its execution, this event will be triggered so whatever things you need to do after the web method call (such as binding data sources to controls) should be coded in this event.

Lastly, you call the Async where again, the methodname is the name of the method in the web service that you are trying to call. When this is invoked, it will return immediately, allowing you to perform further operations.

returnedvalue = service.MethodNameAsync(params);

That’s the additional code you need to make call web services asynchronously. It is not so much of a big deal to add and the advantages outweigh the disadvantages.

When is this applicable to use?

• When you perform time-consuming tasks such as database operations (like a native query or SODA returning a large object set)
• When you need to execute multiple operations simultaneously
• When you need to wait for resources without “hanging” your application.

• Design for Performance: Implement Web Services the Better Way
  

Posted in ASP.Net, C#, Performance, Programming, Web Services | Comment Now!

Design for Performance: Implementing Web Services Better

Written on Sunday, December 30, 2007 by Edwin Sanchez

In a distributed environment where different servers contribute for the processing of a certain task, good design is essential. One point of consideration is your database. If you have a db4o database and you have designed it well, it will give you the expected results. However, something can still slow down your application even if you designed your database well. What else could go wrong? Your middle-tier is another point of consideration. Are you using web services? Is it waiting too long because of a large database query? Let’s consider some design guidelines that can take you to maximum warp.

• Consider asynchronous calls.
  If you are doing large queries that can let your calling application wait for a long time, this is a must. I will explain this further on the next post.
• Group different information into one when making web service calls
  You may have data that you need to display in one or more dropdown lists, an existing transaction that comprise several objects, and any other information that you need to query from the database. Depending on your needs, you can reduce the number of calls to a web service just to return all of the objects you need.
• Cache the results of the web service call
  Output caching is one of the cool things in ASP.Net. How does it work for web services? Suppose you call a web service that returns true if a user is a valid user in your application. You can cache the result of the web service in a specified time and when the same web service call with the same parameters is invoked again, instead of executing it, ASP.Net will get the results from the cache. This improves performance. Implementation is simple:

[WebMethod(CacheDuration=60)] public bool IsUserAuthentic(string userId, string password)

You just need to add the CacheDuration web method attribute and give it a time period in seconds to remain in the cache. In our example, the result will remain in the cache for 60 seconds. Take note, however, not to abuse this feature. This is really cool, I agree. But using too much of this, specially caching large object sets for a long time can be trouble. When caching results, ASP.Net stores it in server memory. It remains in memory for the specified cache duration. If you also have service calls that vary most of the time, it is not a good candidate for caching.

The next time you find bottlenecks in your web services, assess the problem by considering the performance guidelines above.

Posted in .Net, ASP.Net, C#, Performance, Programming, Web Services | Comment Now!

Importing External Data to db4o

Written on Monday, December 24, 2007 by Edwin Sanchez

From time to time, we may experience requirements involving exporting data from one source to another. Today, I will show you how an external data can be exported in db4o.

The following technologies will be used:
· Db4o 6.4
· .Net 2.0 using C#

This post will discuss the following:
· Reading a text file using StreamReader
· Extracting data from a line of record into db4o objects
· Use List Find method to check for existence

Data can come from many different sources. It can be an XML file, Excel File, from an RDBMS, from a web service or simply from a text file. I’m going to show you today about reading a text file as our source data. First, let’s take a look at the format of our sample text file:

“Department””Position””Name””Salary”
“Information Technology””Team Leader””Jun Marfil””30000.00”
“Information Technology””Programmer””Dino De Guzman””25000.00”
“Human Resources””Benefits Supervisor””Shyrel Morco””15000.00”

From the data above, we can see that we need a department object, position object and employee object. The column delimiter is the pipe symbol () and it’s obvious that rows are separated by carriage return and line feed.

In order to process this data, we need to read each line one by one. Each line of record should be read based on the column delimiter, interpreted and then written to a db4o database.

Now, in order to read the loaded text file line by line, we need the StreamReader. To parse a line of record, we will use the Split string method to put the data in an array of string columns.

Before we jump into code, let’s define our classes.

public class Department
{
private string _name;

public Department(string name)
{
_name = name;
}

public string Name
get
{
return _name;
}
set
{
_name = value;
}
}

public class Position
{
private string _name;

public Position(string name)
{
_name = name;
}

public string Name
get
{
return _name;
}
set
{
_name = value;
}

}

public class Employee
{
private string _employeeName;
private double _salary;
private Department _department;
private Position _position;

public Employee(string name, double salary,
Department department,
Position position)
{
_name = name;
_salary = salary;
_department = department;
_position = position;
}

public string EmployeeName
get
{
return _employeeName;
}
set
{
_EmployeeName = value;
}

public double Salary
get
{
return _salary;
}
set
{
_salary = value;
}

public Department EmployeeDepartment
get
{
return _department;
}
set
{
_department = value;
}

public Position EmployeePosition
get
{
return _position;
}
set
{
_position = value;
}
}

Now let’s look into the part of our program of parsing text. We assume that the database is already open at this point.

private void ImportTextFile (IObjectContainer db)
{
string employeeName;
double salary;
string employeePosition;
string employeeDepartment;
string record[];
string rawRecord;
List<Position> positionList = new List<Position>();
List<Department> departmentList = new List<Department>();
Employee employee;

try
{
using (StreamReader sr = File.OpenText(“sample.txt”))
{
while ((rawRecord = sr.ReadLine()) != null)
{
// remove the quotes
rawRecord = rawRecord.Replace("\"", "");

// put into array
record = rawRecord.Split(‘"’);

//avoid the header line
if(record[0] == “Department”)
{
//do nothing
}
else
{ //get column values
employeeDepartment = record[0];
employeePosition = record[1];
employeeName = record[2];
salary = Convert.ToDouble(record[3]);
}
positionList = AddToList(positionList, employeePosition, db);
departmentList = AddToList(departmentList, employeeDepartment, db);

employee = new Employee(employeeName, salary, employeeDepartment, employeePosition);
db.set(employee);
}
db.Commit();
}
}
catch(Exception ex)
{
db.rollback();
throw new Exception(“Error processing text file”,ex);
}
finally
{
sr.Close();
db.Close();
}
}

private List<Position> AddToList(List<Position> list, string name,
IObjectContainer db)
{
Position position;

// search if the name being inserted is already in the list
position = list.Find(delegate(Position pos)
{
return pos.Name == name;
});

if (position == null)
{
position = new Position(name);
list.Add(position);
db.Set(position);
db.Commit();
}
return list;
}

private List<Department> AddToList(List<Department> list, string name,
IObjectContainer db)
{
Department department;

department = list.Find(delegate(Department dept)
{
return dept.Name == name;
});

if (department == null)
{
department = new Department(name);
list.Add(department);
db.Set(department);
db.Commit();
}
return list;
}



Now let’s recap. We have made 3 classes for our objects and it’s pretty straightforward. The method we define for processing the file used several objects worth our concern. We used the StreamReader to process our file and the Readline is the one we used reading the file line by line. We did not try to load all the lines in memory since this will become troublesome when our file is several MBs and GBs big. We used the Split string method in order to parse the string based on the pipe symbol delimiter and put it in an array. Then, we store each and every array element in a variable. Another notable method is the AddToList method. We used the generic List<t> instead of IList so we can make use of the Find method. We need this so we avoid inserting duplicate departments and positions. After parsing the values, we are now ready to save an Employee object in our database.

Our code is rather simple at this point. One point of improvement is to put some visuals to the user like a progress bar to indicate the status of the processing. Or instead of the AddToList method is to use db4o callbacks to check for duplicates prior to commit.

That’s how simple it is to import data from an external data source like a text file into db4o. I hope you find something useful in this post.

Posted in .Net, C#, Databases, db4o, Programming | Comment Now!

Things to Watch Out in db4o

Written on Monday, November 26, 2007 by Edwin Sanchez

Since I started my project using db4o, I get to encounter things that I was not accustomed to do. I used to do Visual Basic 6 on MS SQL Server, then VB.Net 2003 on the same database product and lastly C#(still on MS SQL Server). I would like to list the things out here that might help new entrants in the object-database world and C# with similar experience as mine:

1. db4o/C# is case-sensitive
I'm used to SQL Server's case-insensitiveness (although you can make it case-sensitive too by changing the settings. See SQL Server 2000 Books Online under the topic How to create a case-sensitive instance of SQL Server 2000 (Setup)). When comparing strings, take note of this or your going to have lunch and dinner dates with the debugger. So those migrating from VB/SQL tools, expect this since Java and C# are case-sensitive.

2. Get out of the relational mind-set
There are many posts on this so I won't go any further explaining.

3. Good performance depends on YOU
Some people may not agree with me but db4o may not perform properly if your code is slow. db4o is very fast as proved by the pole position survey. If you quickly jump to coding, process tons of objects and not reading the documentation on the best practices to ensure performance, then your code will crawl like a snail. I sometimes make my own mistakes by forgetting that performing connections and executing queries inside a while loop is a bad news. This is not in the docs but the db4o core team need not to put it there since this is a bad practice even in the relational world.

4. Missing Features?
Check the documentation or the community forum on specific things that you need. If it's not there, do a work around for a while and much better, contribute to the community if you have great ideas. Things will get better soon, and the core team is very agile. And this is open source. Features will get better by community contributions.

That's it for now. I'll try adding some more as I move on to my quest.

Good day to all

Posted in Databases, db4o, Programming | Comment Now!