Totally agree with all of that. I was hopeful that since Wakanda uses the same engine that we'd see a lot of improvement in the SQL engine of 4D, but not so much. Another example: a SELECT on an indexed field using a nested SELECT works great until you reach a certain number of rows returned. Once you go past this magic number, 4D SQL forgets there's an index and drops you into the land of full table scans. So frustrating.
We're probably an exception, but we use the SQL/ODBC engine a lot here. The UPS and FedEx computers in the warehouse use ODBC connections to update shipping records in 4D, several FileMaker systems also use ODBC to receive up-to-date information from 4D and create pretty signs, and we make extensive use of the 4D to PHP DataObjects library (http://www.php.net/manual/en/ref.pdo-4d.php) in a lot of custom projects for monitoring, dashboards, and reporting. The 4D SQL engine works great until it doesn't, and when it doesn't there's not much you can do other than push the data to MySQL or Postgres. And did I mention object fields? :) Anyway, there's a lot of potential in the 4D SQL engine, and I hope that 4D hasn't abandoned it or decided to treat it as a marketing checklist feature. Jeff On Apr 14, 2017, at 10:29 AM, Bart Davis via 4D_Tech <4d_tech@lists.4d.com> wrote: > I couldn't agree more with this statement "It's almost as if the SQL engine > is a bolted-on afterthought to 4D that doesn't receive much love at all from > the development team." > > - The fact that you can't use SQL in a trigger when a record is inserted, > updated or deleted via SQL is baffling. Every other SQL database seems to be > able to do it. > > - You still can't use local variables in a EXECUTE IMMEDIATE SQL statement > in a compiled application? Is it really that difficult to implement? > > - Could there be some internal semaphore/blocking code that prevents some > part of a SQL query in one process from executing (this includes ORDER BY) > when a SQL query in executing in a different process? In two different web > applications that have heavy concurrent use, progress bar windows start > appearing on the screen saying ORDER BY or LOADING DATA and as more processes > are initiated more windows appear the application grinds to a stop and must > be killed and restarted. These are simple queries with Order By on indexed > fields on small data sets. I replaced the SQL queries with the standard 4D > Query language and there are no more performance issues. On all heavily used > queries I have replaced SQL with 4D query language. > > - Why isn't the use of 4D array variables in SQL queries supported? > > Assuming $aRecordID is a populated Longint Array... > > Begin SQL > Select * from Table where ID in :$aRecordID > End SQL ********************************************************************** 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:4d_tech-unsubscr...@lists.4d.com **********************************************************************
