sql server - Performance of a T-sql query retrieving data -

sql server - Performance of a T-sql query retrieving data -

declare @table table( mnbr int, pid int, pname varchar(10), jid int, jname varchar(10)) insert @table values(150 , 1 , 'mark' , 8 ,'jerry') ,(250 , 1 , 'mark' , 8 , 'jerry') ,(350 , 2 , 'jim' , 9 , 'luke') ,(450 , 2 , 'jim' , 9 , 'luke') ,(550 , 2 , 'jim' , 10 , 'jude') ,(650 , 3 , 'andy' , 11 , 'matt') ,(750 , 4 , 'brian' , 21 , 'chris') ,(850 , 4 , 'brian' , 7 , 'mac') ,(950 , 5 , 'jean ' , 21 , 'chris')

for above data..... output should shown below:

350 2 jim 9 luke 450 2 jim 9 luke 550 2 jim 10 jude 750 4 brian 21 chris 850 4 brian 7 mac

in case of mnbr 150 , 250.... primary owner mark has 2 joint accounts , on both joint accounts joint owner jerry. mark's accounts should not displayed.

in case if mnbr: 350, 450, 550

jim has 3 joint accounts , not 3 joint accounts belong same joint owner. 3 accounts have displayed.

mnbr 650 has 1 joint business relationship 1 joint owner. should not displayed.

i have written next query returns info require.

select * @table pname in ( select pname @table pname in (select pname @table grouping pname,jname having count(*)!>1) grouping pname having count(*)>1 )

i know, query going provide performance?

you simplify query to

select * @table pname in (select pname @table grouping pname having max(jid) <> min(jid));

whether perform improve or not need test against much larger info set 1 provided in question.

in case, more of import factor in running either query against larger info set more have index on either combination of pname, jname fields or pname, jid fields.

assuming table name of 'accounts' helpfull index might like:

create nonclustered index [ix_accounts_pname_jid] on [dbo].[accounts] ( [pname] asc, [jid] asc ) (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

sql sql-server tsql