Are date ranges in views ok?
I recently started working for a company with a huge "entrepreneurial" application. In my last job, I developed a database, but here we have a whole database architecture department that I am not.
One of the weird things about their database is that they have a bunch of views that, instead of giving users the date ranges they want to see, join the global global table "TMP_PARM_RANG" with a start and end date. Every time the main application starts processing a request, the first thing it does is " DELETE FROM TMP_PARM_RANG
;" then paste into it.
It seems like a fancy way of doing things, and not very safe, but everyone else is fine here. Is this normal or is it really my concern?
Refresh . I should mention that they use transactions and locks on a per client basis, so it is immune to most concurrency issues. Plus, there are literally dozens if not hundreds of views that depend on TMP_PARM_RANG
.
source to share
Am I getting it right?
There is such an idea:
SELECT * FROM some_table, tmp_parm_rang
WHERE some_table.date_column BETWEEN tmp_parm_rang.start_date AND tmp_parm_rang.end_date;
Then, in some interfaces, the user enters a date range and the application does the following:
- Removes all existing rows from TMP_PARM_RANG
- Inserts a new row into TMP_PARM_RANG with custom values
- Selects all rows from view
I wonder if the TMP_PARM_RANG changes have been fixed or reversed, and if so, when? Is it a temporary table or a regular table? Basically, depending on the answers to these questions, the process might not be safe to run by multiple users at the same time. It is hoped that if this were the case, they would have already discovered it and turned to him, but who knows?
Even if done in a thread-safe way, it doesn't make a lot of sense to make changes to the database for simple query operations. These DELETE and INSERTs generate redo / undo (or whatever is equivalent in a non-Oracle database) which is completely unnecessary.
A simpler and more common way to achieve the same goal would be to execute this query, bind custom inputs to query parameters:
SELECT * FROM some_table WHERE some_table.date_column BETWEEN ? AND ?;
source to share
There must be some business reason for this table. I've seen views with hard-coded dates that were actually partially considered and they used dates as the partioning field. I've also seen how to join a table, for example when it comes to daytime savings, imagine a view that returned all the activity that happened during the DST. And none of these things will ever delete or insert into the table ... it's just weird
So either there is a deeper reason for this to be unearthed, or it is what seemed like a good idea at the time, but why it was done in such a way that it was lost as tribal knowledge.
source to share
Personally, I imagine this will be a rather strange phenomenon. And from what you are saying, two methods calling a process at the same time can be very interesting.
Typically, date ranges are executed as filters in a view and are not driven by external values stored in other tables.
The only excuse I could see for this is a multi-step process that only ran once at a time, and dates are required for multiple operations across multiple stored procedures.
source to share
I guess this will allow them to support multiple ranges. For example, they can return all dates between 01/01/2008 and 1/1/2009 AND 1/1/2006 and 1/1/2007 to compare 2006 data with 2008 data. You couldn't do it with one pair of related parameters. Also, I don't know how Oracle is doing the query to cache plans for views, but maybe it has something to do with it? When date columns are validated as part of a view, the server can cache a plan, which always assumes that dates will be validated.
Just throw some guesswork here :)
Also, you wrote:
I must mention that they use transactions and locks per client, so it is immune to most concurrency problems.
While this may warn against data consistency issues due to concurrency, it hurts when it comes to performance issues due to concurrency.
source to share
The views are probably being used as temporary tables. In SQL Server, we can use a table variable or temporary table (# / ##) for this purpose. While creating views is not recommended by the experts, I've created a lot of them for my SSRS projects because the tables I'm working on don't reference each other (NO FK, seriously!). I have to fix flaws in the database design; why I often use views.
source to share
When using the global temporary table GTT you are commenting, this method is certainly multiuser safe, so no problem. If it is Oracle, then I want to verify that the system is using the appropriate dynamic fetch level so that the GTT is attached appropriately, or that the call to DBMS_STATS is made to provide statistics to the GTT.
source to share