Slow SQL Queries

[Allyn01]

I administer a custom EMR developed in filemaker 8/9 for a small office. This office uses full versions of filemaker on virtually all the machines and i recently found out server advanced was required to support an odbc connection to our hosted files. To work arround this i wrote a local file and had it run on a single machine to allow an ODBC connection between us and a lab to easily allow exchange of demographics for billing purposes.

This odbc bridge for about a month (may-june) and then suddenly stopped. After some investigation I realized the driver was still getting the response it needed, but after about 60 seconds, long after timeout of the request. It was previously taking about 3 seconds. 5 identical queries are run (I had to write a solution so i wrote every piece of data they wanted into a single table, in plain text fields that are imported before any record is retrieved by the odbc connection) and each one takes arround 10-12 seconds when run under a simple VB command. The query i am running that is taking this time is just select * from 'table', adding a where to limit which records are selected doesn't add any additional time or shorten it (especially with one record). The ODBC connection is working, but this is far too slow for any practical application, and originally it was much faster.

Nothing was updated on the local machine when the slowdown occured and since then i've updated to DataDirect 32-bit sequelink 5.5 and filemaker 9, but it retains these slow queries.

Since i thought filesize or previous records were possible issues i emptyed the table and tried with one record, same speed, then i used the resore command on the file, same speed. Also attempted to index/unindex all fields, with the same result. I read some knowledgebase articles suggestion using a string on the odbc connection of LimitCursorColumnSize and setting a value of 50 (anything below 20 created error messages, which I suspect was from the length of field names), and even setting it to twenty didn't speed this up. I checked and the port 2399 is only used by filemaker and at this point i'm unsure what to try next.

I would appreciate any suggestions, thank you.

[mr_vodka]

I administer a custom EMR developed in filemaker 8/9 for a small office. This office uses full versions of filemaker on virtually all the machines and i recently found out server advanced was required to support an odbc connection to our hosted files.

What do you mean by this? What version of FileMaker Server are you using?

[Allyn01]

By this I meant that the database software is run on all of the machines, and it is filemaker 9. The server is is filemaker server, figured it was displayed by my name. For the purposes of this problem I'm only using filemaker 9 and Directdata seque 5.5 for the ODBC driver. As i said, it works, its just become very slow, and I can't explain why.

[mr_vodka]

Hi Allyn

This still does not answer my question. What version of FileMaker Server are you running? Your profile does not indicate it but I am going to guess that you are using FMS9 and that you are trying to get data out of FileMaker INTO another application via ODBC correct?

[Allyn01]

Correct, but i am not using any serverside files to do the ODBC as this required filemaker server advanced (at least it did when i set it up).