0

I am developing a program, with a database and a GUI which displays parts of the database.

Let's suppose I have a textblock which contains data from the database (found with a query). If I want to use the value of this textblock somewhere else in the program, should I better read it from the textblock, or call it a second time from the database?

In other words is it OK, in term of performance and maintainability, to use GUI displayed results of SQL query as an input from other functions, instead of doing new queries?

Sithered
  • 481
  • 7
  • 23
  • 1
    If that values can _expire_ (because they're valid in a specific moment, like weather forecasts or real-time inputs, for example) then you _may_ requery (but it may not be what user expects because he'll run an operation with inputs different from what he is seeing then action may be wrong with new inputs). If they may be overwritten by another user then you may perform a new query (and warn him if they changed). In short: it depends on what your data are, there is not a general rule for this. – Adriano Repetti Jun 09 '15 at 07:38

1 Answers1

2

There are a few things to consider here:

  • Stale data/concurrency. If multiple people/processes work on the same database objects, keeping the same data in the GUI for a long time might lead to stale data, where the data shown is old and out of sync with the actual data in the database. This might lead to errors when the data is updated.

  • Coupling. Sharing state between multiple GUI components could lead to a maintenance nightmare, since you can't change one part of the GUI without it having an effect on another part of the GUI.

  • Performance. Networks are fast. Optimized queries run towards an optimized database are fast. The performance gain from reusing query data thus shouldn't be too high. And probably not enough to justify not re-fetching the data from the database, ref the other bullet points.

Tobb
  • 11,850
  • 6
  • 52
  • 77
  • I agree but refetching is a _dangerous_ operation (hard to say without more context). Think about a power plant management software. Temperature is too high and automatic procedure can't follow quickly enough. You take a corrective action (increase fluid flow by 5%) but you were actually watching 10 minutes old data (now temperature is under control and flow has been already increased by 20%). It's a somehow extreme example but if user has _thinks_ to give an input (fetched from DB) when in reality he's giving another input.. – Adriano Repetti Jun 09 '15 at 11:39
  • My advice is: I don't have any advice because I don't know his specific scenario. – Adriano Repetti Jun 09 '15 at 11:39
  • In your example, _not_ refetching is what is dangerous, right? Typo at the start? – Tobb Jun 09 '15 at 12:33
  • No, I mean **DO refetching** (and using new values) **may be dangerous**. If you're booking a seat for theater then an optimistic lock is probably the best but user will KNOW his data was old. Imagine a typical LOB application to approve/reject orders: operator sees 1 item and decide to postpone that order (because product isn't available until next month). Unfortunately someone else added a new order in that minute and it'll finally reject TWO orders (unaware there is a second one). – Adriano Repetti Jun 09 '15 at 13:00
  • IMO refetch & go is applicable, sometimes, but in general user should be aware of what he is doing (refetch & warn if something changed). With real-time data this may be problematic (because values will constantly change) but it's something to determine case by case (in my previous power plant example if I push button "Increase fluid flow by 5%" then I expect it'll be increased by 5% of value I saw (not actual value). In this case OP may refetch and do nothing if value already increased by 5% or more (and then warn user) but for sure do not take blind decisions instead of user – Adriano Repetti Jun 09 '15 at 13:03
  • Another real-life example: you click "Delete this conversation" in Facebook but someone else sent you a message and window didn't refresh so far...I expect I'll see that message at next refresh. For sure it's not in the set of stuff I said I want to delete. – Adriano Repetti Jun 09 '15 at 13:09
  • Not refetching can in some cases be dangerous as well. If you fetch a large object graph, then change some text field, and wait 10 minutes before storing the entire object graph again, then you might have overwritten other changes done to the object graph. Saying "refetching is dangerous" in general is IMO just wrong, it might be dangerous to refetch, but it also might be dangerous not to refetch. Depending on the context of course, one would need other security mechanisms to handle the input (like optimistic locking, etc) – Tobb Jun 09 '15 at 13:22
  • The real problem is in my opinion operations on entities outside of a transaction. If one wants to do operation A on an entity, pulling that entity out of a transaction, changing it and then merging it back could cause trouble, refetch or not. If one instead collects the necessary data to do operation A, and run the operation inside a transaction (with the necessary, context-specific validation that the operation is in fact allowed), most problems regarding this would dissappear. – Tobb Jun 09 '15 at 13:24
  • To use your examples, instead of sending the whole "TemperatureCorrectionUnit"-entity with the increased liquid flow to be stored, one sends the id of the unit, the old value and the new value. Then, in the transaction, one would get fresh data, check if the old value is still what it was, and if so, update the value. The facebook example would instead of updating the entire "Conversation"-entity, use a conversation-id, and a last-message-id to find the conversation, check if the actual last message is the same as the last message seen by the user, and take appropriate action based on this. – Tobb Jun 09 '15 at 13:27
  • Yes, exactly. I don't say "refetching" is bad (actually you must refetch to be sure nothing changed), what I mean is dangerous is blind use of refetched data (without user is aware of this). – Adriano Repetti Jun 09 '15 at 13:33