Response Time to Finds

[fmsavey]

I have been experimenting with FM9'S ESS with mixed results. Over all I am please with the response time when perfoming finds. There are times, however, when the response time to a simple query takes an unacceptable amount of time. Since I am unable to see the query generated by filemaker I asked our DBA to intercept the query on the backend. The find I was performing was simple. If I were to write the query it would have looked like...

SELECT mttk, mtmatter, mtper, mttkst, mthrwkdw, mtdowkdw, mtdocodc, mthrwkdb, mthrbidb, mtdobidb, mthrcodc FROM son_db.dbo.mattimhs WHERE mtper = '1206'

What filemaker did was to generate (iterations) dozens of queries like this...

SELECT mttk,mtmatter,mtper,mttkst,mthrwkdw,mtdowkdw,mtdocodc,mthrwkdb,mthrbidb,mtdobidb,mthrcodc FROM son_db.dbo.mattimhs WHERE (mttk=N'06391' AND mtmatter=N'60037d1045' AND mtper=N'1206')OR(mttk=N'01758' AND mtmatter=N'60037d1048' AND mtper=N'1206')OR(mttk=N'06920' AND mtmatter=N'60037d1048' AND mtper=N'1206')OR(mttk=N'06920' AND mtmatter=N'60037e1048' AND mtper=N'1206')OR(mttk=N'09992' AND mtmatter=N'60039.1004' AND mtper=N'1206')OR(mttk=N'00250' AND mtmatter=N'60040.1002' AND mtper=N'1206')OR(mttk=N'00280' AND mtmatter=N'60040.1002' AND mtper=N'1206')OR(mttk=N'01577' AND mtmatter=N'60040.1002' AND mtper=N'1206')OR(mttk=N'06800' AND mtmatter=N'60040.1002' AND mtper=N'1206')OR(mttk=N'06806' AND mtmatter=N'60040.1002' AND mtper=N'1206')OR(mttk=N'00130' AND mtmatter=N'60040.1003' AND mtper=N'1206')OR(mttk=N'00138' AND mtmatter=N'60040.1003' AND mtper=N'1206')OR(mttk=N'00250' AND mtmatter=N'60040.1003' AND mtper=N'1206')OR(mttk=N'01577' AND mtmatter=N'60040.1003' AND mtper=N'1206')OR(mttk=N'06800' AND mtmatter=N'60040.1003' AND mtper=N'1206')OR(mttk=N'06420' AND mtmatter=N'60043.1004' AND mtper=N'1206')OR(mttk=N'09995' AND mtmatter=N'88888.1389' AND mtper=N'1206')OR(mttk=N'03834' AND mtmatter=N'90038.1077' AND mtper=N'1206')OR(mttk=N'09972' AND mtmatter=N'90038.1077' AND mtper=N'1206')OR(mttk=N'07101' AND mtmatter=N'90321.1105' AND mtper=N'1206')OR(mttk=N'09950' AND mtmatter=N'90321.1108' AND mtper=N'1206')OR(mttk=N'01708' AND mtmatter=N'90321.1113' AND mtper=N'1206')OR(mttk=N'06877' AND mtmatter=N'90321.1115' AND mtper=N'1206')OR(mttk=N'07097' AND mtmatter=N'90321.1115' AND mtper=N'1206')OR(mttk=N'09839' AND mtmatter=N'90321.1117' AND mtper=N'1206')OR(mttk=N'00113' AND mtmatter=N'90321.1200' AND mtper=N'1206')OR(mttk=N'09762' AND mtmatter=N'90407.1077' AND mtper=N'1206')OR(mttk=N'09911' AND mtmatter=N'90407.1077' AND mtper=N'1206')OR(mttk=N'07072' AND mtmatter=N'90407.1079' AND mtper=N'1206')OR(mttk=N'03031' AND mtmatter=N'90407.1080' AND mtper=N'1206')OR(mttk=N'07091' AND mtmatter=N'90407.1080' AND mtper=N'1206')OR(mttk=N'01005' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'01556' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'01655' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'02704' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'03031' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'03102' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'03638' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'03734' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'04163' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'06853' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'07007' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'09023' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'09934' AND mtmatter=N'90407.1081' AND mtper=N'1206')OR(mttk=N'01005' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'01601' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'03031' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'03102' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'06853' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'07007' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'09907' AND mtmatter=N'90407.1082' AND mtper=N'1206')OR(mttk=N'09894' AND mtmatter=N'90714.1000' AND mtper=N'1206')OR(mttk=N'00148' AND mtmatter=N'90714.1003' AND mtper=N'1206')OR(mttk=N'00252' AND mtmatter=N'90714.1003' AND mtper=N'1206')OR(mttk=N'09828' AND mtmatter=N'90714.1003' AND mtper=N'1206')OR(mttk=N'00148' AND mtmatter=N'90717.1000' AND mtper=N'1206')OR(mttk=N'00148' AND mtmatter=N'90717.1003' AND mtper=N'1206')OR(mttk=N'09833' AND mtmatter=N'90717.1003' AND mtper=N'1206')OR(mttk=N'06723' AND mtmatter=N'90722.1000' AND mtper=N'1206')OR(mttk=N'03031' AND mtmatter=N'90722.1007' AND mtper=N'1206')OR(mttk=N'09833' AND mtmatter=N'90843.1000' AND mtper=N'1206')OR(mttk=N'06922' AND mtmatter=N'91300.1065' AND mtper=N'1206')OR(mttk=N'06877' AND mtmatter=N'92001.1008' AND mtper=N'1206')OR(mttk=N'00261' AND mtmatter=N'92001.1038' AND mtper=N'1206')OR(mttk=N'00094' AND mtmatter=N'92001.1055' AND mtper=N'1206')OR(mttk=N'06863' AND mtmatter=N'92001.1060' AND mtper=N'1206')OR(mttk=N'00166' AND mtmatter=N'92001.1079' AND mtper=N'1206')OR(mttk=N'07127' AND mtmatter=N'92001.1079' AND mtper=N'1206')OR(mttk=N'00289' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'00293' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'00294' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'00297' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'00300' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'00308' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'00581' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'01014' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'01027' AND mtmatter=N'93000.1000' AND mtper=N'1206')OR(mttk=N'01029' AND mtmatter=N'93000.1000' AND mtper=N'1206')

I cannot understand how or why it crafted the query the way it did to perform the find.

By the way my query returned results in under 3 seconds, filemaker's query took 3.5 minutes on a table containing close to 5 million records. I have also connected filemaker using ESS to SQL tables with record counts close to 20 million records and gotten response times to finds almost as fast as if I had written the query myself and executed it in Query Analyzer.

I wish I knew more about how the query is formulated.

Edited October 4, 2007 by Guest

[TimD]

Two suggestions:

[1] Try doing that same find while you're on a layout that is based on the shadow table, and where the fields on the layout are exclusively from the shadow table. You might see FM generate the SQL differently.

[2] When you do the find, try searching on the "mtper" with this value: ==1206

-- Tim

[fmsavey]

Well thank you Tim. Your second suggestion worked better then expected. The find returned 20K+ plus records from the 5 million records in seconds. Three cheers for the "field content match" operator "==". This operator is something I never gave much thought to until now. I wonder what is it telling FM to do differently.

[TimD]

"I wonder what is it telling FM to do differently."

If I had to guess, I'd say that the "mtper" column is actually a numeric column (integer type) and that by specifying "==1206" FileMaker is now treating it as such.

Have your DBA intercept the queries now, and see how they are different from the way they looked before.

-- Tim