Why CASE statement in Where clause for MS Sql server -

-

doing performance tuning on 3rd party vendor system in ms sql server 2012.

they lot of clauses this:

where  (    case        when  @searchid = '' 1        when ((c.sapcustomernumber = @searchid ) or (c.sapcustomernumber = @sapid)) 1     else 0     end = 1                 )

other confusing query plan, advantage if there case 1 or 0 in clause?

thanks

the common, far, cause sort of code appear new sql, case expressions or both. somehow become fixated on case , decide should used conditional evaluations.

this mistake , can replaced simpler boolean logical, @bogdan suggested in comments:

((c.sapcustomernumber = @searchid ) or (c.sapcustomernumber = @sapid) or (@searchid = '' ))

the second reason can done if attempting enforce order of evaluation of predicates1. case documented evaluate when conditions in order (provided scalar expressions, not aggregates). i'd not recommend writes code though - it's mistaken first form instead. , if particular evaluation order best today, today's data, who's whether still correct tomorrow, or in month or years time?

1sql not guarantee evaluation order where clause predicates in general, nor form of short-circuiting evaluation. optimizer free re-order predicates - both within where clause , in join/on clauses - try achieve overall result cheaply possible. shouldn't, generally, try prevent doing so.

if it's not picking efficient plan, far better fixed updating/creating indexes , statistics or forcing specific plan, rather using case.


Comments

Post a Comment

Popular posts from this blog

python - pip install -U PySide error -

-
does know how avoid following error when running pip install -u pyside per official website instructions here: https://pypi.python.org/pypi/pyside/#installing-pyside-on-a-mac-os-x-system note have done brew install qt successfully. you using pip version 7.0.3, version 7.1.2 available. should consider upgrading via 'pip install --upgrade pip' command. collecting pyside using cached pyside-1.2.4.tar.gz installing collected packages: pyside running setup.py install pyside complete output command /applications/anaconda/bin/python -c "import setuptools, tokenize;__file__='/private/var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-build-cpxemt/pyside/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-0r7hri-record/install-record.txt --single-version-externally-managed --compile: ...
Read more

arrays - C++ error: a brace-enclosed initializer is not allowed here before ‘{’ token -

-
simple question: i'm trying initialize array within c++ class declaration: using namespace std; #include <string> class myclass{ public: string myarray[] = {"a","b","c"}; }; and i'm getting error: error: brace-enclosed initializer not allowed here before ‘{’ token no, without complied c++11 compiler, cannot initialize member array in declaration. have initialize array member in constructor. , don't use open array if know number of elements initialize array.
Read more

cytoscape.js - How to add nodes to Dagre layout with Cytoscape -

-
i trying add nodes dagre graph through cityscape.js. problem nodes not painted. code have: cy.add({ group: "nodes", data: { id:"n0",name:"n0",weight: 75 }, }); $.ajax(url).done(function(jsontree){ var newnodes=[]; for(var i=1;i<6;i++){ //var child = jsontree.result[i]; newnodes.push( { group: "nodes", data: { id: "n"+i, name:"n"+i}}, { group: "edges", data: { id: "e"+i, source: "n0", target: "n"+i } } ); } cy.add(newnodes); any ideas? have used loaded function reload graph not work. not know if have use specific function reload graph. thank in advance. you need add positions new nodes. cytoscape.js docs : it important note positions of newly added nodes must defined when calling cy.add(). nodes can not placed in graph without valid position — otherwise not displayed. yo...
Read more