Optimizing Query Speeds

First, a couple of cautions:  If you use Individual or Organization Query Type rather than Key Indicator, be sure you remember you've done that...especially if you tend to use the same query later.  Also, [Equals One Of] and [Is Not Blank] can sometimes return vastly different results!



As to your original question, I've found that it more depends on how complex your criteria are.  You can use Merge Queries or work with Query Lists for some fine-tuning, should you prefer working completely within RE.



I usually try to use as few criteria as possible, and then either clean up the data outside of RE or Globally Add an Attribute, then start whittling down the list by adding one or two pieces of criteria at a time to a Query that starts with "Attribute Category equals This List".  Once I'm done, I can use Global Change to delete the Attributes, and go into Config to delete the Attribute Category itself.  (In my case, I have 5 Attributes for this purpose...with a number in the Attribute Category, so I have them at the ready whenever I need them, and they can hang out for as long as I need them.  I do this most often for Appeals, because it's so difficult to edit Appeal Tags once in RE...Package, for example, cannot be Globally Changed and Marketing Segment cannot be Globally Added.)



 

This is a very complex topic with, literally, millions of variables that can affect performance. Both RE and SQL Server have some automatic optimization built in that do a good job in most cases.



Aside from just running tests on the various possibilities you can go to the menu bar View > SQL (or Ctrl+Q) and see what the underlying SQL is.



Looking at just the checkbox options here't a Constituent Query with all boxes checked and no Criteria or Output:

SELECT

RECORDS.ID "QRECID"

FROM

DBO.RECORDS AS RECORDS

WHERE

((RECORDS.IS_CONSTITUENT = -1))



Unchecking Deceased gives this:

SELECT

RECORDS.ID "QRECID"

FROM

DBO.RECORDS AS RECORDS

WHERE

((RECORDS.IS_CONSTITUENT = -1) AND (RECORDS.DECEASED = 0))



Adding Criteria of "Deceased equals No" gives this:

SELECT

CASE RECORDS.DECEASED WHEN 0 THEN 'N' WHEN -1 THEN 'Y' ELSE '' END "Deceased",

RECORDS.ID "QRECID"

FROM

DBO.RECORDS AS RECORDS

WHERE

((RECORDS.DECEASED = 0) AND (RECORDS.IS_CONSTITUENT = -1))



Because the Decease value gets automatically added to the Output (at least that's how I have it set in my Options) it adds additional processing overhead in the Select statement to return a "Y" or "N" in the Results tab instead of a zero or one. This will take a microscopically longer time to process.



If I remove Deceased from the Output the SQL returns to this:

SELECT

RECORDS.ID "QRECID"

FROM

DBO.RECORDS AS RECORDS

WHERE

((RECORDS.DECEASED = 0) AND (RECORDS.IS_CONSTITUENT = -1))

Identical to what it was before.



Then there's the Execution Plan tab of the SQL Statement window where you can see some of the initial optimization that's being done:

|--Parallelism(Gather Streams)

|--Clustered Index Scan(OBJECT:([Minnesota_Childrens_Museum_RE].[dbo].[RECORDS].[PK__RECORDS__408F9238] AS [RECORDS]), WHERE:([Minnesota_Childrens_Museum_RE].[dbo].[RECORDS].[DECEASED] as [RECORDS].[DECEASED]=(0) AND [Minnesota_Childrens_Museum_RE].[dbo].[RECORDS].[IS_CONSTITUENT] as [RECORDS].[IS_CONSTITUENT]=(-1)))

That's just on the RE end of things.  The SQL Server will do still more optimization completely behind the scenes based on it's current state, memory allocated, etc.



If it's something that runs fairly quickly and doesn't have to be run often, I wouldn't worry about it.

Alan French:

 

John Heizer:



Aside from just running tests on the various possibilities you can go to the menu bar View > SQL (or Ctrl+Q) and see what the underlying SQL is.

Just to add to John's post, I think the View > SQL menu option is only available to organisations who have unlocked the optional RODBA module.