Performance of IN with OR operator





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty{ margin-bottom:0;
}






up vote
1
down vote

favorite












I have common clause in most of the procedures like



Select * from TABLE A  + Joins  where <Conditions>

And
(
-- All Broker
('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID))
OR
(
A.BrokerID in
(
Select BrokerID from SiteUserBroker where SiteUserID
= @SiteUserID)
)
)


Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.



I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?



Any ideas are appreciated.










share|improve this question









New contributor




Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.


























    up vote
    1
    down vote

    favorite












    I have common clause in most of the procedures like



    Select * from TABLE A  + Joins  where <Conditions>

    And
    (
    -- All Broker
    ('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID))
    OR
    (
    A.BrokerID in
    (
    Select BrokerID from SiteUserBroker where SiteUserID
    = @SiteUserID)
    )
    )


    Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.



    I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?



    Any ideas are appreciated.










    share|improve this question









    New contributor




    Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      1
      down vote

      favorite









      up vote
      1
      down vote

      favorite











      I have common clause in most of the procedures like



      Select * from TABLE A  + Joins  where <Conditions>

      And
      (
      -- All Broker
      ('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID))
      OR
      (
      A.BrokerID in
      (
      Select BrokerID from SiteUserBroker where SiteUserID
      = @SiteUserID)
      )
      )


      Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.



      I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?



      Any ideas are appreciated.










      share|improve this question









      New contributor




      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      I have common clause in most of the procedures like



      Select * from TABLE A  + Joins  where <Conditions>

      And
      (
      -- All Broker
      ('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID))
      OR
      (
      A.BrokerID in
      (
      Select BrokerID from SiteUserBroker where SiteUserID
      = @SiteUserID)
      )
      )


      Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.



      I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?



      Any ideas are appreciated.







      sql-server






      share|improve this question









      New contributor




      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited Nov 29 at 9:36









      Michael Green

      13.9k82957




      13.9k82957






      New contributor




      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked Nov 29 at 5:42









      Moons

      1064




      1064




      New contributor




      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Moons is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          2
          down vote













          You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.



          What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
          For example:



          IF (
          -- All Broker
          ( 'True' = (
          SELECT AllBrokers
          FROM SiteUser
          WHERE ID = @SiteUserID
          )
          )
          OR
          ( A.BrokerID IN (
          SELECT BrokerID
          FROM SiteUserBroker
          WHERE SiteUserID = @SiteUserID
          )
          )
          )
          SELECT *
          FROM TABLE A <+ Joins>
          WHERE <Conditions>
          ELSE
          SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
          WHERE 1=0 -- Forces an empty set


          This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.



          An even better approach would be to raise an explicit access denied message, for example:



          ELSE
          THROW <Access Denied Error>


          However, this approach may require application code changes to handle the message correctly.



          BTW - never use SELECT * in production code, see this thread...



          HTH






          share|improve this answer





















          • I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
            – Moons
            Nov 29 at 7:40










          • You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
            – SQLRaptor
            Nov 29 at 7:52










          • i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
            – Moons
            Nov 29 at 10:19


















          up vote
          1
          down vote













          First I'd suggest you check you do (or will) have an actual problem. Mock up a lot of data representative of some future state, perhaps by duplicating current data ten or twenty times. Then check performance. If it's acceptable (how do you define that?) all's well and you can leave your query alone.



          That said, there are other designs that produce your desired outcome. One would be to stop using SiteUser.AllBrokers as a proxy and use the actual data. In other words, create a row in SiteUserBroker for every broker for those users which have access to every broker. The permission check then reduces to a simple inner join.



          Select * from TABLE A  + Joins  
          inner join SiteUserBroker as sub
          on sub.BrokerId = A.BrokerId
          and sub.SiteUserID = @SiteUserID
          where <Conditions>


          With an index on SiteUserBroker (SiteUserID, BrokerID) access will be fast.



          The actual performance compared to your current code will depend on the data distribution. If there are very many users and very many brokers, and most of the users have access to all brokers, this proposal will produce a lot of rows compared to your current solution. In this case inefficient access to a small number of rows (current) may be faster than efficient access to very many rows (proposed). Alternatively, if most users have most, but not all, brokers this proposal will increase the row count in SiteUserBroker only modestly. Access times will be no different to they are currently and the code will likely be faster. Performance will be affected by working set size, IO performance and other things, too. As is so often the case, you must test and see how it looks on your hardware, with your data.



          Changes to the Broker list have to be accommodated. If you add a new broker, to which users should it be allocated also so they maintain their "all brokers" status? To this end I'd suggest you retain SiteUser.AllBrokers. Removing a broker, of course, removes it from all users regardless.






          share|improve this answer























          • I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
            – Moons
            Nov 29 at 10:13













          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          Moons is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223718%2fperformance-of-in-with-or-operator%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          2 Answers
          2






          active

          oldest

          votes








          2 Answers
          2






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          2
          down vote













          You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.



          What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
          For example:



          IF (
          -- All Broker
          ( 'True' = (
          SELECT AllBrokers
          FROM SiteUser
          WHERE ID = @SiteUserID
          )
          )
          OR
          ( A.BrokerID IN (
          SELECT BrokerID
          FROM SiteUserBroker
          WHERE SiteUserID = @SiteUserID
          )
          )
          )
          SELECT *
          FROM TABLE A <+ Joins>
          WHERE <Conditions>
          ELSE
          SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
          WHERE 1=0 -- Forces an empty set


          This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.



          An even better approach would be to raise an explicit access denied message, for example:



          ELSE
          THROW <Access Denied Error>


          However, this approach may require application code changes to handle the message correctly.



          BTW - never use SELECT * in production code, see this thread...



          HTH






          share|improve this answer





















          • I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
            – Moons
            Nov 29 at 7:40










          • You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
            – SQLRaptor
            Nov 29 at 7:52










          • i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
            – Moons
            Nov 29 at 10:19















          up vote
          2
          down vote













          You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.



          What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
          For example:



          IF (
          -- All Broker
          ( 'True' = (
          SELECT AllBrokers
          FROM SiteUser
          WHERE ID = @SiteUserID
          )
          )
          OR
          ( A.BrokerID IN (
          SELECT BrokerID
          FROM SiteUserBroker
          WHERE SiteUserID = @SiteUserID
          )
          )
          )
          SELECT *
          FROM TABLE A <+ Joins>
          WHERE <Conditions>
          ELSE
          SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
          WHERE 1=0 -- Forces an empty set


          This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.



          An even better approach would be to raise an explicit access denied message, for example:



          ELSE
          THROW <Access Denied Error>


          However, this approach may require application code changes to handle the message correctly.



          BTW - never use SELECT * in production code, see this thread...



          HTH






          share|improve this answer





















          • I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
            – Moons
            Nov 29 at 7:40










          • You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
            – SQLRaptor
            Nov 29 at 7:52










          • i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
            – Moons
            Nov 29 at 10:19













          up vote
          2
          down vote










          up vote
          2
          down vote









          You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.



          What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
          For example:



          IF (
          -- All Broker
          ( 'True' = (
          SELECT AllBrokers
          FROM SiteUser
          WHERE ID = @SiteUserID
          )
          )
          OR
          ( A.BrokerID IN (
          SELECT BrokerID
          FROM SiteUserBroker
          WHERE SiteUserID = @SiteUserID
          )
          )
          )
          SELECT *
          FROM TABLE A <+ Joins>
          WHERE <Conditions>
          ELSE
          SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
          WHERE 1=0 -- Forces an empty set


          This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.



          An even better approach would be to raise an explicit access denied message, for example:



          ELSE
          THROW <Access Denied Error>


          However, this approach may require application code changes to handle the message correctly.



          BTW - never use SELECT * in production code, see this thread...



          HTH






          share|improve this answer












          You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.



          What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
          For example:



          IF (
          -- All Broker
          ( 'True' = (
          SELECT AllBrokers
          FROM SiteUser
          WHERE ID = @SiteUserID
          )
          )
          OR
          ( A.BrokerID IN (
          SELECT BrokerID
          FROM SiteUserBroker
          WHERE SiteUserID = @SiteUserID
          )
          )
          )
          SELECT *
          FROM TABLE A <+ Joins>
          WHERE <Conditions>
          ELSE
          SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
          WHERE 1=0 -- Forces an empty set


          This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.



          An even better approach would be to raise an explicit access denied message, for example:



          ELSE
          THROW <Access Denied Error>


          However, this approach may require application code changes to handle the message correctly.



          BTW - never use SELECT * in production code, see this thread...



          HTH







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 29 at 7:30









          SQLRaptor

          1,7771114




          1,7771114












          • I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
            – Moons
            Nov 29 at 7:40










          • You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
            – SQLRaptor
            Nov 29 at 7:52










          • i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
            – Moons
            Nov 29 at 10:19


















          • I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
            – Moons
            Nov 29 at 7:40










          • You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
            – SQLRaptor
            Nov 29 at 7:52










          • i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
            – Moons
            Nov 29 at 10:19
















          I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
          – Moons
          Nov 29 at 7:40




          I dont use select * as this is the sample query, actually most of my queries are huge with joins/stuff/group by/having etc of complicated data with joins and lot more and in the end THIS clause with lot other where/in operators - your approach is promising but i want to avoid too much duplication. My more worrying thing is soemtimes i need to show just top 50 and if it scans full table it is more worrying me. do you have any other alternative as well. I also dont prefer to use Dynamic Sql as i use ORM models
          – Moons
          Nov 29 at 7:40












          You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
          – SQLRaptor
          Nov 29 at 7:52




          You can wrap the permission check in a function or a SP, and call it to avoid the duplication of full permission check code, but you are going to check for permissions every time, so some 'duplication' will be unavoidable. If you are in a position to discuss alternative security architectures, that's a different story. The table scans are not directly related to your question, but may be you are prematurely optimizing it? What worries me is the fact that most of your queries are huge, which typically indicates that your schema is suboptimal. That is where I would invest my efforts first.
          – SQLRaptor
          Nov 29 at 7:52












          i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
          – Moons
          Nov 29 at 10:19




          i was not worried about permission check duplication but the other select part. It is somehow between 50-100 lines in most of my reports. But again thanks for all suggestions. I was actually more into trying to improve performance of most running pages and trying to reduce procedures execution time from around half a second to few milliseconds.
          – Moons
          Nov 29 at 10:19












          up vote
          1
          down vote













          First I'd suggest you check you do (or will) have an actual problem. Mock up a lot of data representative of some future state, perhaps by duplicating current data ten or twenty times. Then check performance. If it's acceptable (how do you define that?) all's well and you can leave your query alone.



          That said, there are other designs that produce your desired outcome. One would be to stop using SiteUser.AllBrokers as a proxy and use the actual data. In other words, create a row in SiteUserBroker for every broker for those users which have access to every broker. The permission check then reduces to a simple inner join.



          Select * from TABLE A  + Joins  
          inner join SiteUserBroker as sub
          on sub.BrokerId = A.BrokerId
          and sub.SiteUserID = @SiteUserID
          where <Conditions>


          With an index on SiteUserBroker (SiteUserID, BrokerID) access will be fast.



          The actual performance compared to your current code will depend on the data distribution. If there are very many users and very many brokers, and most of the users have access to all brokers, this proposal will produce a lot of rows compared to your current solution. In this case inefficient access to a small number of rows (current) may be faster than efficient access to very many rows (proposed). Alternatively, if most users have most, but not all, brokers this proposal will increase the row count in SiteUserBroker only modestly. Access times will be no different to they are currently and the code will likely be faster. Performance will be affected by working set size, IO performance and other things, too. As is so often the case, you must test and see how it looks on your hardware, with your data.



          Changes to the Broker list have to be accommodated. If you add a new broker, to which users should it be allocated also so they maintain their "all brokers" status? To this end I'd suggest you retain SiteUser.AllBrokers. Removing a broker, of course, removes it from all users regardless.






          share|improve this answer























          • I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
            – Moons
            Nov 29 at 10:13

















          up vote
          1
          down vote













          First I'd suggest you check you do (or will) have an actual problem. Mock up a lot of data representative of some future state, perhaps by duplicating current data ten or twenty times. Then check performance. If it's acceptable (how do you define that?) all's well and you can leave your query alone.



          That said, there are other designs that produce your desired outcome. One would be to stop using SiteUser.AllBrokers as a proxy and use the actual data. In other words, create a row in SiteUserBroker for every broker for those users which have access to every broker. The permission check then reduces to a simple inner join.



          Select * from TABLE A  + Joins  
          inner join SiteUserBroker as sub
          on sub.BrokerId = A.BrokerId
          and sub.SiteUserID = @SiteUserID
          where <Conditions>


          With an index on SiteUserBroker (SiteUserID, BrokerID) access will be fast.



          The actual performance compared to your current code will depend on the data distribution. If there are very many users and very many brokers, and most of the users have access to all brokers, this proposal will produce a lot of rows compared to your current solution. In this case inefficient access to a small number of rows (current) may be faster than efficient access to very many rows (proposed). Alternatively, if most users have most, but not all, brokers this proposal will increase the row count in SiteUserBroker only modestly. Access times will be no different to they are currently and the code will likely be faster. Performance will be affected by working set size, IO performance and other things, too. As is so often the case, you must test and see how it looks on your hardware, with your data.



          Changes to the Broker list have to be accommodated. If you add a new broker, to which users should it be allocated also so they maintain their "all brokers" status? To this end I'd suggest you retain SiteUser.AllBrokers. Removing a broker, of course, removes it from all users regardless.






          share|improve this answer























          • I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
            – Moons
            Nov 29 at 10:13















          up vote
          1
          down vote










          up vote
          1
          down vote









          First I'd suggest you check you do (or will) have an actual problem. Mock up a lot of data representative of some future state, perhaps by duplicating current data ten or twenty times. Then check performance. If it's acceptable (how do you define that?) all's well and you can leave your query alone.



          That said, there are other designs that produce your desired outcome. One would be to stop using SiteUser.AllBrokers as a proxy and use the actual data. In other words, create a row in SiteUserBroker for every broker for those users which have access to every broker. The permission check then reduces to a simple inner join.



          Select * from TABLE A  + Joins  
          inner join SiteUserBroker as sub
          on sub.BrokerId = A.BrokerId
          and sub.SiteUserID = @SiteUserID
          where <Conditions>


          With an index on SiteUserBroker (SiteUserID, BrokerID) access will be fast.



          The actual performance compared to your current code will depend on the data distribution. If there are very many users and very many brokers, and most of the users have access to all brokers, this proposal will produce a lot of rows compared to your current solution. In this case inefficient access to a small number of rows (current) may be faster than efficient access to very many rows (proposed). Alternatively, if most users have most, but not all, brokers this proposal will increase the row count in SiteUserBroker only modestly. Access times will be no different to they are currently and the code will likely be faster. Performance will be affected by working set size, IO performance and other things, too. As is so often the case, you must test and see how it looks on your hardware, with your data.



          Changes to the Broker list have to be accommodated. If you add a new broker, to which users should it be allocated also so they maintain their "all brokers" status? To this end I'd suggest you retain SiteUser.AllBrokers. Removing a broker, of course, removes it from all users regardless.






          share|improve this answer














          First I'd suggest you check you do (or will) have an actual problem. Mock up a lot of data representative of some future state, perhaps by duplicating current data ten or twenty times. Then check performance. If it's acceptable (how do you define that?) all's well and you can leave your query alone.



          That said, there are other designs that produce your desired outcome. One would be to stop using SiteUser.AllBrokers as a proxy and use the actual data. In other words, create a row in SiteUserBroker for every broker for those users which have access to every broker. The permission check then reduces to a simple inner join.



          Select * from TABLE A  + Joins  
          inner join SiteUserBroker as sub
          on sub.BrokerId = A.BrokerId
          and sub.SiteUserID = @SiteUserID
          where <Conditions>


          With an index on SiteUserBroker (SiteUserID, BrokerID) access will be fast.



          The actual performance compared to your current code will depend on the data distribution. If there are very many users and very many brokers, and most of the users have access to all brokers, this proposal will produce a lot of rows compared to your current solution. In this case inefficient access to a small number of rows (current) may be faster than efficient access to very many rows (proposed). Alternatively, if most users have most, but not all, brokers this proposal will increase the row count in SiteUserBroker only modestly. Access times will be no different to they are currently and the code will likely be faster. Performance will be affected by working set size, IO performance and other things, too. As is so often the case, you must test and see how it looks on your hardware, with your data.



          Changes to the Broker list have to be accommodated. If you add a new broker, to which users should it be allocated also so they maintain their "all brokers" status? To this end I'd suggest you retain SiteUser.AllBrokers. Removing a broker, of course, removes it from all users regardless.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 29 at 9:32

























          answered Nov 29 at 9:24









          Michael Green

          13.9k82957




          13.9k82957












          • I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
            – Moons
            Nov 29 at 10:13




















          • I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
            – Moons
            Nov 29 at 10:13


















          I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
          – Moons
          Nov 29 at 10:13






          I have lot of data already i am trying to optimize the queries which is called thousands of times - say which takes 300 milliseconds to say less than 10 milliseconds. I need AllBroker list so cant remove it, Caching is the alternate way but want to avoid till we have 10X more traffic. But thanks for Join idea. And yes we have few to couple hundreds brokers per company (around 100) and users and brokers are added fast. Just for Admin - allbroker is true so dont need to reassign them, hence was using AllBrokers Columns, in lot of REPORTS, and other queries
          – Moons
          Nov 29 at 10:13












          Moons is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          Moons is a new contributor. Be nice, and check out our Code of Conduct.













          Moons is a new contributor. Be nice, and check out our Code of Conduct.












          Moons is a new contributor. Be nice, and check out our Code of Conduct.
















          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223718%2fperformance-of-in-with-or-operator%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          AnyDesk - Fatal Program Failure

          How to calibrate 16:9 built-in touch-screen to a 4:3 resolution?

          QoS: MAC-Priority for clients behind a repeater