Jump to content

  •  

Photo

SQL Select through Relationship Help


  • Please log in to reply
21 replies to this topic

#1 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 20 December 2013 - 04:57 PM

Hi,

 

I need some assistance.

 

I have a table with 2500 records.

 

The current SQL select looks at the entire table if I'm not correct.

 

I need my SQL Select to look though a relationship that only list records for a date range we select.

 

I've attached my current SQL Select statement with where I think the new "Relationship Table Occurrence" should go.

 

Could you kindly please have a look see and guide me here where I would place my "Table Occurrence" so my SQL Select will only look for a "Match" though "A Date Range" relationship table occurrence.

 

Thank you.

 

Tom :-)

Attached Files


  • 0

#2 eos  Paris 2016 … :-)

eos
  • Members
  • 1,117 posts
  • LocationTegernsee, Bavaria
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 212d 10h 15m 36s

Posted 21 December 2013 - 12:41 AM

The current SQL select looks at the entire table if I'm not correct.

 

ExecuteSQL works outside the FileMaker relationships; a TO name serves merely as an access point into its base table, and you're always starting out with the complete table. So it doesn't matter where you look at.

 

Being in the right context is only necessary to have any FileMaker Expressions used as parameters evaluated correctly. If you're not using those, then it also doesn't matter where you're looking from.

 

PS:  Shouldn't that read “if I'm not mistaken”?


  • 1

#3 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 22 December 2013 - 09:02 PM

Hi Eos,

 

Yes, my mistake … "mistaken" … sorry about that.  Trying to isolate a problem.  Please read at tour leisure, any assistance, insight, guidance I would be grateful.

 

So when I say "DATE = ?" the SQL still scans the entire table ??

 

The reason for my question is that the data loads very quick on the local PC for this one layout, but when hosted the page loads extremely slow.  One of the reason, I determined was a value list where the customer has built over 500 within the list.  You didn't notice it on the local PC but again when the datafile was hosted, as you navigated to, and as you tried to scroll, the value list would build for each record ( apparently ) causing this painful slow down on this particular page.  We disabling the Value List.

 

The same problem with the SQL page, the page loads extremely slow ( 42 SQL Select Statements ) as compared to the ( 42 SQL Select Statements ) on the local PC, the page loads extremely fast.  But when we host the file … painful slow down ( loading ) and I have been unsuccessful in finding the field (s) that may be causing the delay. ( Strangely enough,  the SQL SELECT queries the same table where the value list of 500 values had been assigned through a relationship within the Value List Index.  Though the "Value List" is no longer assigned to that layout ... to that field … the SQL Select queries the same table where the initial slow down occurs.

 

Could the value list still be effecting the Table ?? Even thought ( as far as I can tell ) the value list is not assign to a field on that particular table occurrence anymore.

 

Thank you.

 

Tom :-)


  • 0

#4 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,603 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 4h 44m 37s

Posted 23 December 2013 - 06:46 AM

Couple of questions:

 

- when are the SQL queries fired?  Do you have them a part of calculated fields?

 

- are any of the fields that are selected by the SQL statement unstored themselves?

 

- how many records in the table?


  • 0

#5 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 29 December 2013 - 06:41 AM

Hi Wim,

 

I copied your question and answered in "RED"

 

- when are the SQL queries fired?  Do you have them a part of calculated fields?

Yes, I have a Calendar Table ( one record ) 42 calc fields looking to the "Remedy" table

 

Calc Field 1 … 42
ExecuteSQL ( " 
 
Select Name, Remedy
 
From Remedy
 
WHERE ConsultDate = ?
 
Order By Name Asc " ; "  -  " ; "" ;
 
cDateOfFirstPortal + 15  )

 

- are any of the fields that are selected by the SQL statement unstored themselves?

Hmm … not sure, let me check.  I am looking at a … CalendarDay = "ConsultDate" from the Remedies Table and I list two fields: Name and Remedy

 

- how many records in the table?

Not that many … 1200


  • 0

#6 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,603 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 4h 44m 37s

Posted 29 December 2013 - 07:13 AM

The delay very like comes from "cDateOfFirstPortal" , how is that field defined?

 

Are users typically on a Calendar layout when they enter new Remedy records?

 

As to your question: yes, SQL needs to look at the whole table/index to figure out what records match.  Same as a find, same as displaying related info.

 

Is the ConsultDate a regular date field and indexed?


  • 1

#7 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 29 December 2013 - 10:00 AM

Hi Wim,

 

Are users typically on a Calendar layout when they enter new Remedy records?

No … they click the calendar button and to print.

 

I use to have a Portal Filter for each day based on the field "cDateofFirstPortal" + … That was way to slow, and I started my SQL Tutorials trying to speed up the calendar and "It works great!" on the local machine but on the networks hosting Filemaker, it is still very slow.

 

I kept the field name "cDateOfFirstPortal" since it is just a calculation to determine the first square of the calendar it is defined as follows: 

 

Let ( [ 
 
FirstDayOfTheMonth = DayName ( Date ( gSelectedMonth ; gSelectedDay ; gSelectedYear ) ) ] ;
 
( Date ( gSelectedMonth ; gSelectedDay ; gSelectedYear ) + 1 ) - 
 
Case (
FirstDayOfTheMonth = "Monday" ; 1 ; 
FirstDayOfTheMonth = "Tuesday" ; 2 ; 
FirstDayOfTheMonth = "Wednesday" ; 3 ; 
FirstDayOfTheMonth = "Thursday" ; 4 ; 
FirstDayOfTheMonth = "Friday" ; 5 ; 
FirstDayOfTheMonth = "Saturday" ; 6 ; 

FirstDayOfTheMonth = "Sunday" ; 7 )

 

)

 

So let say Let me check your other request … I'll be back :-)


  • 0

#8 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,603 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 4h 44m 37s

Posted 29 December 2013 - 12:28 PM

A couple of design implications:

 

1) your calculation seems to include at least one global field and that means that it can only be calculated on the client.  And since you are calling it 42 times you are causing a lot of data to flow between the client and the server.

 

Find a way to set that field as part of a scripted workflow instead of using calculations.

 

2) ExecuteSQL may force FMS to send the client all the data in the Remedy table if there is an open record in Remedy. Can't tell if that is the case in your scenario but it is one of the main reasons why I stay away from ExecuteSQL in field definitions and conditional formats etc: you have no control over when the calc fires so you can not prevent it with open records.

 

Since you are only seeing the slowdown when the files is hosted, it is a clear sign that the inefficiency is in data flow to and from the server.


  • 1

#9 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 22 January 2014 - 11:20 PM

Hi Wim,

 

Sorry for the delay,  

 

I get no emails from this forum even though I've selected the "Follow" and I've been in my preferences and I believe I've selected all that is appropriate but yet now email …  so anyway, could you force the forum engine to email me when you reply this this ???

 

tomtheriault@me.com

 

Global field for my date reference only all other fields not global.

 

So are you saying that if I have a script that sets the 42 fields ( one record on the calendar table ) it will be faster ?? Rather than having the SQL in a calculated field 42 times ??  Here is the calc in each fields … ( Not global )

 

ExecuteSQL ( " 
 
Select ClientFNameLInitial, RemedyPotencyCalendar
 
From Calendar_Dates_to_Remedy_DateStart 
 
Where DateStart = ? and ClientType = ?" ; "  -  " ; "" ; 
 
cDateOfFirstPortal + 1 ; Type )
 
Also, can you clarify "OPEN" records.  I am helping a guy who developed his file now he needs help continue his development and I would have to say that I am to sure how the client is one the file.  If they select a field and not do anything while the consult with a client, I would imagine that you would have an open record.  But, can you clarify more, the "OPEN" record concern I would be grateful
 
Thank you.
 
Tom :-)

  • 0

#10 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,603 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 4h 44m 37s

Posted 23 January 2014 - 07:54 AM

Yes, I'm saying that if you can get away from your calculations being unstored (which I'm guessing they are) then it will be faster.

 

Unstored calcs will be calculated on-the-fly whenever FM needs them and you do not have much control over when that is.

 

Open records: when the user puts the cursor in the field: that does not open a record.  When they start editing a field then FM opens the record (or if the developer takes control and uses the "open record" script step).

A new record also starts in the "open" state


  • 0

#11 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 25 January 2014 - 05:40 AM

Hi Wim,

 

Me again …  If what I am stating sounds confusing, I'm confused.

 

Obviously I need more understanding … calendar page load is still very slow.  Not sure what I'm doing wrong here.  I am still referencing three global fields ( Month, Day, Year ) within the "cDateOfFirstPortal" which is within each SQL Select Statement.  That has not changed.

 

I've moved the SQL Select Statement's that were calculating within each field ( 42 of them ) to a "SET FIELD" step within a Script … 42 set fields script steps as instructed.

 

​So, Wim … One Table, One Record … that is may calendar with … 42 fields.  

 

Since I am still very slow … can you restate the global issue with Filemaker again.  I have 42 set fields within a script now ( field calculations removed ) … but within each SQL STATEMENT I am still referencing this field "cDateOfFirstPortal" ( which has three global fields within it … Let Statement Above ).

 

​Are you saying that each "Set Field" script step is forcing some "LOAD" on the FM SERVER due to the the fact that I'm referencing this "cDateOfFirstPortal" within each SQL Statement ?? which the global fields resides on the clients machine … which does what again ??? to the FM server ???

 

And this is why I have a "SLOW" page load.  Yes / No / Maybe ?? :-)

 

If I change the three global field ( Month, Day, Year ) to three number fields ( since I only have one record ) the calendar will still build.  Will I have any issues with more than one user on the calendar ??

 

1) your calculation seems to include at least one global field and that means that it can only be calculated on the client.  And since you are calling it 42 times you are causing a lot of data to flow between the client and the server.

 

Find a way to set that field as part of a scripted workflow instead of using calculations.

 

The 3 global fields ( Month, Day, Year ) are set on page load.  So I shouldn't need to worry about globals on my calendar … Yes / No ??

 

Thank you for any insight here, and thank for your previous suggestions.

 

Tom

tomtheriault@me.com


  • 0

#12 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,603 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 4h 44m 37s

Posted 25 January 2014 - 07:27 AM

At this point we're all confused I think, this discussion is a little too abstract without being able to inspect the file and the actual flow.

 

To troubleshoot:

- if the file is local and all is fast then it is a data-flow issue because of the calcs & scripting that forces client and server to communicate too much and send too much data

- with the file hosted again, disable the 42 set fields and see if it is fast.  My guess is that it will be fast.  That demonstrates that the issue is caused by the calc that is used in the Set Field

- disable only 21 set fields and see if the performance penalty is linear

- in general; find a more efficient way to produce the Set Field calc

 

From looking at the cDateOfFirstPortal field; I guess it is going to e unstored because of the globals.  Does it need to be a field?  Does it need to be calculated?  Sounds like something that could be set fairly easily as part of the workflow and not calculated


  • 0

#13 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 27 January 2014 - 11:26 PM

Wim,

 

I'm helping the person who developed the datafile and it is difficult to work within his methodology ...  

 

Reviewing your suggestions …

 

Yes … Each Day I add the calendars slows.

 

Yes … Both fields within the SQL statement can not be stored … Filemaker will not let me.  And the information is coming from another relationship. 

 

Last questions promise … If I replace each SQL with an actual relationship map for each day rather than using SQL which if I'm not mistaken, looks at the entire data table 42 times, would I be able to work around the slow page load by using relationships for each day instead ??  No filtering ??

 

Thank you.

 

Tom :-)


  • 0

#14 comment  consultant

comment
  • Members
  • 24,322 posts
  • Time Online: 335d 18h 32m 25s

Posted 27 January 2014 - 11:54 PM

If I replace each SQL with an actual relationship map for each day rather than using SQL which if I'm not mistaken, looks at the entire data table 42 times, would I be able to work around the slow page load by using relationships for each day instead ?

 

That's a very good question. Why don't you try it and let us know; I'd like to know if my bet is correct.

 

 

In any case, 42 relationships - native or SQL - is no way to construct a calendar - see: 

http://fmforums.com/...endar-april-06/


  • 0

#15 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 28 January 2014 - 06:35 PM

Hi Comment,

 

The link is not working ??

 

Any other link ??

 

I would be grateful

 

Thank you.


  • 0

#16 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 28 January 2014 - 06:43 PM

Comment,

 

Never mind, I went to the website, thanks.


  • 0

#17 comment  consultant

comment
  • Members
  • 24,322 posts
  • Time Online: 335d 18h 32m 25s

Posted 28 January 2014 - 08:08 PM

What website? You should read the thread!


  • 0

#18 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 06 February 2014 - 06:06 AM

Hi Comment,

 

Well I read the posts … hmm … I am not creating any records … not on the Calendar at least.

 

Basically, a person ask me to create a concept similar to a Calendar, meaning that they have a REMEDY table that holds records; each records holds ...

 

Customer Name :: Relationship to Customer Table ( No-Indexing allowed ) ( Stilling Learning about this Obscure Concept )

Consultation :: Relationship to Consultation Table ( No-Indexing allowed )

Remedy :: Relationship to Pharmacy Table ( No-Indexing allowed )

Potency :: Relationship to Pharmacy Table ( No-Indexing allowed )

 

The only records that have actual data in the REMEDIES table are "Set" with the ID's … so customer ID, Consultation ID, Pharmacy ID ...

 

And from these key fields, he populate the other data fields with calculation / relationships which are non - indexing and non storing ...

 

He wants to list in the calendar those records on the days the remedy were written with "client name and potency" and now my recent underdeveloped understanding of the Calendar results which are based on un-stored calculations fields have cause an extreme slow down.

 

My Solution … I'm thinking … have the user ( owner of the database ) create ( write data / copy / import / set … what ever ) to the Calendar Table every time a record is created on the Remedy Table where there would be NO RELATIONSHIP anymore ( only real data ) than use a self relationship to the DATA on the Calendar Table via a GLOBAL DATE FIELD.

 

That should speed things up … Yes ? No ? 

 

Thank you so much for your guidance and assistance here. ( Everybody ) I am grateful.

 

Tom :-)


  • 0

#19 comment  consultant

comment
  • Members
  • 24,322 posts
  • Time Online: 335d 18h 32m 25s

Posted 06 February 2014 - 10:19 AM

I am afraid you have lost me completely. I have no idea what this is about. For example, you say that the REMEDIES table has Customer ID, Consultation ID, etc. - but just before that you said that each record holds "Customer Name :: Relationship to Customer Table ( No-Indexing allowed )". This makes no sense whatsoever, esp. since you cannot have a relationship with no indexing.

 

In any case, I don't see what all this has to do with the calendar issue. A calendar is a device to display events on a grid. An event is something that has a date attribute. Is there a table there that has a date field? If so, that would be your "events" table. And the calendar should be able to show the "real" data from this table, with no need for "copy / import / set … what ever".


  • 0

#20 Snozzles  newbie

Snozzles
  • Members
  • 175 posts
  • LocationBaltimore, MD
  • FM Application:12 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 2d 5h 42m 41s

Posted 06 February 2014 - 07:01 PM

Hi Comment,

 

I apologize for the confusion and as I am trying to understand what the problem is I'm stuck … I've attached three Field definitions with the message I get when I try to Index the fields.

Attached Thumbnails

  • Screen Shot 2014-02-06 at 9.55.10 PM.png
  • Screen Shot 2014-02-06 at 9.55.53 PM.png
  • Screen Shot 2014-02-06 at 9.57.15 PM.png
  • Screen Shot 2014-02-06 at 9.54.46 PM.png

  • 0




FMForum Advertisers