ProLaw Querying; Never Use Brackets. Unless you get an error message.

In every query box in ProLaw, this concept holds true. Always, always, use parentheses and never brackets. Until you get the error message.

your query is too long. Please try splitting it into smaller queries surrounded with square brackets. Text in screen shot.

Not This; (Query 1)

[Opened Date < today-30 and status ='open'] and [assigned type = 'main attorney' and initials='ABC']

This: (Query 2)

(Date of Outcome < today-30 and status ='open') and having any (assigned type = 'main attorney' and initials='ABC')

So for all you geeks and visual folks Venn diagrams!

Query 1 returns every possible matter for A and every possible every possible matter for B. Then the over lap is the returned set. So if the red area was 10, and A and B were both 50, we had to look at 100 records to get 10. Inefficient.

Query 2 returns only the conditional statement that makes up 10. So the result is 10 and the final result is 10. A white and B white space are never considered because they are outside of the bounds of the conditional statements.

Another way to to think of it is:

[Opened Date < today-30 and status ='open'] and [assigned type = 'main attorney' and initials='ABC']

is really two steps:

1. [Opened Date < today-30 and status ='open']

OR

2. [assigned type = 'main attorney' and initials='ABC']

Then where both of those are true it returns the result in the second step.

Notice I changed the and to to an or.

I think the piece that professionals don't realize is that the conditional statements with brackets don't overlap first, they are a comparison second. So the status = open doesn't apply to the second bracketed statement, it only applies to the first on the first comparison. You think you're returning just the subset of open matters, but you're actually first returning every matter ever assigned with ABC main attorney, regardless of opened date and status, then comparing that to all the open matters in the last 30 days.

This is just the way all relational database query engines work. But it applies to you if your reports are taking a very long time to complete. How we query the database, especially as our DB instances mature, is important to keep productivity high. If you have a bunch of bracketed queries, fix them, and see if you don't see an overall increase in your application performance.

Oh and on that topic, please disable the auto search in matters and contacts. You're degrading your system for no reason, even with indexes.

As always if you have any questions, or want to talk it out some more. Feel free to email or call.

Merry Christams and Happy Holidays!

Jason & C2Axis