fmsavey Posted October 3, 2007 Share Posted October 3, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
TimD Posted October 29, 2007 Share Posted October 29, 2007 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 Link to comment Share on other sites More sharing options...
fmsavey Posted October 29, 2007 Author Share Posted October 29, 2007 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. Link to comment Share on other sites More sharing options...
TimD Posted November 14, 2007 Share Posted November 14, 2007 "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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6142 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now