Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

Return First Matching Query

I have a set of business rules that are prioritized. These rules specify how a customer is located in the database (by various search criteria). The first matching rule wins. Within a stored procedure, how can I basically do something like this:

--Return this if there are rows
SELECT * FROM table WHERE firstCriteria = "firstValue"

-- Else Return this if there are rows
SELECT * FROM table WHERE secondCriteria = "secondValue"

-- Else Return this if there are rows
SELECT * FROM table WHERE thirdCriteria = "thirdValue"

-- etc.

Open in new window


Due to how are database is structured, I have determined that it will end up being the most efficient to break the queries out into separate ones rather than have a compound WHERE clause.

Now, as I am returning this to .NET code, I actually need to open a cursor in order to have .NET pick up the data. I think this might be where I'm getting thrown (mentally). I believe I can do a SELECT COUNT(1) for each query to check for rows before opening the cursor, but I thought that approach might be cumbersome, messy, slow, or all. I'm trying to write as clean of a query as possible (but willing to make sacrifices if necessary).

I'm afraid my PL/SQL skills are less than padawan level, so I turn to the Oracle gods. I'm not necessarily looking for the complete solution, but a nudge in the right direction would be most appreciated.

Thanks!
0
käµfm³d   👽
Asked:
käµfm³d   👽
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Not sure three queries will be 'better' than a single one but it is your requirements.

See if this is what you are after.


drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','1');
insert into tab1 values('b','1');
insert into tab1 values('c','1');
commit;

select col1,col2 from
(
select col1, col2, criteria, min(criteria) over() min_criteria from
(
select 1 criteria, t1.* from tab1 t1 where col1='z'
union
select 2 criteria, t1.* from tab1 t1 where col1='y'
union
select 3 criteria, t1.* from tab1 t1 where col1='c'
)
)
where criteria=min_criteria
/

Open in new window

0
 
sdstuberCommented:
Combine all 3 queries into one (that way you only have to read the table once), assign numbers to identify the criteria you want.  Rank the results.  Take whatever ends up in Rank 1


SELECT *
  FROM (SELECT RANK() OVER (ORDER BY n) rn, x.*
          FROM (SELECT
                           CASE
                               WHEN firstcriteria = 'firstValue' THEN 1
                               WHEN secondcriteria = 'secondValue' THEN 2
                               WHEN thirdcriteria = 'thirdValue' THEN 3
                           END                      
                           n,
                       t.*
                  FROM yourtable t
                 WHERE firstcriteria = 'firstValue'
                    OR secondcriteria = 'secondValue'
                    OR thirdcriteria = 'thirdValue') x)
 WHERE rn = 1
0
 
Mohit VijayCommented:
Use dataReader. When you do DataReader.Read() it will give you next row, if row is not there result will be false.

It will be kind of like this

DataReader dr = Sql.ExccuteQuery()

While (dr.read())
{
var field1 = dr["field]
}
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
käµfm³d 👽Author Commented:
@slightwv & sdstuber

Won't your approach execute every query? I'm only trying to execute as a previous query fails to match (probably wasn't clear from the OP).

The indexes we have on the tables aren't playing nice with these requirements. I'm attempting to exhaust all manner of query manipulation before I go back to the DBAs to request a new index be added.

@VjSoft
I know how to read the data in .NET. That is not my question. Thank you for your input, though.
0
 
slightwv (䄆 Netminder) Commented:
I'll let sdstuber continue.  His approach is much cleaner than mine.
0
 
sdstuberCommented:
>>> Won't your approach execute every query?


No, because there is only one query to execute, but that query has 3 conditions.

The downside to that is the indexing, unless you have an index that covers all 3 conditions will likely be suboptimal.
That is, you "might" get index usage for one condition but not the others.
0
 
käµfm³d 👽Author Commented:
OK, I understand what you are saying. I realize now that I oversimplified the question. But I'll see if I can make the case statement work within my environment. If not, I'll start a new question with a more accurate description.

You've answered the question that I asked, so...

Thanks!
0
 
sdstuberCommented:
I think I understood your requirement, but it's unfortunately not one that's easy to satisfy with good performance for all cases.

The single query with multiple conditions is "best" in terms of number of times you have to query the table.

However, it might not be good for performance if you have indexes that satisfy each of the 3 conditions and might possibly be able to very quickly read a row and validate.  Especially if the first condition would usually be hit.

If you did want to force a check (and you could do it efficiently) then I might try something like this...

Simply execute each query with a rownum=1 clause because you only need to check for existence, not actual values.  Then open your cursor


DECLARE
    v_check INTEGER;
    v_cur   SYS_REFCURSOR;
BEGIN
    SELECT COUNT(*)
      INTO v_check
      FROM yourtable
     WHERE firstcriteria = 'firstValue' AND ROWNUM = 1;

    --Return this if there are rows
    IF v_check = 1
    THEN
        OPEN v_cur FOR
            SELECT *
              FROM yourtable
             WHERE firstcriteria = 'firstValue';
    ELSE
        SELECT COUNT(*)
          INTO v_check
          FROM yourtable
         WHERE secondcriteria = 'secondValue' AND ROWNUM = 1;

        --Return this if there are rows
        IF v_check = 1
        THEN
            -- Else Return this if there are rows
            OPEN v_cur FOR
                SELECT *
                  FROM yourtable
                 WHERE secondcriteria = 'secondValue';
        ELSE
            SELECT COUNT(*)
              INTO v_check
              FROM yourtable
             WHERE thirdcriteria = 'thirdValue' AND ROWNUM = 1;

            --Return this if there are rows
            IF v_check = 1
            THEN
                -- Else Return this if there are rows
                OPEN v_cur FOR
                    SELECT *
                      FROM yourtable
                     WHERE thirdcriteria = 'thirdValue';
            END IF;
        END IF;
    END IF;
END;

Open in new window

0
 
käµfm³d 👽Author Commented:
Yeah, I started off with everything in one query, but there is one rule that relies on a substring (last 5 of SSN) of an indexed column (negating the index). The DBAs are OK with creating a new index if it is needed, but they want to do it in a manner that scales. Today we are working with last 5 of SSN; tomorrow we might need to go to 6. My goal was to whittle down the rows to a small enough number that the substring would be inconsequential. My rules aren't giving me enough columns to work with in order to achieve this smaller subset. This is why I thought i might run each query as needed. Then the hit might not be as bad.

The oversimplification I mentioned above is due to join tables, which unfortunately are not the same tables across all rules.

As it stands right now, my latest incarnation is running at a worst-case of about two minutes for a common search criteria (e.g. last name 'Smith'). Less common criteria seems to run a bit snappier. In the web world, though, 2 minutes is an eternity!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now