Query speed

[Vrto]

Hi all. This is the query i need to help with.

OPEN QUERY {&SELF-NAME} FOR EACH shipments NO-LOCK,
FIRST user WHERE user.sysuser = gv-sysuser
AND CAN-DO (gv-sysgroup, user.sysgroup)
AND user.category = 'supplier'
AND (user.address_code = "*" OR user.address_code = shipment.exporter_code) NO-LOCK,
FIRST bf1-user WHERE bf1-user.sysuser = gv-sysuser
AND CAN-DO (gv-sysgroup, bf1-user.sysgroup)
AND bf1-user.category = 'agent'
AND (bf1-user.address_code = "*" OR bf1-user.address_code = shipment.foreign_agent) NO-LOCK,
FIRST bf2-user WHERE porder.bf2-user.sysuser = gv-sysuser
AND CAN-DO (gv-sysgroup, bf2-user.sysgroup)
AND bf2-user.category = 'customer'
AND (bf2-user.address_code = "*" OR porder.bf2-user.address_code = po_shipment.importer_code) NO-LOCK.

Explanation.
bf1-user and bf2-user are buffers for user table.
Indexes for shipments: exporter_code, foreign_agent, importer_code.
Indexes for user: sysuser, sysgroup, category, address-code.

What it should do.
I need to check each shipment record if an user has permission to see it. In user table are stored address-codes for each address category which can be "Customer,Agent or Supplier". It looks like:

user group address category address code
1 a agent a1001
1 a agent a2002
1 a supplier b2001
1 a customer *

If there is an asterix as an address code it means user can see all shipment record for that customer.

Problem. Query speed.

My solution.
Create a list of address codes and work with them them (lookup......) i open query.

DEFINE VARIABLE lv-query AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-wc AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-ship-query AS HANDLE NO-UNDO.

CREATE QUERY lv-ship-query.
lv-ship-query:SET-BUFFERS(BUFFER po_shipment:HANDLE).

RUN create-agent-list.
RUN create-supplier-list.
RUN create-customer-list.

IF lv-customer-list NE "" THEN lv-wc = lv-wc + "AND" + " LOOKUP(STRING(po_shipment.importer_code),'" + lv-customer-list + "',',') NE 0 ".
IF lv-agent-list NE "" THEN lv-wc = lv-wc + "AND" + " LOOKUP(STRING(po_shipment.foreign_agent),'" + lv-agent-list + "',',') NE 0 ".
IF lv-supplier-list NE "" THEN lv-wc = lv-wc + "AND" + " LOOKUP(STRING(po_shipment.exporter_code),'" + lv-supplier-list + "',',') NE 0 ".

lv-query = "FOR EACH po_shipment WHERE YES " + lv-wc + " NO-LOCK".

lv-ship-query:QUERY-PREPARE(lv-query).
{&browse-name}:QUERY IN FRAME {&FRAME-NAME} = lv-ship-query.
lv-ship-query:QUERY-OPEN().

What i'm asking for.
I believe there must be better solution how to do this.

Progress version is 9.1D 0980.

THANK YOU for any help / idea.

Peter

P.S. I've never been good in explain things even in my native language so if you have any question please ask. :blush:
P.S2. Users can also search on couple of things so real query is a little bit bigger but it's not relevant:
IF ov_type NE "ALL" THEN lv-wc = lv-wc + "AND" + " po_shipment.ship_type = '" + ov_type + "'".
IF ov_ref-no NE 0 THEN lv-wc = lv-wc + "AND" + " po_shipment.ship_id = '" + STRING(ov_ref-no) + "'".
IF cb_jobs NE "ALL" THEN lv-wc = lv-wc + "AND ((" + cb_jobs + " = 'YES' AND po_shipment.mfrt_jobdisp NE '') OR (" + cb_jobs + " = 'NO' AND po_shipment.mfrt_jobdisp = ''))".
IF ov_agent NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.foreign_agent = '" + ov_agent + "'".
IF ov_agent-ref NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.agent_ref = '" + ov_agent-ref + "'".
IF ov_vessel NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.vessel_flno = '" + ov_vessel + "'".
IF ov_carrier NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.carrier_code = '" + ov_carrier + "'".
IF ov_importer NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.importer_code = '" + ov_importer + "'".
IF ov_exporter NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.exporter_code = '" + ov_exporter + "'".
IF ov_obill-mawb NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.ocean_mawb = '" + ov_obill-mawb + "'".
IF ov_hbill-hawb NE "" THEN lv-wc = lv-wc + "AND" + " po_shipment.house_hawb = '" + ov_hbill-hawb + "'".
IF ov_etaf NE ? THEN lv-wc = lv-wc + "AND" + " po_shipment.eta >= '" + STRING(ov_etaf) + "'".
IF ov_etat NE ? THEN lv-wc = lv-wc + "AND" + " po_shipment.eta <= '" + STRING(ov_etat) + "'".
IF ov_etsf NE ? THEN lv-wc = lv-wc + "AND" + " po_shipment.ets >= '" + STRING(ov_etsf) + "'".
IF ov_etst NE ? THEN lv-wc = lv-wc + "AND" + " po_shipment.ets <= '" + STRING(ov_etst) + "'".

 

[Cringer]

Personally I would read the relevant user records into temp tables rather than having the buffers. You can cache those into a temp-table on screen load. Then you don't need to query them again and as the tt is loaded into memory a search would be much quicker, plus as you only have the users you want loaded into each tt, the query's much simpler.

It might not be the best solution, but then I'm not that great at query tuning. It's a suggestion at least.

 

[GregTomkins]

There is way, way, way too much detail here for random web posters like us to say anything meaningful. Besides, you'd need to know the data distribution, eg. does each customer typically have 10 records, or 100,000?

We have a very large table in our system (millions of records, thousands more every day) and a very highly used query that permits filtering by 9+ different criteria. We streamline this somewhat by forcing them to input enough filters to get the query down to a manageable size.

For example, if your table contains order data, you might require them to always enter either (a) a customer identifier or (b) a product identifier. Hopefully, that reduces the scope of the query sufficiently that you can then efficiently discard more obscure filters such as, perhaps, size of order or where it was shipped from.

Another thing we constantly talk about doing, didn't do in the original design, and wish we had: separate tables like this into a 'current highly used data', keep it relatively small, and put a zillion indices on it. Move the 'older' stuff into a 'history' table, that is enormous, with far fewer indices and much more restrictive search ability.

Of course this may be totally inappropriate to your situation, but it would definitely help in ours, where 99% of the time all we really care about is orders entered within the last 24 hours.

 

[Vrto]

Thank you for your responses. Unfortunatelly i don't have enough information how our customers going to use this product. It looks like i have to leave it how it is and if necessary re-write queries in future.