1

Lets say, I have a query,

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam

Here MyParam is parameter and optional. So, it only check MyColumn = MyParam if MyParam is not 0. But our DBA is saying OR will makes it slow and db will suffers. Another option is,

IF MyParam = 0 
SELECT * FROM MyTable WHERE MyColumn = MyParam

The problem with this approach is that we have lot of optional parameters. So, our query become very very big. Another option is CASE.

So what you guys suggest. I am talking about in general whether Oracle or SQL Server.

  • 1
    Only way to find answer is to set a test case. Load the table with random data (not 10 rows), then run both cases using a utility like runstats for Oracle and select the option that is more efficient. I can't say anything about SS. After test if you find OR option is better and your DBA says otherwise, ask him or her to prove it with numbers and then show your testing. – Raj Mar 21 '17 at 12:39
  • @Raj, talking about generally. Is it fine to use OR for optional value check. – user960567 Mar 21 '17 at 13:37
  • It is a decision between writing lot of code and minimal. You could write this in pl/sql and generate dynamic sql statement and use refcursors etc. But it is a decision you have to make. Me? I would test it and use the one that makes most efficient execution and results into maintainable code. – Raj Mar 21 '17 at 14:01
  • There's no such thing as "general" performance advice. Tuning depends heavily on your specific database, your specific tables, your specific indexes, and your specific query. – jpmc26 Mar 21 '17 at 17:49
5

In sql-server:

One option is dynamic sql, another is option (recompile).

using option (recompile):

select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile);

dynamic sql example:

declare @sql nvarchar(max);

if nullif(@MyParam,0) is not null
  begin;
  set @sql = 'select * from MyTable where MyColumn=@MyParam;'
  exec sp_exeuctesql @sql, '@MyParam int', @MyParam;
  end;
else 
  begin;
  set @sql = 'select * from MyTable;'
  exec sp_exeuctesql @sql;
  end;

Reference:

  • Well, since the OP said they have multiple optional parameters, I would probably just build a single dynamic query, and append any clauses that need to be used. You can hard-code the full list of parameters and values, and always use the whole list, even if not all of them are populated or referenced in the dynamic query. I wrote about this technique here. – Aaron Bertrand Mar 21 '17 at 13:19
  • @AaronBertrand True, my example is pretty simple and only addresses the example in the question instead of the whole question. The whole where should be built dynamically checking individual parameters, as in Catch-all queries - Gail Shaw – SqlZim Mar 21 '17 at 13:24
4

I just ran a test in my environment against AdventureWorks and found that the CASE approach works best. Using the OR approach made the engine use the clustered index where the CASE statement let it use the non-clustered index.

so your query could look like this.

SELECT *
FROM MyTable
WHERE CASE WHEN @MyParam = 0 THEN 1
           WHEN MyColumn = @MyParam THEN 1
           ELSE 0
           END = 1

However, if as you say there are lots of parameters then it's unlikely you are going to be able to use an index in any case. So you might leave it alone. My testing was for a simple query.

  • I would expect this to change as you add more optional parameters and CASE expressions. – Aaron Bertrand Mar 21 '17 at 13:22
  • @AaronBertrand, would you believe that I was updating my answer as you were posting your comment? – Jonathan Fite Mar 21 '17 at 13:23
  • Yes, I would. :-) – Aaron Bertrand Mar 21 '17 at 13:23
  • Its mean for OR, db is using index. @AaronBertrand, should I assume dynamic SQL is best way to tackle this both in Oracle and SQL Server. – user960567 Mar 21 '17 at 14:27
  • 2
    @user960567 It was just a lame joke about Oracle's luxury pricing. I don't have any customers who can afford it, so I barely know it enough to spell it. – Aaron Bertrand Mar 21 '17 at 16:36
3

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam

But our DBA is saying OR will makes it slow and db will suffers.

There are several flaws with this:

  • The programming world, and especially the DB(A) world is chock full with adages like this that get perpetuated ad nauseam. "Never use NOT EXISTS, never use NOT IN, avoid this, avoid that, you must do XYZ at all cost, avoid OR at all cost" and so on. The problem with these things is that they may have been true at some point in time, for a specific version of a specific RDBMS, and quite often for a specific subset of parameter settings for that specific RDBMS version.
  • This is in spirit like optimizing before knowing that there is something to optimize. The only thing that is completely obvious here is that you are now using more time on this statement (by thinking about it, asking SE etc.). So the cost went up already. Whether the statement itself is "bad" has not even shown, and if it truly is "bad", someone has not even proven that it is bad enough to warrant action.

So, what you should do in such cases: create a test case, and do a benchmark. Nothing else can help you.

For your particular case:

  • The DB could in theory optimize the statement and recognize the issue; it could then be almost exactly as fast as if you had done it differently.
  • The overhead could be negligible and totally worthless to fuss about. It could take 1ms more for a request that takes 1000ms end-to-end, with a nice big chunk of optimize-worthy code that takes the other 999ms. Wasting even a minute of your time to bother about these things could be too much.
  • On the other hand, this issue could have a tremendous impact on your performance.

Make a benchmark; make tests; evaluate DB execution statistics and so on. Then you will know for sure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.