2

I have a table with large amount of data and want to take too much inquiry for checking the existence of a specific row. If that row exist then increase value of inquiry_count column. We have tow different approach to do this please tell me which one is better.

first : we can select that row and check it with "Exists" function, If that row exists then Execute update command to increase inquiry_count column.

Second: we can only execute update command. if the number of affected rows are more than 0 then that row exists.

Community
  • 1
  • 1
Mohammad
  • 97
  • 1
  • 9
  • 2
    In most cases I would expect `EXISTS` to be better, but Eugene is right in that you'd need to test it. Frankly the most important thing here is: "is there a suitable index to use for the check". And updating *data* as part of a *query* sounds.... odd. – Marc Gravell Mar 05 '12 at 06:51
  • 1
    I agree - code them both and test them yourself. Remember to give consideration to how frequently the two possibilities occur *(no record found, and records-found then updated)*. – MatBailie Mar 05 '12 at 07:02
  • Bear in mind that check/update would be subject to changes in the underlying data, unless you apply some serious locking within a transaction. You might want to avoid that. – Damien_The_Unbeliever Mar 05 '12 at 07:45
  • It would probably help to see your schema, along with the queries that you use in both scenarios. There may be room for further optimization there. – Eugene Mar 05 '12 at 15:58

3 Answers3

3

Write both queries, check both with SQL Profiler which one works better in your scenario.

Eugene
  • 2,965
  • 2
  • 34
  • 39
  • regularly second scenario must be better, Because update command should find that row, if that row exist then apply update. – Mohammad Mar 05 '12 at 06:59
1

Seems related to that post if you are concerned by performances issues: Solutions for INSERT OR UPDATE on SQL Server

Community
  • 1
  • 1
David Brabant
  • 41,623
  • 16
  • 83
  • 111
0

I think both queries do not really fit.

  1. EXISTS is only used for subqueries, where it is very performand. - But not used for standalone checks.
  2. UPDATE should really only be used to update data, not for existence checks.

I would suggest to query with a TOP Query and use an appropriate index.

e.g. 

SELECT TOP 1 {ID} FROM {TABLE} WHERE {YOUR CHECK HERE}
BitKFu
  • 3,649
  • 3
  • 28
  • 43
  • 1
    this query is better than "Exists". – Mohammad Mar 05 '12 at 07:11
  • 1) In my opinion, this is simply untrue. `IF EXISTS()` is a very standard construct. 2) If the 'normal' situation is that the update will be required, optimisitcally running the update and then checking for success/failure is also a normal construct. `IF (check) UPDATE` is more fit for cases where it is more normal for the record to *not* be found. Overall, I strongly disagree with this answer. – MatBailie Mar 05 '12 at 16:23