query optimization

Classic Servoy
1

Good morning my friends,

I have a question about how to optimize a query. Below you will see the query I have created. However it is slow, which doesn’t surprise me.

Is there anything I can do to optimize it or is it better to make it a stored procedure to win time.

The query can take from 1 - 3 seconds… That’s not a real issue in this case but every second I can win is… one extra second :)

Thanks.

var query = 
	"SELECT i.quantity4*(CAST(LEFT(i.printingcolours,1) AS INT)+CAST(RIGHT(i.printingcolours,1) AS INT)) FROM orders AS o,orders_items AS i WHERE " +
	"i.overview=4 AND o.ordersid=i.ordersid AND " +
	"i.printingprocessid=? AND " +
	"o.status>2 AND " +
	"((i.status IS NULL OR i.status<23) AND (i.statususer IS NULL OR i.statususer='')) AND " +
	"(o.onhold IS NULL OR o.onhold=0) AND " +
	"(o.incomplete IS NULL OR o.incomplete=0) AND " +
	"(o.inproof IS NULL OR o.inproof=0) AND " +
	"i.productiondatetime<=? AND o.invoiceitem=0 AND (i.machinesfree IS NULL OR i.machinesfree=0) " +
	"ORDER BY o.rushorder DESC,i.productiondatetime ASC";
2

Depends on the DB used, indexes on the tables etc… :)

For one, if using Oracle, there’s the nvl() command, with which you can rewrite (x is null or x = y) to nvl(x,y) = y, which performs quicker.

As for indexing: Index on the fields that limit the record set the quickest.

Paul

3

Thanks Paul!

Indexes are already there. I need most of these data xxx times per day.

The database used is Sybase iAnywhere… Will see if the nvl (or equivalent) command is also available for iAnywhere.

4

Try asking the index consultant. Unlike normal consultants he charges 0. You can find him in sybase central, launch central, start consultant, run query and the consultant will try to help you out, for free!

5

Yep, I agree :) That’s the way I set the indexes. I will run it again after this query is really in production…