Sequelink ODBC 5.5 - Slow Query

[uemtux]

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?

[IdealData]

Is REFERENCE1 indexed?

[uemtux]

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, 2010 by Guest

[IdealData]

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?

[mr_vodka]

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.

[uemtux]

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, 2010 by Guest

[uemtux]

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.

[Wim Decorte]

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

[uemtux]

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.