Anybody use the Execute SQL function in FM ever???

[angelleye]

Is it normal for FM to take a VERY LONG TIME to do relatively simple SQL selections? I'm trying to execute the following SQL:

SELECT Inventory.Ref_Number, Purchase_Order_Items.Condition, Inventory.Qty_Available, Ref_Number.Description, Ref_Number.Manu_Part_Number, Ref_Number.Street, Ref_Number.Weight, Ref_Number.Class, Ref_Number.Hot

FROM Inventory, Purchase_Order_Items, Ref_Number

WHERE Inventory.Qty_Available > 0 AND Ref_Number.Item_Number = Inventory.Ref_Number AND Inventory.PO_Item_Number = Purchase_Order_Items.Control_Number AND Ref_Number.Class = 'ATA Hard Disk Drives'

I've tried it both within FM itself via ScriptMaker Execute SQL command, and I've also run it from an ASP page. Both ways use ODBC, and both ways take literally 10 min or so to complete. The web page takes so long that the script times out before it can even display the records.

For all the hype I hear about FM it's pretty sad this is such a problem and I now see why it seems to be the absolute last choice for web developers.

I've run an UPDATE SQL command that updated all 52k records in our DB in roughly 30 seconds. I've been using ODBC to insert records that seemingly have more complictated SQL than this. They all worked fine.

Why would this be taking so long?? There are only 4800 records in the table that I'm trying to select from and only about 94 that would actually come back with this particular SQL.

Is FM really this lame, or is there something I can do to make this work like any other RDBMS out there?

Any information I can get would be greatly appreciated. Thanks!

Edited October 1, 2006 by Guest

[Søren Dyhr]

Regard this as a cul de sac, making a filemaker database a SQL source is filled with exceptions to stadard. This is far away from the core use of a filemaker database. Filemaker have carved out a niche, where ODBC just is put there for compatibily reasons.

Filemaker requests are of a very different nature to frankly, you have to take off you coat of armour to get the gist of the tool, or use migrate further to servoy. Filemaker uses other ways to make webpage interactions somewhere far from the heard of SQL tools.

--sd

[angelleye]

Ok, well I've been trying Custom Web Publishing with XML and so far it's a whole lot slower than ODBC.

I've got a file called Auctions that stores all of our eBay auction data. There's a field in there called Auction_Status that holds a value of Current, Winner, or Loser.

I've got 2 scripts setup to grab all current auction records and display their auction numbers to the screen. One uses ODBC and the other uses CWP w/ XML.

There are about 3500 records that get returned and the ODBC way does it in about 2 seconds while the CWP way takes a full minute or a little longer. Entirely too slow.

So ODBC works fine for this particular use, but for the one I originally posted about it's VERY, VERY slow and in fact I've come to find out that it causes the server to get stuck at 70% CPU usage and doesn't seem to ever stop. Even when I shut down the browser that was making the request.

So...what the hell do I do? Any tips would be greatly appreciated.

[Søren Dyhr]

Ask the same question in the Servoy forum, they're migraters from filemaker or still doing both. But there might be something else in the structure in the filemaker base that recieves the data such as invoking layout issues that easily can wait untill later. But also would I digest the info recieved in CURL and the streameditor like SED, in order to let as few as posible native calc'fields deal with it. Are a lot of autoenters employed during this grapping??

--sd