I have a process that does a lot of read and write actions to the database which is time consuming. It is a loop over a table with a few joins to other tables. So it is working with Clarion side views which is already an improvement in comparison with loops over files.
However I am considering using a SQL side view to improve speed. That SQL view would have the same structure as the Clarion side version.
Would using a SQL side view make a difference in comparison with a Clarion side view? Or isnât it worth the effort trying?
âAsk 5 SQL people a question, and youâll get 10 opinionsâ.
The truth is, you cannot get a ârightâ answer to your question because without a lot more context, there isnt really a right answer.
The short answer is pretty much ânoâ because server-side views are (mostly) not persistent. In orher words whether the SELECT comes from the client, or from a stored SELECT on the server, itâs still just a SELECT generating a result set.
Which is not to say that there arenât other ways to improve your code (depending on your context.) For questions like this though i recommend the webinar. That helps because we can explore your context to determine what (if any) strategies might be beneficial.
Unfortunately with SQL there are very few âabsolutesâ. Good advice for one context can be bad in another. Which is partly why there are so many ârightâ ways to do something and why most advice in forums like this is useless.
Iâm certainly no SQL expert, but it seems like I hear a lot about moving processes to stored procedures instead of looping through a view on the Clarion side, where possible.
Which SQL? In PostgreSQL, I would make this a function that perhaps returns the number of writes. A big advantage - if not completed, no changes are saved.
If your process doesnât do any display stuff, I have found that almost every process can be turned into an SQL procedure/function and mostly runs many times faster. This is particularly so if you can take advantage of the SQL set processing power. Counter intuitively, That may mean applying an update statement a number of times instead of a loop, and it can still be way faster.
Note the Almost, Like Bruce says context in your particular situation matters.
Youâll see this advice a lot, and for good reason. But even this approach of moving code into stored procedures is context dependent.
Thereâs a growing movement in SQL circles to keep code out of the database. The justifications are as follows;
Firstly (and i guess this doesnt apply to most of us) as the system scales up (say with multiple web servers) the database is the hardest part to scale. So the (single) database machine becomes the bottleneck. In this case treating the server as a data store, and not a processing engine is good.
Secondly (and more applicable to us), moving code into the database means your program now âlivesâ in 2 places, and is written in 2 languages. This makes maintainence harder, backups more complicated, training new employees trickier and so on. If the server code requires a client update, or vice versa, that can be hard to enforce. And rolling code back becomes difficult etc
Thirdly, obviously, portability drops right off as well. That might not be a big deal now, but it can become important down the road.
So moving code into Stored Procedures may improve performance, but it certainly comes at a large long-term cost.
Again, context matters. In some cases the above issues are moot. In others they are really important. And, i want to stress, the advice is not wrong - it likely will improve performance. But performance is not the only thing that matters.
If the db backend is a SQL server of sorts, thereâs not much in clarion side views and server side views, however this relies on the SQL server caching the requests to improve speed so the first will be âslowâ, subsequent will be quicker.
Building a SQL view in the SQL db is also an option, this will improve the speed a bit more, and it can be imported back into the clarion dct like a normal table.
Stored Procedures are the next step up from SQL views. The advantage of these is parameters can be passed to the Stored Procedure to limit its range, to use a Clarion term, and it can be used to do additional processing much like a Clarion Process does. Parameters passed can be used to determine the action.
As for OP question, yes its worth the effort, but then you have to put the logic into the SQL db.
Well, yes. scale matters. Even with scale something that client side takes 2 mins vs server side 2 secs (which I have seen) is likely worth the clustering required.
If I ever make it to that point Iâll be a happy chap.
Well yes. This is what âClient-Serverâ processing is all about. It splits the processing and thus is more efficient, particularly when high IO loads are required, which is typically when you would employ the split processing.
Granted this requires a knowledge of SQL, but if youâre employing that anyway with server side views (which can be very complex), etc anyway, why not go all the way.
Your argument implies under utilization to avoid multiple knowledge. Iâm uncertain of that reasoning.
Iâm suggesting that all magic comes at a price. Its important to understand that price, and be willing to pay it.
Syncronizing code in two places has significant complexibilty, especially when rolling versions forward or backward.
Writing code across multiple languages has implications for long term maintainabilty, and succession.
These are not disqualifying arguments. In some contexts they are less important. They are more qualifying arguments - understanding the full scope of choosing one route or another.
I agree with Bruceâs answer to the question âWill defining a server-side view help?â as No. A view is just a stored query, and if you take the select statement generated by your clarion view and set that up as a view on the server, you have really changed nothing.
I think if you are doing set-based things, like processing a batch of stuff, the more you can do on the server the better. Clarion does stuff row-by-row which is inherently slow. Plus you have the overhead of sending stuff from the client and the server, which just may not be necessary. So Iâm with Sean and other people on that.
Portability I wouldnât worry too much about. So long as your procedures are mostly SQL statements, maybe throw in a few local variables or something , and if you ever need to switch servers, the translation of your stored procedures should be trivial. However, add in some proprietary procedural language data structures (like arrays), and your moving cost becomes less trivial.
So, view - no; stored procedure to deal with a batch of stuff on the server rather than sending data back and forth to the client â absolutely.
True and there wont be much difference in terms of performance on an ad-hoc SQL query from the clarion app compared to a view created in the sql server.
Depends on the complexity of the view. If itâs view upon view upon view itâs probably inefficient. If itâs a single view thatâs not particuarly complex, thereâs probably little to no performance impact.
Ergo performance can be impacted if its a complex view.
This may also help the OP if they are using MS SQL server.
The process I have does a lot of reading of several tables and then based on the read values adds records to other tables. The writing part is surround with a logout and commit and written in native Clarion (no ABC). The database is MS SQL.
I understand that using stored procedures might also improve things but that is not the way I would like to go. For reasons already mentioned like maintainability but also I am not very familiar with stored procedures. The process I am working on is more than a decade old and quite complex so I donât want to make it more complex by moving parts of it to the database engine (besides SQL views).
My original question is answered: Moving to a SQL side view is probably not worth the effort. So that is not the way to go.
But reading all the responses made me realize that I wrote once a SQL statement as large string that inserts multiple records with one PROP:SQL statement. That is much faster than adding records one by one whatever it is surrounded with logout and commit or not. Maybe I can use this technique also in this process.
To end with and a bit ashamed to say: Today I found that the index on the sort order and the used filter of the view is missing. This is probably overlooked for years, sighâŚ
That might be a starter.
If youâre primarily reading from the view, you might find that creating a filtered view and/or indexed view on the SQL server might be efficient. (OK for updates also, but particularly good for fast reads).
I hope you donât have any string values that have dubious characters in them. If you are creating a huge PROP:SQL statement to insert or update records and you have some bad character (like a binary 0) that causes the server to believe it has reached the end of the statement, those are very difficult to figure out.
Also, as a counter-balance to Bruce on maintainabilityâŚ
Where I work they are just starting to replace a system that was built starting in 2003 using Oracle as the back end and VB6 as the front end. VB6 reached end of life very soon after the system came into operation. There are not going to be a lot of changes to the back end â the data tables will stay pretty much as they are, and the procedures, IMO, need refactoring, but could come straight over. Where a bunch of difficult work will need to be done is where procedural stuff has been done in the front end, and Iâm pretty sure any of that stuff they have to deal with will be turned into stored procedures, and as they do it, they will curse the people who decided to put that stuff in the front end in the first place.
Since you are on MSSQL and you canât use a SP, then I would suggest create the query in SQL, with all the necessary JOINs, and then use the tools in the IDE to profile the query and make suggestions for improvements. You may get suggestions of new indexes that can VERY drastically improve performance on the SQL side. I have seen processes that took several minutes go to sub-second timeframe by simply adding an index. Definitely worth the few minutes it will take!
Good point. Most fields are filled with a long. But string values I can clean up before writing them. No doubt that StringTheory has a method for that.
Yes, absolutely. This is why context in SQL advice is so important. Because sometimes it makes sense to put the business logic into stored procedures, and sometimes it makes sense to put it in the client.
And it can be very hard to predict which road will end up being the right one 20 years later. Which is why blindly following âdo it this wayâ advice us dangerous. Everyone needs to do their own research and understand enough to make an informed decision for their context.
