Call us on 0141 779 2969
Kigtek Logo
We are very happy to be working with Kigtek Solutions, a leading Systems Integrator and Electrical Installation Services company based in the central belt of Scotland. Work on this project is ongoing and is expected to be completed by the end of November 2016.
In this post, we give an example to show how a function-based index in Oracle can increase the speed of a query where null and not null values are being compared.
Our customer asked us to modify their application to enable an ETL tool to collect invoice records from an Oracle database. The invoice table contained upwards of 8 million records, but we were really only interested in looking at records which had been flagged for collection but not yet flagged as collected. However, the ETL tool’s method of flagging these records did not allow us to use a simple bitmap index to quickly retrieve the rows we needed.
Extract, Transform, Load refers to a process that extracts data from outside sources (e.g. a file produced by a customer), transforms it to fit operational needs, and then loads it into your target database.
You can read about an example ETL tool, Pentaho Data Integration on our Technologies page.
Consider the following example data set for our Invoices table
In this example, the two columns with names starting ETL_ behave like flags. The possible values are null or a varchar2 value which happens to be based on a timestamp. For the purposes of this exercise, we are only interested in whether the values are null or not null.
SELECT * FROM INVOICES WHERE ETL_collect IS NOT NULL AND ETL_done IS NULL;
which would retrieve the following rows
In a table containing several million rows, this query would take too long to run. Adding a B-Tree index on ETL_collect and ETL_done wouldn’t help because the null values would not be included in the index. Bitmap indexes do include nulls but we have such a range of different not null values in our table, that a bitmap index is not the best choice.
We need to transform our ETL columns from null / not null values to 0 / 1 and then use an index to optimise the query. First we create a function that tests if a value is null, and returns a 1 or 0 accordingly –
CREATE OR REPLACE FUNCTION ETL_MATCH (
field_in IN INTEGER
) RETURN INTEGER
DETERMINISTIC
IS
BEGIN
if field_in is not null then
RETURN(1);
ELSE
RETURN(0);
END IF;
END;
By declaring this function to be DETERMINISTIC, we indicate that the function returns the same result value whenever it is called with the same argument.
Now we create an index on our ETL columns which makes use of this function on both of the columns we are testing –
CREATE INDEX ETL_Match_idx ON Invoices (ETL_Match(ETL_collect), ETL_Match(ETL_done));
And now to revisit our original query. To force it to use this index, we need to reference the indexed columns in the where clause, and we can do that as follows:
SELECT * FROM INVOICES WHERE ETL_Match(ETL_collect) = 1 AND ETL_Match(ETL_done) = 0;
With an underlying invoices table containing more than 8 million records, the original query took 17 seconds to retrieve 70 rows. The new query using a function-based index returns 70 rows in less than 3 seconds.
If you have a database or application which is running slowly, we can help you make it perform better. Please give us a call or use the contact us form on the right, and we will get in touch.
Maria visited the London Book Fair on Tuesday this week. Buzzing with activity and enthusiasm, it’s great to see our thriving book industry in action. Lots of familiar faces too, and chocolate frogs!
Maria with some of the Batch Team at the Book Fair
The London Book Fair 2014 at Earls Court
April 8th was International Terry Pratchett Day
Hop along to the Batch stand for a chocolate frog
Maria will be visiting the London Book Fair (LBF14) on Tuesday 8th April, looking out for opportunities to share ideas on how to use technology to create, sell and promote books. If you would like to meet up for a chat, please get in touch.
When we develop software, we are guided by our customers to tell us what they need. Quite often, however, the people who will be using the final product are not our customers, but their customers. To put the icing on the cake, we need to do some Usability Testing with real users. Such as Chris.
Chris McLaughlin is a former colleague of mine who now runs a a busy academic bookshop at Glasgow Caledonian University. He has a button named after him. The Chris Button. Well that’s what we call it, anyway.
As software developers, we’re testing things all the time. As soon as we have written a chunk of code to do something, we will test it to see if it does what we want. The bigger the system gets, the more we need to test, and of course, if we go back and change something, we need to test that the change hasn’t broken something else. You can’t really get away from that kind of testing. However, that’s not what I want to talk about today. This article is about the importance of usability testing, which can often be overlooked.
Usability testing involves evaluating a working piece of software by presenting it to a user – a real user, preferably someone who is going to be using the finished product – and observing what they do with it. There are no rights or wrongs for the tester, we just want to know what they make of the software, so we resist the urge to jump in and correct them when they do something unexpected. We can learn a huge amount by watching (and listening, if they are happy to think aloud as they test), and this can feed back into the software design to make the next release better.
In 2009 I was working on a project to implement a returns system for books. The book industry has an agreed process whereby bookshops can return books to the suppliers if they don’t sell within an agreed timescale. First they have request permission from the supplier. The supplier responds with an authorisation message, which indicates how many copies they will accept, and then, all being well, the books are returned, and the shop sends a third message to the supplier to let them know how many copies have actually been sent. This whole process can be done manually via email, fax or (in the old days, when I was a bookseller) by post. However, it is time consuming, and tedious. The Batch Returns service provides a standard way for booksellers and suppliers to send and receive electronic Returns messages, resulting in a turnaround time of a few hours. The only manual bit is putting the books into boxes.
I was delighted to be working on this project, given my bookselling background; I must have handled thousands of book returns, and reams of associated paperwork, twenty-odd years ago before Batch Returns even existed. And here I was, giving something back. However, mindful of the fact that I hadn’t worked in bookselling for several years, I was keen to get real users involved in evaluating the system as development progressed. I made a few phone calls and arranged to visit Chris at his shop to show him what I had so far. I think I met with him three times in total, and each time I would come away with a few ideas on how to improve the interface and the workflow. During one of our meetings I learned that he sometimes needed to return a long list of books, each with the same quantity, and that using the arrow keys to move down the list adjusting the quantities one by one was a pain. I took this on board and on the next visit I introduced him to the Chris Button which was well received.
The Chris button allows users to quickly set all quantities in a long list of books to the same value.
On seeing the Chris button, Batch recognised its potential on other screens, and we added a variation to the suppliers’ interface to speed up the process of authorising returns.
The “Set All” Button on the Suppliers’ Authorisation screen offers a choice of fields to set.
The idea of a button which allows the user to apply the same action to multiple lines is by no means groundbreaking. We see it in software all the time. But this story highlights the importance of spending time with end users, learning how to make their experience better. Never assume you know better than a real user.
Further Reading