Thanks Robert... I get it. So if I am returning just one unique record, there is no need to check Get Total Number of Matching Rows. Is there a Witango hit to have this checked when not needed?
_____ From: Robert Shubert [mailto:[email protected]] Sent: Tuesday, July 24, 2012 11:33 AM To: [email protected] Subject: RE: TeraScript-Talk: Total Number of Matching Rows, Limit To, No maximun 1. searching for 1 unique record should show Get total number of matching rows unchecked, Limit to 1. Correct. If you believe that you have a unique key but aren't 100% (it doesn't have a unique constraint on it) then you could leave the limit to "no maximum" and then check to make sure that you only received 1 row from the db, and perhaps do maintenance or throw an error if you don't. On the other hand, if there is a unique constraint on it, then it's impossible for there to be more than one row with that ID and you can safely leave the limit to "no maximum", which will execute ever so slightly faster. 2. searching for 10 records should show Get total number of matching rows unchecked, Limit to 10. Same notes as #1. 3. searching for all records but show 10 at a time (scroll through next and previous) should show Get total number of matching rows checked, Limit to 10. And the start value needs to be a variable or argument pointing the starting row of the page. These 3 controls (total count, limit to, start at) are essentially here to support pagination. 4. searching for all records and show all records on a page should show Get total number of matching rows unchecked, No Maximum. Where I am confused is with #4. Wouldn't having both Get total number of matching rows checked and No Maximum give you the same results? Is there more of a server hit for this? Or is the Get total number of matching rows just a count and No Maximum and Limit is the actual number of results returned. The result of the action is always the found result set. What happens inside the server when you check the 'Get total number of matching rows' option is that a second SQL query, one with the same criteria and joins, but with the Select clause simply "count(*)" is executed. This execution happens before the main query that retrieves data. The only thing that this value does is populate a variable returned by the tag @TOTALROWS. So essentially there is a linear connection between this option, a special counting query and the value of @TOTALROWS. Since this option executes a second SQL query, it is best to check this box only when you intend to use @TOTALROWS. Additionally, @TOTALROWS is only meaningful when you use the Start at or Limit to options, therefore, queries that retrieve the entire found dataset (Start at: 1; Limit to: No maximum) @TOTALROWS is redundant with @NUMROWS and <@VARINFO resultset rows> and therefore 'Get number of matching rows' is not needed and shouldn't be used. Sorry for the brain fart moment ;-) _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body.
