Query seaches slowwww!!!

Madimant

I haven't had this problem before or at least didn't notice it until the other day.
Not sure if it is because there is a lot more records now than what was when I originally wrote the rules.

Example...
I have 3 BO Clients (750 records)->Schedules (1000 Records)->Risks (5000 records)
Clients is the owner of Schedules and Schedules is the owner of Risks.

I now do a Search - FIND Risks WHERE (Risks.Schedules.Clients.ID = Client.ID)
and it takes 20 seconds to return 5 entries.

Tried creating a shortcut - Risks.sc_ClientID and search for FIND Risks WHERE (Risks.sc_ClientID = Client.ID)
Same result 20 seconds.

Then I created another attribute - Risks.ClientID and populated it overnight with Client.ID values.
Searching FIND Risks (WHERE Risks.ClientID= Client.ID) then came back within 1 second.

This works great but defeats the purpose of referencing tables?

What am I doing wrong?

I'm on the latest software version....

Jaymer

hard to say without looking at a SQL profiler,

Risks.Schedules.Clients.ID requires SQL to join 3 tables together - the search is done on the field from the Client table (as you requested)

Risks.Schedules.ps_Client would search on a field in the Schedules table (I named it ps_Client for clarity, but thats your ref field) because Schedules most likely contains the Client ID in it - unless there's a _REF table involved.

AND, the ref field is already indexed automatically by Aware, so that simple change may show better results.

tford

Tried creating a shortcut - Risks.sc_ClientID and search for FIND Risks WHERE (Risks.ClientID = Client.ID)
Same result 20 seconds.

I'm assuming perhaps a typo, but your FIND in this case does not use the same name as the shortcut attribute name you mentioned.

Also, I'm curious in your situation where you are finding Client.ID in the first place. Is it typed in by the user or is it perhaps the ID of a Client selected from a query?

Madimant

Thanks for the replies guys,...

The three tables are in a [1 to many] relationships .
1 Clients got many Schedules and 1 Schedules got many Risks...

The Client.ID is the ID created by AwareIM tables and I was having difficulty finding items in referenced tables without specifying an attribute.
ie FIND Schedules WHERE Client.ref_Schedules=Schedules instead of FIND Schedules WHERE Client.Schedules.ID=Schedules.ID

This was awhile ago and will revisit it again this weekend.

tford - And yes that was a typo my side should read Risks.sc_ClientID=Client.ID

Hope this makes sense...
Tj

customaware

Here is how I would do it.

I will assume all Relerences are Peer for this exercise but of course the Reference between Client and Schedule would I suspect be best as OWNS

The Reference Attribute in Client for many Schedules..... Name the Attribute pm_Schedules (and ensure is is multiple) and the reverse matching Reference in Schedule, name that ps_Client (and ensure it is NOT multiple)
The Reference Attribute in Schedule for many Risks...Name the Attribute pm_Risks (and ensure is is multiple) and the reverse matching Reference in Risk, name that ps_Schedule (and ensure it is NOT multiple)

You will RARELY EVER need to use the ID attribute of a BO.

So, if you are wanting to FIND the Schedules that belong to a Client.... and you have that Client in context.. ie. have passed it as the Input to a Process, then

FIND Schedule WHERE Schedule IN Client.pm_Schedules

Alternatively you can achieve the same result by

FIND Schedule WHERE Schedule.ps_Client = Client

Also, if you want to say, FIND all the Risks for a partiulare Client regardless of their Schedule....

Again, assuming the Client in question is in context....

FIND Risk WHERE Risk.ps_Schedule.ps_Client = CLient

Using .ID except in those rare circumstances is fighting against the Aware IM engine and is missing out on taking advantage of the AWESOME strength of Aware References.

Hope this helps.