I’m wondering how you are determining if the query results of the tuning session candidates match that of the original statement - do you use a SQL MINUS or do you look at the rowcount or something else?
Also, does the default batch optimizer setting to compare the execution time of that on the server and NOT to retrieve the entire data set: does this have an impact on how the result comparison is done?
Thanks!
Question: I’m wondering how you are determining if the query results of the tuning session candidates match that of the original statement - do you use a SQL MINUS or do you look at the rowcount or something else?
Answer: When SELECT SQL statements are executed, a comparison of the result is done to further insure that result set for an alternative SQL statement is the same as the original SQL statement. When you select the “Run on Server” option from the Execution Method options page, the comparison made between the original SQL statement and the SQL alternatives is the number of rows returned. No comparison of the result data is performed.
When you select the “Run on Client” option, the comparison is done between the hash values of the data. To compare the result set of the original and alternative SQL statements, each row of the result set is hashed and then the hash values are stored in the memory of the client computer to compare the hash results of the original SQL statement with the hash results of the SQL alternatives. The data is not stored in memory nor on the disk drive of the client computer.
Question: Also, does the default batch optimizer setting to compare the execution time of that on the server and NOT to retrieve the entire data set: does this have an impact on how the result comparison is done?
Answer: As you can see from the answer above, the answer to this question is “yes”.
Perfect!