She did mark the thread with the database tag, though.
If the data is being used only by the person doing the sorting and not by other users in others workstations, in my opinion it needs to be persisted in either a database or file. If the data is not persisted anywhere and is only used this particular client then is a dynamic query.
One question to ask is how the this user is creating the dataset, is it created manually and embedded?
Doing this cancels out Query Caching, and increases the number of trips to the DB for essentially the exact same data. This is not good for performance. No, until your dataset gets substantially large, the sort should be done on the front end, preferably as an expression binding, but a script transform will work if you need to sort against multiple columns and you are not be able for some reason to use Phil's free module.
If the dataset is being created manually, then the point is moot as there is no other option.
Raw data should almost always be kept in its raw state outside of a consumers "data" prop, and the binding to it should perform the sort.
3 Likes
If the dataset is being created manually, then the point is moot as there is no other option.
kind of agree but there are other things that need to be taken into consideration not just how to sort the data and that may affect the performance. For example: How many other clients will be connected at a given time, is the same data needs to be visible to the other users, are other users going to sort their data individually, how large can the data set get and critical is latency created by running the script.
Raw data should almost always be kept in its raw state outside of a consumers "data" prop, and the binding to it should perform the sort. Don't understand this.
What other things need to be considered? The dataset is held solely in memory (tag or otherwise) so there is no option to do the sort at the DB. The DB has nothing to do with it at that point. Should the data be stored in the database is a different discussion and is outside the scope of this thread.
Just so we're clear, the most performant way to sort a dataset by a single column is an Expression using sortDataset() the next best option or if you need to sort by more columns is a script unless you use Phil's module and can use the OrderBy() expression.
This is exactly the point. If you have multiple users viewing the same data, then you want to make all attempts to reduce the number of trips to DB as possible. Network latency is a factor that can be removed if you optimize the number of trips made. Say, you have 20 users, and each user wants to sort the data (can even be the same sort, doesn't matter), that now means you have 20 requests hitting the database. On top of other potentially critical requests for say storing the data. Now, you have a critical system fighting with the UI for rights to the table, which slows it down.
If you always do the sorting in the DB, then you always have to pay that penalty, it is most often not worth it. If you're returning very large datasets (say 10,000 rows plus) then perhaps the cost of the trip to DB is outweighed by the cost to do the sort locally. Then I would be arguing why you need to display 10,000 records to a user (the exception being potentially trending).
If you keep the data in it's raw state (as returned from the DB without any filtering or sorting applied). Then it is very easy to cache that query. Caching requires that the query sent to the DB is exactly the same. That means the same parameters and the same structure. Dynamic SQL defeats this, don't get me wrong it has it's place and is a very useful tool, after all retrieving and sorting data is what DB's do best, but reducing the load on the DB only helps to make it more performant when you need it to be.
On top of that, keeping the data in a separate place cuts down on the complications caused by editing that data. Browse the forum and see how many posts you can come up with where the OP was trying to edit/sort/filter data in a table but it wasn't being saved because it was being overwritten by the binding.
Keep the data separate, then if the user wants to change the filter, just reference the property. If the user wants to change the sorting, just reference the property. They can do it to their hearts content, and it will never re-query the DB. An expression binding will always be faster than a query, a script transform will almost always be faster than a query (assuming all it is doing is the sort).
Much like dates and numbers shouldn't be converted to a string until being displayed, data should never be sorted or filtered until it needs to be. When it is consumed (by user or script).
2 Likes
Thanks