How to Make Your Browses Race Like Rockets

The best thing about browses is that anyone can create one!  Just grab a table or two, slap some fields in place, and away you go!

The worst thing about browses is pretty much the same thing: anyone can create one.  The way you create your browses, the tables you choose, the fields you pick… if you’re not careful, your browses will sputter and wheeze off the launchpad instead of racing like rockets. 

To help you out, I’m going to give you three things to speed up your browses.  These aren’t the only things you can do, to be sure, but they can really give you a boost.

For simplicity, let’s assume you’re creating a browse to show available inventory for open sales orders.

Plan Out Your Tables

When you add tables to a browse, you’re building the underlying database query.  In our example, we’ll use three tables: the part master (pt_mstr), inventory location detail (ld_det), and sales order detail (sod_det).  That gives us items, inventory, and sales orders.

Whatever table you add first becomes the driver behind the entire browse.  Consider this sequence with 1,000 items, 2,000 location details (two for each item), and 100 sales order lines:

                Part master → Inventory location detail → Sales order detail

This looks at all 1,000 items in the database, ultimately reads every one of the 2,000 location detail records, and then reads the 100 sales order lines.  Doing the math, that means 3,100 reads  (1,000 + 2,000 + 100) to query the database.

That could be better, though!  This approach checks every item, no matter whether there’s inventory, whether there are orders, or whether you even sell it!  This will be slo-o-ow.

If you rearrange it:

Sales order detail → Part master → Inventory location detail

Then the browse will only look for items that are currently on sales orders.  It checks all 100 sales order line, reads the item records (100 more reads), and reads their location detail records (200 reads).   Doing this math, that’s only 400 reads (100 + 100 + 200).

 Quite a bit better than 3,100, isn’t it?  Change those numbers to match your database and the difference should be much greater.

Choose Fields For Performance

Did you know that the fields you add to a browse serve three purposes? 

The first is obvious: to display data.  If you want to see it in the browse, you must add it.

The second is a little less obvious: to filter data.  If you want to sort or select by a particular field, you must add it to the browse.

The third is less obvious: to retrieve your data.  And this is where performance happens (or doesn’t!).

Browses use the fields contained in your query to find the best index to retrieve your data.  They will do the best they can to find a good index but sometimes you can help them out by adding an extra field to your query to support an index.  You don’t even have to include that field in your browse output; just set “Display” to “No” for that field and it won’t show up!

Add Filters To Reduce Records

Older versions of QAD removed the sales order detail records once they were shipped.  Modern QAD can still leave those SO detail records, though, and if you’re not careful, you could be searching through all those shipped lines! 

By entering filter criteria in the Query section of the browse, you can eliminate or reduce records before the browse even starts.  In the case of sales order lines, filtering the Complete Status or Status Date can eliminate all those completed order lines right away!

I have also used filters to remove obsolete parts, skip items with no inventory, and even eliminate certain site.  Your table selection will determine where you can apply filters.

Summary

And there you have it:  how to use tables, fields, and filters to speed up your browses.  Experiment with them and you’ll fine-tune the process to make them go even faster!