On Sep 13, 2017, at 4:48 PM, Stephen J. Orth wrote: > I've given up trying to write and use stored procedures, the Server is just > too fragile. I'd be open to anyone telling me you can really use stored > procedures in a "hard core" way without completely killing all connected > users performance. > > For years now, with each new release of 4D, I've been patiently waiting for > my opportunity to really utilize the power of the server hardware our Clients > purchase. However, each new version I continue to find it is not robust > enough to really use stored procedures.
I’m a user of Stored Procedures in all my 4D applications. But there are some things I avoid doing to reduce the impact on connected clients. Here are some things I think about when creating a Stored Procedure. Don’t use 4D Internet Commands plugin calls if possible. Why? Because when a plugin call is made the 4D cooperative scheduler can be blocked until the plugin command finishes. And that means any other code that needs to run on 4D Server — in other stored procedure or more importantly in triggers — can’t execute. Here is a real world example: You have a Stored Procedure that periodically checks an email account for new messages, downloads the messages and attachments and then does some processing. I’ve found that sometimes email servers are not too responsive. So commands like POP3_MsgInfo or POP3_GetMessage can take many seconds to complete. And during those seconds no other 4D code on the server can run. So a user tries to save a record, and it appears 4D Client hangs. That’s because there is some code in the trigger and it can’t execute until the plugin command returns control back to the 4D scheduler who can then switch out of the Stored Procedure method and give the trigger some time to execute. So users complained that periodically 4D hangs. You log the times of this happening and find that at the exact same time the email retrieval Stored Procedure has woken up and is doing its email work. The fix for me was to kill the email Stored Procedure and run a copy of 4D Client on the 4D Server machine and have it run the email retrieval method instead. The problem immediately went away. It’s an extra hassle. You you have to make sure the 4D Client is always running and relaunches after a machine restart. But it’s a doable fix. And it solved my problem. You can usually get away with sending the occasional email from a Stored Procedures. But you can run into the same problem if you send a lot of emails and/or have large attachments. So you have to careful. And if you don’t have any triggers, then users will not experience the hangs. Of course any other Stored Procedure on the server are still affected. This is the way 4D works with cooperative tasks. And plugin calls live in the 4D world of cooperative processes. There are plugin API commands to let the plugin yield back to the 4D Scheduler. But the plugin developer must use them, and sometimes that’s hard to do in the plugin in certain situations. In the past 4D indicated it would like to eliminate the 4D Internet Commands plugin and replace it with native commands. (Anybody else remember hearing that?) I think this is part of the reason why. It would easier to implement non-blocking versions of POP3_MsgInfo, POP3_GetMessage, POP3_Download, etc. They can better handle the situations and yield time to the 4D scheduler more often. Another thing to consider is being a processor hog in a Stored Procedure. Say you have a method that takes 1 minute to run as a Stored Procedure. It is doing queries, record saves, record deletes, typical database operations. But it does thousands of them at a time. It’s like having a burst of many users all doing many things at the same time. Users notice 4D Client gets “sluggish” at times. It’s running slow. Then it is back to normal. The fix is to not have the Stored Procedure run at “full speed” until it is done. Slow it down. Have it delay itself often to yield processing time to other users. Here’s another real world example: In some databases I implement a “soft delete” mechanism where a record is not immediately deleted. Instead a “DeletedRecordDate” field in the table is used to know if a record has been “marked” for deletion. You have a Stored Procedure that wakes up periodically and searching for any records with a DeletedRecordDate that is say over 30 days old. Then you physically delete those records with the DELETE RECORD command. The “Purge Deleted Records” Stored Procedure is written to not be a processor hog. Inside all for loops there is a DELAY PROCESS(Current Process;1) command. You can use more than 1 tick. Maybe 10 ticks is better. But the key is to force the Stored Procedure to yield to other processes. Instead of doing 1,000 QUERY and then 1,000 DELETE RECORD commands in a tight loop that takes 30 seconds to run, maybe it takes 2 minutes to run with the process delays. I also break up big selections into chunks of arbitrary size. So if I find a table that has 100,000 records to delete, I don’t do a DELETE SELECTION on 100,000 records. I split it into 100 chunks of 1,000 records with delays between each chunk. Say DELETE SELECTION of 100,000 records takes 500ms to run (maybe there is some delete trigger code that runs). Doing a DELETE SELECTION on 1,000 records only takes 5ms. Then you pause for a moment before doing more. The impact to users is less on a very active database because you are not hogging the processor. Some day I hope to rewrite my “Purge Deleted Records” Stored Procedure to run as a preemptive worker process. The idea here is that all this cooperating and delaying and “playing nice” will not be needed. I’ll let the OS do the scheduling and delaying for me. 4D Server's Stored Procedures is a great feature. It seems to be such a shame to just throw the whole thing out and say “don’t use Stored Procedures EVER because it will cause all kinds of problems”. I don’t believe that. It can cause problems in some situations. But in other situations it is fantastic and trouble free. Yes, I have read the recent posts about v16 having issues where some Stored Procedures seem to stop executing. That’s obviously a serious bug. 4D needs to immediately find and fix that. If they don’t, then all 4D developers will lose confidence in Stored Procedures and we will all stop using them. (Or nobody will upgrade to v16+.) But I don’t believe that will happen. Final thoughts… Stored Procedures are great. Same goes for triggers. They are great and use them. But like with drinking, drink responsibly. Use Stored Procedures and triggers responsibly. Could be Steve is right that “hard core” Stored Procedures is a bad idea. It’s like being a drunkard. Don’t be drunkard. :) I am confident that there will come a day when there is a 4D version that eliminates the need for the cooperative process model we live with now. Everything will be running in a preemptive thread. That includes Stored Procedure and Triggers. We are on that path. Is it v18 or v19 or v20? Only 4D Engineering could answer that. I’ll be sure at the next 4D Summit to track down Laurent and ask him directly about this. It should be something in the 4D roadmap we should know about it so that we can plan for it. (Wouldn’t it be nice if 4D actually had a public roadmap of what was coming in future versions? But that’s a whole other topic to debate.) It has to be done in a way that does not kill all of our existing systems. We have to migrate to that. Remember that one of things we love about 4D is its legacy support. New version comes out, still runs all the code and work with old version code. Yes, we have to tweak things and make adjustments here and there but its nothing “major”. It continues to work. What if 4D said they were releasing a version called “4D Enterprise” the eliminates all the problems talked about above? Totally thread safe everywhere. All preemptive processes. You got 12 cores, it uses them all. No blocking issues. You want 10 “hard core” Stored Procedures, no problem. It can handle it easy. But "4D Enterprise" has zero legacy support. No backward compatibility. Existing .4DB files cannot be converted or used. Can’t use any existing 4D plugins. Goodbye AreaList, 4D Write, hmCal, NTK, Win32API, etc. It's like Wakanda. You must start again from scratch. All new code. All new language. Great new product, but would you use it? Would you start over? I think we are going to get to where we all want to be. It’s just going to take a few more years, if you want my honest opinion. Wow… this turned out to be a lot longer post than I expected. But it might be helpful to less experienced 4D developers. Tim ******************************************** Tim Nevels Innovative Solutions 785-749-3444 [email protected] ******************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
