Jump to content

Why does it take forever to count records by SQL Query with particular condition when using FIleMaker?


mak
 Share

This topic is 2581 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Why does it take forever to count with this SQL?

 

ExecuteSQL(

 "Select count(*)

  From TableName

  Where kanymd between '20140201' and '20140228'"

;"";""

)

 

btw, the format type of kanymd is based on Number, not date type.

 

 

For your information, it doesn't take time at all if I  try to Import records via ODBC with the same condition.

Also it takes less than a second if I do the same thing with MS Access.

 

Any advice?

Link to comment
Share on other sites

By using single quotes around the values, you are telling the db that it is text, not numbers.

 

Any difference if you try > and < instead of between?

 

Also: a little confused:  since you mention "import from ODBC", but at the same time use the ExecuteSQL() function: is this an ESS data source?

In that case, using ExecuteSQL on a TO from an ESS source has been reported to be slow in general because of the extra ESS layer.  The ExecuteSQL has to be translated and passed on to the external database and then back.

It may be faster to just do a find in FM and get the found count.

Link to comment
Share on other sites

Did you index: kanymd ?

 No, I haven't.

 

I think it doesn't really matter because it returns quick if I do "Import records from ODBC" with the same condition.

By using single quotes around the values, you are telling the db that it is text, not numbers.

 

Any difference if you try > and < instead of between?

 

Also: a little confused:  since you mention "import from ODBC", but at the same time use the ExecuteSQL() function: is this an ESS data source?

In that case, using ExecuteSQL on a TO from an ESS source has been reported to be slow in general because of the extra ESS layer.  The ExecuteSQL has to be translated and passed on to the external database and then back.

It may be faster to just do a find in FM and get the found count.

Thanks for an advice. I tried this now result returns "?"

It's so weird because indeed there are few records whose 'kanymd' are 20140201

 

ExecuteSQL(

 "Select count(*)

  From TableName

  Where kanymd = 20140201

;"";""

)

Link to comment
Share on other sites

Try:

ExecuteSQL(
 "Select count(*)
  From TableName
  Where kanymd >= ? AND kanymd <= ?"
;"";""; 
20140201 ; 20140228
)

"Between" can be really slow, I don't know why.

Link to comment
Share on other sites

This topic is 2581 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By Buckie
      Tried adding an ODBC source using both MySQL 8.0.22 and MariaDB 3.1.10 drivers in Unicode mode and I can connect just fine, however it's impossible to add a table onto the relationships graph. It sees the table's name but when I try to add it, I get 
      This action cannot be performed because the required table is missing. error. It works fine with ANSI version of the MySQL driver, sans the ability to work with Unicode of course. I've tried multiple combinations, including making the database and the table strictly "latin", it still refuses to add the table. The test database itself is very simple, just a single table and a single field, no spaces and no unicode characters in names. Test/test/test, basically, tested with an empty FM database. The server is running MariaDB 10.4.16. Any pointers to solve that?
    • By 34South
      I previously used ODBC Manager (32 bit)  to great success importing data directly from Filemaker Server to JMP. I recently upgraded to Catalina (MacOS 10.15.5) and knew that one of the casualties would be this ODBC utility. I downloaded the 64 bit ODBC manager from Actual Technologies and successfully installed it but get the following message when trying to open an FM database from within the ODBC interface in JMP:
      dlopen(/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so, 6): image not found
      I have navigated to Actual Technologies' web site believing I should download an ODBC driver but this comes at a hefty price tag, especially when converted to my local currency. Given the increasing costs of maintenance contracts and SSL certificates I had hoped to avoid further expenditure. Do I really need this and is there an alternative?
    • By andyCodling
      Apologies if I've put this in the wrong place.
      My Filemaker solution uses a FM database and an ODBC connection to a MySql database that is used to serve data to a website.
      I have complete read/write access to the data in the MySql database from inside FM and thus control of data published on the site.
      I would like the MySql database to be able to access tables in the FM database in the same manner.
      I have set up an FM ODBC connection to our FM server and that is visible in my local ODBC Manager, have tested the connection and that is good, but I can't see any way in the MySql apps I have installed on my system to create a table in the MySql database using live FM data, in the same way that I can make a table in FM using live data from the MySql database.
      Is it possible to do this?
      My main MySQL app is SequelPro.  And occasionally MySQL workbench and associated tools.
      Thank you.
       
      Andy Codling
       
       
       
    • By TimP
      I have been attempting to connect to a SQL database on my network using Monkeybread Software and each time that I attempt to do it I have encountered the same error, which I have attached here. I have set up an ODBC connection as well as made sure that I had the names correct, however I have gotten the same error message each time. Thank you for your help.

    • By Will_Logic
      Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data:
      BE_FileMakerSQL ( $sql_select;  "<c>" ; "<r>" ; $fm_file ) The string this returns now looks like for example: 352265<gwgwh<474848<<wegwgw<65755<gwgw ....
      i.e. it has only taken the first character of the delimiter, I changed delimiter string to test with eg "A≠" instead of "<c>", same problem, it then only inserts "A" as delimiter. Just wonder if anyone can guess what might have happened here?
      Thanks kindly any suggestions!
  • Who Viewed the Topic

    1 member has viewed this topic:
    Maria Helgeson 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.