Executing SQL Command via ODBC takes a LONG time

[conjur]

Hey, all.

I have a very simple SQL statement that is only doing a SELECT on one field (and it's an indexed field, too) with no WHERE clause. When I get to this part of a Windows Scripting Host script:

iAffect = 0

msgbox "cmd.Execute(iAffect)"

set rs = cmd.Execute(iAffect)

The command takes about 20-30 min. to return. The .fp5 (under FM Pro 6) that I'm querying is about 7800 records.

Does anyone have any ideas as to why FM would take half an hour to just process that query before I even got to walk through the recordset?

[transpower]

Try using MSQUERY instead.

[conjur]

I've never used MSQuery. Is that something used within, say, an Office application?

I have to run these queries outside of FM and via scripts as they are part of our interface to the database that runs every 15 minutes to pull updated data.

FWIW, I've done with two other sites using Filemaker and it's worked fine. Not sure why this site's database is so slow. Is there some setting in Filemaker perhaps?

[transpower]

Yes. Word 2003 | View | Toolbars | Database | Insert Database | Get Data | Tools | MS Query | your SQL code

[conjur]

That doesn't do me any good then. This must run via command-line as a .WSF or .VBS file.

What gets me is why does this FM database behave so differently from others I've dealt with in the past in the same manner?

[transpower]

MSQUERY.EXE could be put in a script.

[conjur]

Well, I have Office 2000 installed and didn't find an MSQuery.exe on my harddrive. Is there some way to download it from MSFT?

[transpower]

The file name has been changed to msqry32.exe. It's in office2003office11.

[conjur]

Still has the same problem of taking FOREVER to return anything.

I'm examining other options with the developer including him writing data to a temp table that contains just those fields that I need.