Hi all,

I have FileMaker Server 9 running (under Max OS X Server 10.5.5), sharing a database via FM and ODBC.

On a Windows PC, I have the Sequelink ODBC 5.5 driver installed, and my system DSN is configured and works fine.

The driver seems to take a really long time to run almost any query.

This query:

SELECT * FROM Shipment WHERE REFERENCE1='18281'

returns only 1 record, but my hard drive's grinding away and my client application is hung for a good 30-45 seconds before the data appears in it...

Is there any way I can structure my query to improve the speed...? Where is the performance bottleneck?

Is REFERENCE1 indexed?

Yes.

Just to note... I replaced the * wildcard with a list of all the fields I want returned and that reduced the query time to about 6-12 seconds.

Even the 6-12 second query is too slow though. I need it down to 1 second...

Edited March 5, 201015 yr by Guest

How many fields was that? and how many would it be for "*". Are there any BLOBs in there too?

I wonder if FMS10 would improve matters?

IMO that odbc driver is slow in general. Also I stopped using it because I am more than certain that it kept crashing my server when I performed imports of the FileMaker data into MySQL via the ODBC driver. I cant undoubtedly prove at which exact point it makes it happen but as soon as I turned ODBC off the crashes stopped.

I'm asking for 47 fields now, the * wildcard would have given me 138 fields

There are no blobs... all the fields are text and number fields... the ODBC manual mentioned that text fields have a character limit of some 1,000,000 characters unless a shorter length is specified so I tried setting all the fields to a maximum character length of 100... don't think ODBC cares, it didn't affect the performance. Is there some way to specify a certain data size to retrieve in the query..?

I will install FMS10 if I have to, but I'm hoping to solve the problem without doing that, since to upgrade the server I'd have to come into the office on a weekend... :)

Edited March 5, 201015 yr by Guest

Okay... let's assume that Sequelink ODBC is a big non-functional joke that Filemaker has played on developers.

Might there be an ODBC driver that speaks one of FileMaker's other protocols? I don't really know anything about XSLT, and nothing about XML beyond the structure of RSS feeds...

There must be some kind of workaround.

Same here. When we stopped hitting the server with JDBC queries the crashing stopped...

I just thought that I'd add to this thread that my problem was eventually solved by the release of FileMaker 11. I upgraded our server to FMSA11 and installed the new ODBC driver on the Windows machine and things are now lightning fast, and no more memory hogging!

I was really disappointed that neither FileMaker or Sequelink would support the old driver. Guess they both knew it was trash but no one wanted to admit it.