Blog Archive for / 2007 / 08 /

Interview with Nick Hodges from Codegear

Friday, 31 August 2007

Over at Channel 9, Michael Lehmann from Microsoft has posted a video in which he and Bob Walsh interview Nick Hodges, the Delphi programme manager from Codegear. They discuss the future of Delphi, including support for Windows Vista, .NET 2.0 and WPF, as well as what makes Delphi good for small software companies.

For those looking to get to grips with Delphi, Nick recommends www.delphibasics.co.uk and www.marcocantu.com as resources well worth investigating.

Posted by Anthony Williams
[/ delphi /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Database Tip: Use Transactions

Monday, 27 August 2007

Here's another Database Tip, to follow on from my previous one on creating appropriate indexes. This time the focus is transactions.

For any experienced database developer, using transactions might seem an obvious suggestion, but lightweight databases may require configuration in order to use transactions. For example, MySql tables use the MyISAM engine by default, which doesn't support transactions — in order to use transactions you need to set the storage engine of your tables to InnoDB or BDB. Also, whereas in Oracle, every statement occurs within a transaction, and you need an explicit COMMIT or ROLLBACK to end the transaction, in databases such as MySQL and SQLite every statement is its own transaction (and thus committed to the database immediately) unless you explicitly begin a transaction with a BEGIN statement, or other database configuration command.

Benefits of Transactions

The primary benefit of using transactions is data integrity. Many database uses require storing data to multiple tables, or multiple rows to the same table in order to maintain a consistent data set. Using transactions ensures that other connections to the same database see either all the updates or none of them. This also applies in the case of interrupted connections — if the power goes off in the middle of a transaction, the database engine will roll back the transaction so it is as-if it was never started. If each statement is committed independently, then other connections may see partial updates, and there is no opportunity for automatic rollback on error.

A secondary benefit of using transactions is speed. There is often an overhead associated with actually committing the data to the database. If you've got 1000 rows to insert, committing after every row can cause quite a performance hit compared to committing once after all the inserts. Of course, this can work the other way too — if you do too much work in a transaction then the database engine can consume lots of space storing the not-yet-committed data or caching data for use by other database connections in order to maintain consistency, which causes a performance hit. As with every optimisation, if you're changing the boundaries of your transactions to gain performance, then it is important to measure both before and after the change.

Using Transactions in Oracle

Oracle databases are always in transaction mode, so all that's needed is to decide where to put the COMMIT or ROLLBACK. When one transaction is finished, another is automatically started. There are some additional options that can be specified for advanced usage — see the Oracle documentation for these.

    INSERT INTO foo (a,b) VALUES (1,'hello');
    INSERT INTO foo (a,b) VALUES (2,'goodbye');
    COMMIT;
    INSERT INTO foo (a,b) VALUES (3,'banana');
    COMMIT;

Using Transactions in SQLite

In SQLite, if you wish a transaction to cover more than one statement, then you must use a BEGIN or BEGIN TRANSACTION statement. The transaction ends when you execute a COMMIT or ROLLBACK statement, and the database reverts to auto-commit mode where each statement has its own transaction.

    BEGIN;
    INSERT INTO foo (a,b) VALUES (1,'hello');
    INSERT INTO foo (a,b) VALUES (2,'goodbye');
    COMMIT;
    INSERT INTO foo (a,b) VALUES (3,'banana');
    -- implicit COMMIT at end of statement
    -- with no preceding BEGIN

Using Transactions in MySQL

As mentioned above, by default all tables use the MyISAM storage engine, so transaction support is disabled. By changing a table to use the InnoDB or BDB storage engines, transaction support can be enabled. For new tables, this is done using the ENGINE or TYPE parameters on the CREATE TABLE statement:

    CREATE TABLE foo
    (
        bar INTEGER PRIMARY KEY,
        baz VARCHAR(80) NOT NULL
    )
    ENGINE = InnoDB;

Existing tables can be changed to use the InnoDB storage engine using ALTER TABLE:

    ALTER TABLE foo ENGINE = InnoDB;

You can also change the default storage engine using the default-storage-engine server option on the server command line, or in the server configuration file.

Once all the tables you're using have storage engines that support transactions, you have two choices. For a given connection, you can set the AUTOCOMMIT session variable to 0, in which case every statement within that connection is part of a transaction, as for Oracle, or you can leave AUTOCOMMIT set to 1 and start transactions explicitly as for SQLite. In auto-commit mode for MySQL, transactions are started with BEGIN, BEGIN WORK or START TRANSACTION. To disable AUTOCOMMIT for the current transaction, use the SET statement:

    SET AUTOCOMMIT=0;

You can configure the database to run this statement immediately upon opening a connection using the init_connect server variable. This can be set in the configuration file, or using the following command:

    SET GLOBAL init_connect='SET AUTOCOMMIT=0';

MySQL also supports additional transaction options — check the documentation for details.

Automatic ROLLBACK and COMMIT

One thing to watch out for is code that causes an automatic ROLLBACK or COMMIT. Most databases cause an automatic ROLLBACK of any open transaction when a connection is closed, so it is important to make sure that all changes are committed before closing the connection.

Also worth watching out for are commands that cause an automatic COMMIT. The list varies depending on the database, but generally DDL statements such as CREATE TABLE will cause an automatic commit. It is probably best to avoid interleaving DML statement with any other type of statement in order to avoid surprises. Check your database documentation for details.

Posted by Anthony Williams
[/ database /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

V1.3 of the dbExpress drivers for MySQL V5.0 released

Friday, 24 August 2007

New this release:

• Correctly stores BLOB data with embedded control characters
• Correctly stores string data with embedded slashes
• BCD fields in parameterized queries now work correctly with DecimalSeparators other than '.'
• Time values stored and retrieved correctly
• TSQLTable support works correctly with Delphi 7

See the download page for more details.

Posted by Anthony Williams
[/ delphi /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Database Tip: Create Appropriate Indexes

Monday, 20 August 2007

One of the simplest things you can do speed up database access is create appropriate indexes.

There are several aspects to this. Firstly, you need to identify which columns are used for queries on a given table; in particular, which columns appear in the WHERE clause of time consuming queries. If a query is only done once, or the table only has five rows in it so queries are always quick then there is no benefit to adding indexes. It's not just straight-forward SELECT statements that need checking — UPDATE and DELETE statements can have WHERE clauses too.

Having identified which columns are used in queries, it is important to also note which combinations are used. A database engine will tend to only use one index per table (though some can use more, depending on the query), so if your time-consuming queries use multiple columns from the same table in the WHERE clause, then it's probably better to have an index that covers all of them rather than separate indexes on each.

The cost of indexes

Adding an index to a table does have a downside. Any modification to a table with an index (such as inserts and deletes) will be slower, as the database will have to update the index, and the index will occupy space in the database. That can be a high price to pay for faster queries if the table is modified frequently. In particular, an index that is never used, or covers the same columns as another index is just dead weight. It is important to remember that PRIMARY KEY and UNIQUE columns automatically have indexes that cover them.

Timing is everything

As with every optimization, it is important to profile both before and after any change, and this includes checking the performance of the rest of the application too. Don't be afraid to remove an index if it isn't helping, and bear in mind that it's also possible to improve performance by rewriting queries, particularly where the are joins or subqueries involved.

Posted by Anthony Williams
[/ database /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Demonstrating Software on the Web

Friday, 10 August 2007

One of the techniques we use for collaborating with customers, and obtaining feedback on work in progress is to demonstrate the software over the internet. This means that customers can see the software in action, without having to install it on their systems. This can be beneficial when the software is not yet complete, and we wish to demonstrate how a particular feature works — many customers are reluctant to install unfinished software on their systems to try it out, and an online demonstration means they don't have to do this.

Online demonstrations also provide scope for faster feedback — it's much quicker to start up a web demo than it is to ship a new version of the software to a customer, wait for them to find time to install it, and then talk them through it. This means that changes can be demonstrated as soon as they are ready, and also alternate versions can be shown in the case that the choice is unclear.

For most demonstrations we use TightVNC. This allows the screen of our demonstration PC to be replicated across the web. All that is needed at the customer's site is a web browser with Java installed. We send our users a URL to go to which then connects them to our demonstration PC and loads the TightVNC Java applet. The display is updated in real-time. As an added bonus, the server can also be configured to allow the customers to control the demonstration machine from their end, giving them a chance to try out the software and see how they would use it. We also have the customers on the phone (usually using a speaker-phone) at the same time, so we can talk them through the software, or the changes that have been made.

Though not as ideal as a face-to-face meeting, such an online demonstration is considerably less expensive and time consuming for both parties, and can consequently be arranged far more often.

Posted by Anthony Williams
[/ feedback /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Delay Using a Database

Wednesday, 08 August 2007

A client of ours makes hardware that generates data files, and a few years ago I wrote a piece of software for them to help manage those data files. Initially it only had to deal with a few data files, so it indexed them on start up. Then someone tried to use it with several thousand data files, and start-up times got too slow, so I modified the indexing code to dump the current index to an XML file on shutdown, which it then loaded at startup. This has worked well, but now they're using it to handle hundreds of thousands of files, and the startup and shutdown times are again becoming significant due to the huge size of the XML file. Also, the data file access times are now getting larger due to the size of the in-memory index. We've now been hired again to address the issue, so this time I'm going to use a SQLite database for the index — no start up delay, no shutdown delay, and faster index lookup.

What lessons can be learned from this experience? Should I have gone for SQLite in the first instance? I don't think so. Using a simple in-memory map for the initial index was the simplest thing that could possibly work, and it has worked for a few years. The XML index file was a small change, and it kept the application working for longer. Now the application does need a database, but the implementation is certainly more complex than the initial in-memory map. By using the simple implementation first, the application was completed quicker — not only did this save my client money in the development, but it meant they could begin using it sooner. It also meant that now I come to add the database code, the requirements are better-known and there are already a whole suite of tests for how the index should behave. It has taken less than a day to write the database indexing code, whereas it could easily have taken several days at the beginning.

I think people are often too keen to jump straight to using a database, when they could often get by for now with something far simpler. That doesn't mean that requirements won't evolve, and that a database won't be required in the end, but this time can often be put off for years, thus saving time and money. In this instance I happened to use SQLite, which is free, but many people jump straight to Oracle, or SQL Server, which have expensive licenses and are often overkill for the problem at hand. Just think how much money you could save by putting off the purchase of that SQL Server license for a year or two.

Don't be scared into buying a full-featured enterprise level RDBMS at the beginning of your project; simple in-memory maps or data files will often suffice for a long time, and when they're no longer sufficient you'll know more about what you do need from your RDBMS. Maybe SQLite will do, or maybe it won't — in any case you've saved money.

Posted by Anthony Williams
[/ database /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

The C++ Performance TR is now publicly available

Wednesday, 08 August 2007

The C++ Performance TR is a Technical Report issued by the C++ Standards committee detailing various factors that affect the performance of a program written in C++.

This includes information on various strategies of implementing aspects of the language, along with their consequences for executable size and timing, as well as suggestions on how to write efficient code. It also includes information on use of C++ in embedded systems, including a discussion of putting constant data in ROM and direct access to hardware registers.

Whether you're a compiler writer, library writer or application developer, this is well worth a look. Download a copy from the ISO website today.

Posted by Anthony Williams
[/ cplusplus /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Previous Entries Later Entries

Design and Content Copyright © 2005-2025 Just Software Solutions Ltd. All rights reserved. | Privacy Policy