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

[mak]

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?

[ggt667]

Did you index: kanymd ?

[Wim Decorte]

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.

[mak]

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

;"";""

)

[Fitch]

Try:

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

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