indexing - Why this query still taking time to return 500K records having 14 columns -

i looking advise regarding improvement in query ( if any).

please @ below query plan , notice of tables using index seek ( non-clustered) , nested loops. still query taking 00:01:17 time extract 500k rows. please suggest if wrong or needs improvement in it.

stmttext    |--top(top expression:((500000)))     |--nested loops(inner join, outer references:([mydatabase].[dbo].[cardmerchant].[cardid], [expr1028]) optimized unordered prefetch)          |--nested loops(inner join, outer references:([mydatabase].[dbo].[merchant].[merchantid], [expr1027]) unordered prefetch)          |    |--nested loops(inner join, outer references:([mydatabase].[dbo].[merchantgroup].[merchantgroupid], [expr1026]) unordered prefetch)          |    |    |--nested loops(inner join, outer references:([mydatabase].[dbo].[regionalgroup].[id]))          |    |    |    |--nested loops(inner join, outer references:([mydatabase].[dbo].[company].[companyid]))          |    |    |    |    |--nested loops(inner join, outer references:([mydatabase].[dbo].[company].[companygroupid]))          |    |    |    |    |    |--index scan(object:([mydatabase].[dbo].[company].[ix_companygroupid]))          |    |    |    |    |    |--index seek(object:([mydatabase].[dbo].[companygroup].[ix_name]), seek:([mydatabase].[dbo].[companygroup].[companygroupid]=[mydatabase].[dbo].[company].[companygroupid]) ordered forward)          |    |    |    |    |--index seek(object:([mydatabase].[dbo].[regionalgroup].[ix_companyid]), seek:([mydatabase].[dbo].[regionalgroup].[companyid]=[mydatabase].[dbo].[company].[companyid]) ordered forward)          |    |    |    |--index seek(object:([mydatabase].[dbo].[merchantgroup].[ix_regionalgroupid]), seek:([mydatabase].[dbo].[merchantgroup].[regionalgroupid]=[mydatabase].[dbo].[regionalgroup].[id]) ordered forward)          |    |    |--index seek(object:([mydatabase].[dbo].[merchant].[ix_merchantgroupid]), seek:([mydatabase].[dbo].[merchant].[merchantgroupid]=[mydatabase].[dbo].[merchantgroup].[merchantgroupid]) ordered forward)          |    |--index seek(object:([mydatabase].[dbo].[cardmerchant].[ix_cardmerchant_merchantid]), seek:([mydatabase].[dbo].[cardmerchant].[merchantid]=[mydatabase].[dbo].[merchant].[merchantid]) ordered forward)          |--index seek(object:([mydatabase].[dbo].[view_transaction].[ix_merchantcardid]), seek:([mydatabase].[dbo].[view_transaction].[merchantcardid]=[mydatabase].[dbo].[cardmerchant].[cardid]) ordered forward)

shall thankful you.

cheers,

the question might not be: why take 1:17 fetch information, rather: why take 1:17 data in client application.

where run query? if in ssms lot of time 'lost' in loading 500k records results-grid. if want 'measure' query switch on 'discard results after execution' option, or dump result in different table. both still have overhead on top of actual query, lot less building large grid have.

in ssms

• open new query
• in menu select query / query options
• select results pane
• check "discard result after execution"