Symbian developer community

 
Home > Wiki > Optimizing Database Operations
wiki

Optimizing Database Operations

From Symbian Developer Community

Jump to: navigation, search


Contents

Options for executing database operations

The most simplistic way of executing a database operation is by executing the SQL query using RSqlDatabase::Exec(). This applies to most single SQL query operations but under other different circumstances such as multiple SQL query operations, it is recommended to use other APIs to achieve better performance. The following subsections outline how different operations can be optimized.

Note that in the example code that is presented in this page the pseudo-code ‘<db_conn>’ is used to represent an instance of RSqlDatabase.

Optimizing operations – general tips

This section contains guidance and tips on how to optimize operations.

Optimizing multiple operations of a single SQL query

This applies to the case where a single operation needs to be executed multiple times with different data values each time. Rather than directly executing multiple statements, a single statement containing the bound parameters should be used instead. For example: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
CleanupStack::PushL(TCleanupItem(&DoRollback, &<db_conn>));
<db_conn>.Exec(_L(“BEGIN”));
stmt.PrepareL(<db_conn>, _L(“INSERT INTO <table> values(?,?,… )”));
for (TUint i=0; i < <no_of_rows_to_insert>; ++i)
	{
   <bind all column values> 
User::LeaveIfError(stmt.Exec());
User::LeaveIfError(stmt.Reset());
	}
<db_conn>.Exec(_L(“COMMIT”));
CleanupStack::Pop(); // TCleanupItem
CleanupStack::PopAndDestroy(); //stmt
 

Optimizing multiple operations using a transaction

Every SQL statement that represents a database operation is always performed within a transaction whether it is explicitly specified or not. This means that specifying N separate operations without wrapping them within an explicit transaction will result in N transactions being made and so N commits to the database. This will have a significant impact on the time taken to complete all N operations – it is much more optimal to execute all N operations within a single explicit transaction.

Note that care should be taken to ensure that transactions are short lived, as other processes will not be able to write to the database whilst another transaction holds a reserve lock.

Caching and re-using the Prepare statement

When it is likely that SQL statements will be re-used by an application, it may prove beneficial to maintain a cache of the most frequently used statements after they have been prepared (RSqlStatement::Prepare()). This is because for each SQL statement the SQL server needs to parse and compile the statement before executing it, and hence by caching the statement the time that would be spent parsing and compiling it can be avoided the next time this statement is required. In the case where a cached statement contains one or more bound parameters it is easy to bind new values to the parameters prior to each run and thus execute slightly different operations on each invocation.

Note however that each cached SQL statement may occupy up to 1KB of RAM (see Optimizing RAM usage when using Symbian SQL#Prepared statements) and thus there is a trade off between operation execution speed and RAM usage when this caching approach is used.

Using aggregate and simple functions

An aggregate function such as COUNT() acts on multiple values but returns a single result whereas a simple function such as random() allows more complex operations to be performed inside the SQL engine. An understanding of the complexity of available functions is useful in helping to reduce the amount of data processing that an application will typically do.

The GROUP BY and HAVING keywords can then be used in conjunction with an aggregate function to impose the required ordering and constraint respectively.

Using the EXPLAIN keyword

The EXPLAIN keyword can be placed before an SQL command to obtain a report of the sequence of internal steps that would have been taken to execute the command. This can be very useful to help understand whether, for example, an index is used when executing the statement, which index is used and whether a temporary table is created, etc. For a further explanation see SQLite Query Language: EXPLAIN.

How to optimize a SELECT operation

There are various ways in which a SELECT operation can be optimized, for example, by the use of an index in the WHERE clause so that the search is a binary search rather than a linear full table scan.

The following subsections provide guidance on various ways to optimize a SELECT statement and common pitfalls to be aware of.

Using an index to make WHERE clause evaluation faster

Using an index in the WHERE clause of a statement can significantly decrease the time taken to find the required record(s).

However, in order for an index to be utilized when executing an operation containing a WHERE clause, there are several rules that need to be adhered to. Note that the SELECT operation is used in the example statements below but that that the rules described for the WHERE clause also apply to other operations such as UPDATE and DELETE.

  • Rule 1: Avoid operator expressions involving the indexed column in the WHERE clause

For example: 

 
CREATE TABLE T1(i1 INTEGER, i2 INTEGER, t3 TEXT)
CREATE INDEX idx1 on T1(i1)
 
// the index is used in this case
SELECT * FROM T1 WHERE i1=2
 
// the index is not used in this case
SELECT * FROM T1 WHERE i1+1=4
 
  • Rule 2: Indices will be utilized if the conjunction (AND) operator is used in the WHERE clause

For example: 

 
// the index is used in this case
SELECT * FROM T1 WHERE i1=2 AND i2=4
 
  • Rule 3: Indices will be utilized if the IN operator is used in the WHERE clause

For example: 

 
// the index is used in this case
SELECT * FROM T1 WHERE i1 IN (5,7)
 
  • Rule 4: Indices will be not be utilized if the disjunction (OR) operator is used in the WHERE clause

Note that an exception to this is that a disjunction on two or more equality tests on the same column is re-written internally to use the IN operator, which will use the index 

 
For example:
 
// the index is used in this case because the expression is re-written as  // SELECT * FROM T1 WHERE i1 IN(1,2,3)
SELECT * FROM T1 WHERE i1=1 OR i1=2 OR i1=3
 
// the index is '''not''' used in this case because the WHERE clause contains the OR operator // with different columns
SELECT * FROM T1 WHERE i1=2 OR i2=3
 
  • Rule 5: Ensure that for a multi-column index the ordering of indexed columns in the WHERE clause matches the ordering of the columns in the index.

For example: 

 
CREATE TABLE T1(i1 INTEGER, i2 INTEGER, t3 TEXT);
CREATE INDEX idx1 on T1(i1, i2, t3); // multi-column index
 
// index is used in this case because all three columns of the index are used
SELECT * FROM T1 WHERE i1=2 AND i2=3 AND t3=’abc’
 
// index is used in this case because the first two columns of the index are used
SELECT * FROM T1 WHERE i1=2 AND i2=3
 
// index is used in this case because the first column of the index is used
SELECT * FROM T1 WHERE i1=2
 
// index is '''not''' used in this case because i1 is the first column in the index
SELECT * FROM T1 WHERE i2=3 AND t3=’abc’
SELECT * FROM T1 WHERE i2=3
 
  • Rule 6: For a multi-column index, an inequality constraint on one of the indexed columns will result in it being the final column to be evaluated – any subsequent indexed columns will not be evaluated. Note that Rule 4 still needs to be obeyed.

At most one indexed column should be constrained by an inequality - an exception to this is that two inequalities are permitted on the same indexed column provided that they provide an upper and lower bound on the column.

For example: 

 
CREATE TABLE T1 (i1 INTEGER, i2 INTEGER, t3 TEXT)
CREATE INDEX idx1 on T1(i1, i2, i3) // multi-column index
 
// index is used in this case, however t3 is not evaluated
SELECT * FROM T1 WHERE i1=2 AND i2<3 AND t3=’abc’
 
// index is used in this case
SELECT * FROM T1 WHERE i1>2 AND i1<7
 

Using an index to make ORDER BY evaluation faster

Using an index in the ORDER BY clause of a SELECT statement can significantly decrease the time taken to order the retrieved record(s).

The default underlying method for evaluating an ORDER BY clause of a SELECT statement is to first evaluate the SELECT statement and store the results in a temporary table, then to sort the temporary table according to the ORDER BY clause and finally to scan the sorted temporary table to generate the final output.

When possible, storing and sorting the result set can be avoided by the use of an index that causes the results to emerge from the query in sorted order on first pass.

However, in order for an index to be utilised when executing an operation containing an ORDER BY clause, there is a rule that needs to be adhered to:

  • Rule 1: Ensure that the order of the columns following the ORDER BY clause matches the order of the columns in the index. If there is a WHERE clause before the ORDER BY clause then the columns used in the ORDER BY clause should occur immediately after the columns used in the WHERE clause

For example: 

 
CREATE TABLE T1(i1 INTEGER,i2 INTEGER,t3 TEXT);
 
CREATE INDEX idx1 on T1(i1,i2,t3); // multi-column index
 
// the index is used in this case
 
SELECT * FROM T1 ORDER BY i1, i2, t3
 
SELECT * FROM T1 WHERE i1=2 ORDER BY i2, t3
 
SELECT * FROM T1 WHERE i1=2 AND i2=3 ORDER BY t3
 
SELECT * FROM T1 WHERE i1=2 ORDER BY i2
 
// the index is '''not''' used here because i1 is the first column in the index
 
SELECT * FROM T1 ORDER BY i2, t3
 
SELECT * FROM T1 ORDER BY i2
 
SELECT * FROM T1 WHERE i1=2 ORDER BY t3
 

Retrieving LONG text or BLOB data

Using a SELECT statement to retrieve text or BLOB data requires the data to be loaded in its entirety into server-side RAM. The client can then use the RSqlStatement::ColumnText(L)/ColumnBinary(L) or RSqlColumnReadStream APIs to load the data into client-side memory. This approach is suitable for text or BLOB data that is not too large in size – specifically, 16KB or under. It is recommended for data larger than 16KB that the RSqlBlobReadStream or TSqlBlob classes are used because this greatly reduces server-side RAM requirements.

To avoid having to allocate a client-side buffer that is large enough to hold the entire text or BLOB data the RSqlColumnReadStream APIs should be used to retrieve the data in blocks. This approach may perform slightly slower than using the RSqlStatement APIs because it will involve multiple IPC calls but it is more RAM-efficient.

For example: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
stmt.PrepareL(<db_conn>, _L(“SELECT * FROM <table>”));
<allocate a buffer that is KBlockSize bytes in size>
 
TInt err = KErrNone;
while ( KSqlAtRow == (err = stmt.Next()) )
    {
    // assume column1 is a BLOB
    RSqlColumnReadStream columnStream;
    CleanupClosePushL(columnStream);
    err = columnStream.ColumnBinary(stmt,0);
    TInt size = stmt.ColumnSize(0);
    while(size)
         {
         TInt bytesToRead = (size >= KBlockSize) ? KBlockSize : size ;
         columnStream.ReadL(<buffer>, bytesToRead); // read next block
         <do something with the block of data>
         size =- bytesToRead;
         }
     CleanupStack::PopAndDestroy(); // columnStream 
     }
CleanupStack::PopAndDestroy(); //stmt
 

For text or BLOB data that is over 16KB2Mb in size it is recommended to not use a SELECT statement to retrieve the data. Instead, the RSqlBlobReadStream and TSqlBlob::Get(LC) APIs provide a way of retrieving the data that requires much less server-side RAM usage.

For example, retrieving a 5Mb BLOB using the code in the above example would require 6Mb of server-side RAM to be allocated - 5Mb to store the entire BLOB in memory and the cache would grow to the 1Mb soft heap limit in doing so. However, using the RSqlBlobReadStream or TSqlBlob::Get(LC) APIs to retrieve the 5Mb BLOB will require only just over 1Mb of server-side RAM – a few Kb for the transfer buffer and the cache will again grow to the 1Mb soft heap limit as the data is fetched in a sequence of blocks.

Again, the RSqlBlobReadStream streaming class should be preferred to the ‘retrieve the data in one block’ TSqlBlob::Get(LC) APIs whenever it is important to avoid allocating a client-side buffer that is large enough to hold the entire text or BLOB data.

For example: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
 
// SELECT the ROWIDs of the BLOBs to be retrieved
stmt.PrepareL(<db_conn>, _L(“SELECT ROWID FROM <table> WHERE Artist = ‘REM’”));
<allocate a buffer that is KBlockSize bytes in size>
 
TInt err = KErrNone;
while ( KSqlAtRow == (err = stmt.Next()) )
    {
    TInt64 rowid = stmt.ColumnInt64(0);
    RSqlBlobReadStream rdStrm;
    CleanupClosePushL(rdStrm);
    rdStrm.OpenL(<db_conn>, <table>, <column>, rowid);
    TInt size = rdStrm.SizeL(); // get the size of the BLOB
    while(size)
        {
        TInt bytesToRead = (size >= KBlockSize) ? KBlockSize : size ;
        rdStrm.ReadL(<buffer>, bytesToRead); // read next block
        <do something with the block of data>
        size =- bytesToRead;
        }
    CleanupStack::PopAndDestroy(); // rdStrm
    }
CleanupStack::PopAndDestroy(); // stmt
 

Optimal interface for single value retrieval

The TSqlScalarFullSelectQuery interface is used for executing SELECT SQL queries that return a single record consisting of a single column value. Using this interface is optimal as it requires fewer IPC calls.

For example, retrieving the ID value of a specific person in a table:

TSqlScalarFullSelectQuery fullSelectQuery(<db_conn>);

TInt personId = fullSelectQuery.SelectIntL(_L("SELECT ID FROM PersonTbl WHERE Name = 'John'"));


RSqlStatement API optimizations

Various APIs of the class RSqlStatement can be used to make the execution of operations as optimal as possible.

TInt RSqlStatement::ParameterIndex(const TDesC &aParameterName) const

TInt SqlStatement::ColumnIndex(const TDesC &aColumnName) const

These APIs get the index of the parameter with the given name and the column with the given name, respectively.

It is more optimal to use a parameter/column index than a parameter/column name in the code that is used to execute an operation. These APIs should be called outside of the main retrieval loop and the returned index value stored and re-used within the loop, rather than using the API as a parameter to one of the bind methods. See the example code below. 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
 
TInt err = stmt.Prepare(<db_conn>, 
				   _L("SELECT i1 FROM Tbl1 WHERE i1 > :Val")); 
<handle error if one occurs>
 
TInt paramIndex = stmt.ParameterIndex(_L(":Val"));
err = stmt.BindInt(paramIndex, 5);
<handle error if one occurs>
 
TInt columnIndex = stmt.ColumnIndex(_L("i1"));
 
while((err = stmt.Next()) == KSqlAtRow)
    {
    TInt val = stmt.ColumnInt(columnIndex);
    RDebug::Print(_L("val=%d\n"), val);
    }
 
if(err == KSqlAtEnd)
    // OK - no more records
else
    <handle the error>
 
CleanupStack::PopAndDestroy(); //stmt
 


TInt RSqlStatement::ColumnText(TInt aColumnIndex, TPtrC& aPtr) const

TInt RSqlStatement::ColumnText(TInt aColumnIndex, TDes& aPtr) const

The first API sets the supplied TPtrC object to point to the specified column data (no copy is made). The second API gets the value of the specified column data as a 16-bit descriptor and copies the data into a 16-bit modifiable descriptor supplied by the caller.

TInt RSqlStatement::ColumnBinary(TInt aColumnIndex, TPtrC8& aPtr) const

TInt RSqlStatement::ColumnBinary(TInt aColumnIndex, TDes8& aPtr) const

The first API sets the supplied TPtrC8 object to point to the specified column data (no copy is made). The second API gets the value of the specified column data as an 8-bit descriptor and copies the data into an 8-bit modifiable descriptor supplied by the caller.

Less copying occurs if the TPtrC methods are used. When a TPtrC object is used, once data is in the client side buffer the TPtrC object will be set to point to that buffer. When a TDes object is used, the data from the client side buffer will be copied to the place pointed to by that TDes object.

Example 1 – A SELECT statement that involves processing a BLOB column, where the data is copied into a buffer supplied by the client: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
TInt err = stmt.Prepare(<db_conn>, _L("SELECT BinaryField FROM Tbl1"));
<handle error if one occurs>
TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));
 
while((err = stmt.Next()) == KSqlAtRow)
    {
    TInt size = stmt.ColumnSize(columnIndex);
    HBufC8* buf = HBufC8::NewLC(size);
    err = stmt.ColumnBinary(columnIndex, buf->Ptr());
    <do something with the data>
    CleanupStack::PopAndDestroy(); //buf
    }
if(err == KSqlAtEnd)
    // OK - no more records
else
    <handle the error>
 
CleanupStack::PopAndDestroy(); //stmt
 

Example 2 – A SELECT statement that involves processing a BLOB column, where the data is accessed by the client without copying: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
 
TInt err = stmt.Prepare(<db_conn>, _L("SELECT BinaryField FROM Tbl1"));
<handle error if one occurs>
TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));
 
while((err = stmt.Next()) == KSqlAtRow)
    {
    TPtrC8 data;
    TInt err = stmt.ColumnBinary(columnIndex, data);
    if(err == KErrNone)
        {
        <do something with the data>
        }
    }
if(err == KSqlAtEnd)
    // OK - no more records
else
    <handle the error>
 
CleanupStack::PopAndDestroy(); //stmt
 

Place constant sub-expressions inside sub-queries

If a calculation has to be made within a query then it can be more efficient to contain the expression in its own sub-query. For example:

// in this case ‘ function’ will be called for each record

SELECT * FROM T1 WHERE t3 > function('abc');

// the more optimal approach is to place the function call inside a sub-query

SELECT * FROM T1 WHERE t3 > (SELECT function('abc'));

Using LIMIT and OFFSET

The LIMIT clause curtails the number of records returned by a SELECT statement by specifying an integer upper bound. There is an optional OFFSET keyword that specifies how many records to ignore from the beginning of the set returned. When a statement contains sub-queries, the LIMIT clause may only appear on the final SELECT statement. Note that in this case the specified limit is applied to the entire query and not just to the individual SELECT statement that the LIMIT clause is part of.

How to optimize an INSERT operation

The following subsections provide guidance on various ways to optimize an INSERT statement.

Optimizing insertion of a single record

For any single INSERT operation, using the API RSqlDatabase::Exec() is the most optimal way of executing it.

The exception to this is when the record to be inserted contains LONG text or BLOB data, which is discussed in the next section.

Optimizing insertion of LONG text or BLOB data

When one or more records to be inserted contain LONG text or BLOB data then the quickest way to execute the operation is to use a RSqlStatement::Prepare() statement with bounded parameters.

There are two ways in which to bind a BLOB value:

    • Use RSqlStatement::BindBinary() (and use BindText() for text)
    • Use RSqlParamWriteStream::BindBinary() (and use BindText() for text)

For example: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
stmt.PrepareL(<db_conn>, _L(“INSERT INTO <table> values(?,?,… )”));
for (TUint i=0; i < <no_of_rows_to_insert>; ++i)
	{
   	// assume column1 is a BLOB and direct binding is to be used
   	TInt err = stmt.BindBinary(0,<parameter_value>);
<handle error if one occurs>
	// assume column2 is a BLOB and streaming is to be used
RSqlParamWriteStream stream;
CleanupClosePushL(stream);
err = stream.BindBinary(stmt,1);
<handle error if one occurs>
stream.WriteL(<parameter_value>,<length_of_parameter>);
stream.CommitL(); // Commit the data to the stream.
CleanupStack::PopAndDestroy(); // stream
	<bind all other column values>
User::LeaveIfError(stmt.Exec());
User::LeaveIfError(stmt.Reset());
	}
CleanupStack::PopAndDestroy(); // stmt
 

However, for text or BLOB data that is over 16KB in size it is recommended to use a zeroblob when inserting the record and then to update the zeroblob with the actual BLOB content using the RSqlBlobWriteStream class or TSqlBlob::SetL() API.

A zeroblob acts as a place-holder for a BLOB whose content is later written and one can be created using an INSERT statement that either contains the SQLite 'zeroblob()' function or on which RSqlStatement::BindZeroBlob() has been executed. Using zeroblobs in combination with the RSqlBlobWriteStream class it is possible to create BLOBs that are gigabytes in size. Use of the RSqlBlobWriteStream and TSqlBlob classes allow manipulation of very large BLOBs and also significantly reduce the amount of server-side RAM that is required to insert the BLOB.

For example, inserting a 5Mb BLOB using the code in the above example would require 5Mb of server-side RAM to be allocated to store the entire BLOB in memory. However, using a zeroblob that is later written to using the RSqlBlobWriteStream or TSqlBlob::SetL() APIs will require only a few Kb of server-side RAM for the transfer buffer that is used to write the BLOB content in a sequence of blocks.

Note that zeroblobs should be created in a column which has no columns to its right which are anything other than zeroblobs or NULLs. If there are columns to the right of the zeroblob which are not NULLs or zeroblobs, the zeroblob being created will be fully allocated in server side RAM.

When creating a zeroblob it is recommended where possible, to create the zeroblob and then write the BLOB content within the same transaction. Otherwise the zeroblob will have to be journalled before being written to.

The RSqlBlobWriteStream streaming class should be preferred to the ‘write the data in one block’ TSqlBlob::SetL() API whenever it is important to avoid allocating a client-side buffer that is large enough to hold the entire text or BLOB data to be written.

For example: 

 
CleanupStack::PushL(TCleanupItem(&DoRollback, &<db_conn>)); // rollback function
TInt err = <db_conn>.Exec(_L("BEGIN"));
<check err>
err = <db_conn>.Exec(_L("INSERT INTO <table> VALUES(1, zeroblob(5242880))"));
<check err>
RSqlBlobWriteStream wrStrm;
CleanupClosePushL(wrStrm);
wrStrm.OpenL(<db_conn>, <table>, <column>);
TInt size = wrStrm.SizeL(); // get the size of the zeroblob
while(size)
	{
	TInt bytesToWrite = (size >= KBlockSize) ? KBlockSize : size ;
	<fill a buffer with this amount of the BLOB data to be written>
	wrStrm.WriteL(<buffer>); // write the next block		
	size =- bytesToWrite;
	}
wrStrm.CommitL(); // Commit the data to the stream.
CleanupStack::PopAndDestroy(&wrStrm);
CleanupStack::Pop(); // TCleanupItem
err = db.Exec(_L("COMMIT")); // BLOB data committed to disk
<check err>
 

Optimizing insertion of multiple records

The insertion of multiple records can be achieved by using a single RSqlStatement::Prepare() statement containing bounded values as described in #Optimizing multiple operations of a single SQL query

If the time taken to execute bulk insertions is causing performance issues then there are a few techniques that may help to lessen the perceived slowness.

If there are indices present on the table then it may be beneficial to temporarily ‘drop’ the indices before the bulk insertion. This separates the time taken to create/update the indices from the time taken to do the bulk insertion, which may allow for improved application responsiveness.

Another option is to insert the records initially into a temporary table (no indices) and to then transfer the records from there into the main table.. This approach also favours controlled application responsiveness – the insertions happen immediately (into the temporary table) and so are not ‘lost’ and they can then be transferred into the main table, perhaps in blocks, as and when appropriate, to help meet the required application response times.

How to optimize an UPDATE operation

The following subsections provide guidance on various ways to optimize an UPDATE statement.

Optimizing an update to a single record

For any single UPDATE operation, using the API RSqlDatabase::Exec() is the most optimal way of executing it.

The exception to this is when the data to be updated is LONG text or BLOB data, which is discussed in the next section.

Optimizing an update to LONG text or BLOB data

This case is similar to the INSERT case in which LONG text or BLOB data is involved. Again, for optimal speed, an RSqlStatement::Prepare() statement should be preferred, with bounded parameters.

For example: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
stmt.PrepareL(<db_conn>, _L(“UPDATE <table> SET column1=?, column2=?”));
for (TUint i=0; i < <no_of_rows_to_insert>; ++i)
	{
   	// assume column1 is a BLOB and direct binding is to be used
   	TInt err = stmt.BindBinary(0,<parameter_value>);
<handle error if one occurs>
	// assume column2 is a BLOB and streaming is to be used
RSqlParamWriteStream stream;
CleanupClosePushL(stream);
err = stream.BindBinary(stmt,1);
<handle error if one occurs>
stream.WriteL(<parameter_value>,<length_of_parameter>);
stream.CommitL(); // Commit the data to the stream.
CleanupStack::PopAndDestroy(); // stream
	<bind all other column values>
User::LeaveIfError(stmt.Exec());
User::LeaveIfError(stmt.Reset());
	}
CleanupStack::PopAndDestroy(); // stmt
 


In the case where the text or BLOB data to be updated is over 16KB in size it is recommended to use the RSqlBlobWriteStream or TSqlBlob::SetL() APIs as these provide a way of updating the data that requires much less server-side RAM usage.

For example, updating a 5Mb BLOB using the code in the above example would require 6Mb of server-side RAM to be allocated - 5Mb to store the entire BLOB in memory and the cache would grow to the 1Mb soft heap limit in doing so. However, using the RSqlBlobWriteStream or TSqlBlob::SetL() APIs to update the 5Mb BLOB will require only just over 1Mb of server-side RAM – a few Kbs for the transfer buffer and the cache will again grow to the 1Mb soft heap limit as the data is written in a sequence of blocks.

Again, the RSqlBlobWriteStream streaming class should be preferred to the ‘write the data in one block’ TSqlBlob::SetL() API whenever it is important to avoid allocating a client-side buffer that is large enough to hold the entire text or BLOB data to be written.

It is strongly recommended to execute calls to RSqlBlobWriteStream::WriteL() within a transaction. If a leave occurs during a call to WriteL() then the current state of the BLOB object is undefined and a ROLLBACK should be executed to return the BLOB object to its previous state. Note that in order for a ROLLBACK to execute successfully all open RSqlBlobWriteStream and RSqlBlobReadStream handles and all open RSqlStatement objects must be closed before the ROLLBACK is executed.

For example: 

 
RSqlStatement stmt;
CleanupClosePushL(stmt);
// SELECT the ROWID of the BLOB to be updated
stmt.PrepareL(<db_conn>, _L(“SELECT ROWID FROM <table> WHERE Track = ‘Imagine’”));
TInt err = KErrNone;
if ( KSqlAtRow != (err = stmt.Next()) )
	{
	<handle error>
	return;
	}
TInt64 rowid = stmt.ColumnInt64(0);	
CleanupStack::PopAndDestroy(); // stmt
 
<allocate a buffer that is KBlockSize bytes in size>	
CleanupStack::PushL(TCleanupItem(&DoRollback, &<db_conn>)); // rollback function
TInt err = <db_conn>.Exec(_L("BEGIN"));
<check err>
RSqlBlobWriteStream wrStrm;
CleanupClosePushL(wrStrm);
wrStrm.OpenL(<db_conn>, <table>, <column>, rowid);
TInt size = wrStrm.SizeL(); // get the size of the BLOB
while(size)
	{
	TInt bytesToWrite = (size >= KBlockSize) ? KBlockSize : size;
	<fill a buffer with the next block of changed blob data>
	wrStrm.WriteL(<buffer>); // write next block	
	size =- bytesToWrite;
	}
wrStrm.CommitL(); // Commit the data to the stream.
CleanupStack::PopAndDestroy(); // wrStrm
CleanupStack::Pop(); // TCleanupItem
err = <db_conn>.Exec(_L("COMMIT")); // blob data committed to disk
<check err>
 

Optimizing updates to multiple records

Again, similar to the insert case, the update of multiple records can be achieved by using a single RSqlStatement::Prepare() statement containing bounded values as described in #Optimizing_multiple_operations_of_a_single_SQL_query.

How to optimize a DELETE operation

There is no great extent of optimization that can be achieved when executing DELETE operations. As a general point however, performing bulk deletes (i.e. the deletion of many records at a time within a single transaction) is more performant than deleting each record individually.

Retrieved from "http://developer.symbian.org/wiki/index.php/Optimizing_Database_Operations"

Comments

Sign in to comment…