sql server - T-SQL Find matching rows based on BIT parameter values -


i trying create stored procedure returns rows within person table have same value particular columns. want stored procedure accept parameter values such @match_on_forenames bit , @match_on_surname bit. example exec find_matching @match_on_forenames 1, @match_on_surname 1 find rows in table forenames , surname fields match, not matching against itself.

i have stored procedure similar :

alter proc get_matching_records     @match_on_forenames bit,     @match_on_surname bit select distinct     p.forenames, p.surname      person p            exists        (             select                  p1.forenames, p1.surname                              person p1                                                                                                                                                                                                                            (((@match_on_forenames = 1 , p.forenames = p1.forenames , p.id <> p1.id)) or @match_on_forenames = 0)                 , (((@match_on_surname = 1 , p.surname = p1.surname , p.id <> p1.id)) or @match_on_surname = 0)                 )

soon introduce or @match_on_forenames performance of query becomes poor.

any ideas how best approach problem?

many thanks.

this logic:

where exists (select 1               person p1                                                                                                                                                                                                                              (((@match_on_forenames = 1 , p.forenames = p1.forenames , p.id <> p1.id)) or @match_on_forenames = 0)                 , (((@match_on_surname = 1 , p.surname = p1.surname , p.id <> p1.id)) or @match_on_surname = 0)              )

if fast without or, 3 conditions might solve problem:

where (@match_on_forenames = 1 , @match_on_surname = 1 ,        exists (select 1                person p1                                                                                   (p.forenames = p1.forenames , p.id <> p1.id) ,                      (p.surname = p1.surname , p.id <> p1.id)               )       ) or       (@match_on_forenames = 0 , @match_on_surname = 1 ,        exists (select 1                person p1                                                                                   (p.surname = p1.surname , p.id <> p1.id)               )       ) or       (@match_on_forenames = 1 , @match_on_surname = 0 ,        exists (select 1                person p1                                                                                   (p.forenames = p1.forenames , p.id <> p1.id)                )       )

each of exists clauses should fast, based on observation simpler query faster.


Comments

Post a Comment

Popular posts from this blog

android - MPAndroidChart - How to add Annotations or images to the chart -

i working latest mpandroidchart library , looking way add annotations or images clickable. example: once data reaches value event occurs want able display user - there multiple events can occur annotations (not sure if right word) or image added @ point in chart. any pointers welcome. check documentation of yaxis . focus on setspacetop(...) , setaxismaxvalue(...) methods. for background: bar-shadow , disable calling: barchart.setdrawbarshadow(false) or change color: bardataset.setbarshadowcolor(int color)
Read more

javascript - Add class to another page attribute using URL id - Jquery -

i have attached id url. ex: when click link 1 , has #tab-2 id , takes me landing page. in landing page have find element has #tab-2 id have add class .active . i tried no luck. please 1 correct code. thanks js fiddle link html <div class="menu"> <ul> <li><a href="http://jsfiddle.net/gscyoa0j/1/#tab-2">link 1</a></li> <li><a href="http://jsfiddle.net/gscyoa0j/1/#tab-44">link 2</a></li> <li><a href="http://jsfiddle.net/gscyoa0j/1/#tab-74">link 3</a></li> </ul> </div> landing page <div class="tab"> <ul> <li><a id="tab-2" class="active" >link 1 content</a></li> <li><a id="tab-44" >link 2 content</a></li> <li><a id="tab-74" >link 3 content</a></l...
Read more

firefox - Where is 'webgl.osmesalib' parameter? -

i'm trying webgl running on firefox (v37.0.2) via software acceleration mesa. tried following steps have been stated in article: http://www.binarytides.com/enable-webgl-firefox-ubuntu/ however, unable find parameter named ' webgl.osmesalib ' in about:config section? has been removed (or renamed)? if yes, how should instruct firefox use mesa's software acceleration webgl? looks support osmesa removed in favour llvmpipe.
Read more