|
northyen.dk
Starting Member
16 Posts |
 Posted - 2014-06-24 : 08:20:48
|
| Hi,While trying to figure out what exactly the built-in data retrival functions does in Dynamics NAV, I bumped into this query, which is actually quite a bit faster than the non-TOP 1-SELECT TOP (1) "timestamp","Entry No_","Date and Time","Time","User ID","Table No_","Field No_","Type of Change","Old Value","New Value","Primary Key","Primary Key Field 1 No_","Primary Key Field 1 Value","Primary Key Field 2 No_","Primary Key Field 2 Value","Primary Key Field 3 No_","Primary Key Field 3 Value","Record ID" FROM "Demo Database NAV (7-1)".dbo."Demo$Change Log Entry" WITH(READUNCOMMITTED) WHERE ("Old Value"='Edit Production BOM & Routing') ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN)The query cost for TOP 1 one is ~25% compared toSELECT "timestamp","Entry No_","Date and Time","Time","User ID","Table No_","Field No_","Type of Change","Old Value","New Value","Primary Key","Primary Key Field 1 No_","Primary Key Field 1 Value","Primary Key Field 2 No_","Primary Key Field 2 Value","Primary Key Field 3 No_","Primary Key Field 3 Value","Record ID"FROM [Demo Database NAV (7-1)]..[Demo$Change Log Entry] WITH(READUNCOMMITTED) WHERE [Old Value] = 'Edit Production BOM & Routing' ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN)Which is 75%.The differences in the "Clustered Index Scan" which both queries uses, are "Estimated Number of Rows", "Estimated Subtree Cost" and "Operator Cost". The TOP 1 query is quite a bit lower on those three.So, how come it's faster? |
|
SELECT TOP 1 faster on one row results, why?