Misleading Execution Plan when using TOP n
-
StmtText
select top 20
accountnumber,cli,crt.description,fve.description,bbprs.error
from bbprovisionresponse_T bbprs
inner join bbprovisionrequest_T bbpr on bbprs.bbprovisionrequestid = bbpr.bbprovisionrequestid
inner join carrierrequesttypes_T crt on bbpr.requesttypeid = crt.requesttypeid
inner join filevalidationerrorcodes_T fve on fve.code = cast(bbprs.responseid as varchar(10)) and fve.carrierid = 1000020
inner join clicarrier_T cc on cc.clicarrierid = bbpr.clicarrierid
inner join cli_T c on cc.cliid = c.cli_id
inner join account_T a on c.accountid = a.accountid
order by bbpr.bbprovisionrequestid desc
(1 row(s) affected)
StmtText
|--Top(20)
|--Bookmark Lookup(BOOKMARK: ([Bmk1003]), OBJECT: ([Reach_roms].[dbo].[FileValidationErrorCodes_T] AS [fve]))
|--Nested Loops(Inner Join, OUTER REFERENCES: ([bbprs].[ResponseID]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES: ([c].[AccountID]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCES: ([cc].[CLIID]) WITH PREFETCH)
| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([bbpr].[CLICarrierID]) WITH PREFETCH)
| | | |--Bookmark Lookup(BOOKMARK: ([Bmk1000]), OBJECT: ([Reach_roms].[dbo].[BBProvisionResponse_T] AS [bbprs]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([bbpr].[BBProvisionRequestID]) WITH PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([bbpr].[RequestTypeID]))
| | | | | |--Clustered Index Scan (OBJECT
[Reach_roms].[dbo].[BBProvisionRequest_T].[PK_NEW_BBProvisionRequest]AS [bbpr]), ORDERED BACKWARD)
| | | | | |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[CarrierRequestTypes_T].[PK_D_CPSRequestTypes_T]
AS [crt]), SEEK: ([crt].[RequestTypeID]=[bbpr].[RequestTypeID])
ORDERED FORWARD)
| | | | |--Index Seek(OBJECT: ([Reach_roms].[dbo].[BBProvisionResponse_T].[IX_NCL_BBProvisionRequestID]
AS [bbprs]), SEEK: ([bbprs].[BBProvisionRequestID]=[bbpr].[BBProvisionRequestID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[CLICarrier_T].[PK_CLICarrier_T] AS [cc]), SEEK: ([cc].[CLICarrierID]=[bbpr].[CLICarrierID]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[CLI_T].[PK_CLI] AS [c]), SEEK: ([c].[CLI_ID]=[cc].[CLIID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT: ([Reach_roms].[dbo].[Account_T].[PK_ROMS_Account] AS [a]), SEEK: ([a].[AccountID]=[c].[AccountID]) ORDERED FORWARD)
|--Index Seek(OBJECT: ([Reach_roms].[dbo].[FileValidationErrorCodes_T].[IX_FileValidationErrorCodes_T] AS [fve]), SEEK: ([fve].[Code]=Convert([bbprs].[ResponseID]) AND [fve].[CarrierID]=1000020) ORDERED FORWARD)
(16 row(s) affected)
-
What's misleading about this??
-
Nothing. Sorry - I posted it by mistake before I was ready. I've just spent best part of an hour filling all the rest in as an EDIT, and the blo*** site's gone and lost all my changes.
Grrrrrrrrr.
I'll re-post later.
-
The session times out after a few minutes... always save the post before sending it away
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply