1

What would be better approach or rather in what situation should I select which approach, in below explained situation

    **Table - Wishlist**
    Id PK
    Owner FK
    Recipient FK
    Title
    Description
    Url

Search Rules Return wishlist item with specific 'Id', if Logged in user's username matches Owner or Recipient.

Approach 1 - 
Result = Select * From Wishlist where id == %id% && ( Owner == username || Receipent == username )

This will return only one record if the user is allowed to access it.

Approach 2 - 
Result = Select * From Wishlist where id == %id%
if(Result.owner == username || Result.Receipent == username){
  return Result
}

This will return result irrespective of whether the user is an owner or not. However, we're looping over single column as compared to multiple columns.

Which approach should be preferred and under which condition?

PS - SQL syntax is not accurate, and is only meant as an example.

2

Since no specific database is given, the below advice is general:

Most database engines try to limit the number of rows they have to look at as quickly as possible. Since both of your queries involve the primary key of you table, that will be used as the first basis for limiting the rows.

Obviously, a small amount of additional work is involved in the further limiting required in your first query, to only include the row in the result set if the Owner or Recipient match a specific value. If the check in your IF statement is done within the database (before, say, returning data to a calling application), then the performance is probably similar.

If the IF check is being done in an application, then you're sending data that may not be needed to the application. If it's as easy to check the data in the database as it is in the application, then I would generally filter on the database side, to minimize the amount of unnecessary data that needs to be sent to the application.

For one row, the difference is probably negligible. However, if you were trying to locate all rows in Wishlist that have the current user as Owner or Recipient and were added in the past 3 weeks, the difference would probably be very big.

Also: In most databases, applying an IF check like that outside the query requires that the data be stored in a staging table of some sort (ideally a temporary table) so a secondary query to filter using the additional criteria can be run, or by using a cursor to check the data row by row.

There are cases where using an intermediate table of some sort can greatly benefit a query. The DB engine may need much longer to figure out the best execution plan for very complex query, and may in fact have to go with a "best so far" plan sue to time constraints on the planning process. This can result in a sub-optimal query plan. Using a two-step process, where each query involved is simpler, can allow the best plan to be located more easily, and thus provide results faster than a single query. Note, however, that this will generally involve writing the intermediate results to disk, and writing to disk is one of the most costly operations most DBMSes perform.

Using some form of cursor, however, can be even worse. All SQL-based DBMSes I know of run best when treating rows as a "bag" of data, and processing them in bulk. In almost every case I can imagine, using a query that can apply a condition to all possible rows at once will have better results than checking the rows individually in a loop.

There are cases where that's appropriate. DBMSes so some thing better than others. Complex string processing is generally considered to be something most databases systems do relatively poorly. If you need to match on a complicated regular expression, and your DBMS doesn't have any native tools to handle regular expressions (or any easily-used add-ins that operate at native speeds), then sending the possible matches to your application and filtering based on the complex regular expression may be much faster (and easier) than cobbling together a series of comparable checks in a WHERE clause (see above notes on very complicated queries).

Another answer notes that MS SQL Server(and possibly other DBMSes) have some sort of row-level security. In the MS SQL Server case, note that this capability restricts access based on the SQL Server logins and users accessing the server. If you are looking at direct querying of the database by users, or if every user in your system has their own unique database-level login, then this could be of use to you. If, however, your application has its own internal users table, and needs to use that to control access (as would be true of most customer-facing applications, like the amazon.com website), then you probably need to manage that yourself.

0

Because ID column is Primary Key, quering with ID predicate should be very efficient (seek in B-Tree index).

Therefore, in this case first approach is more readable and transparent. Performance should be the same in both cases.

It depends which RDBMS are you using, but you can use feature like Row-Level Security. This feature implements exactly that kind of behavior which you desribed but it is SQL Server's tool. Probably other RDBMS have their own solutions for this scenario.

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.