Optimizing ODBC

[kwebb]

I have a FM DB with over 6,900 records. The size of the file is over 9MB. I am accessing the DB through an ASP page using the FileMaker ODBC Driver. It is working; however, very slowly.

My Server:

2- Pentium III 500MHz

768 MB of Memory

SCSI Hard Drive

Windows NT 4.x

IIS

FileMaker Pro 5.0 Unlimited

Web Server Connector

If I do a simple search, returning only two small text fields with about 63 records it was taking 12 seconds to execute. I found the way to Optimize the DB using the Save As command. However, now the same exact SQL statement (returning two small text fields for about 63 records) is taking about 35 seconds to execute. I thought that optimizing a DB not only made is small by removing deleted or no longer used information and make the DB more efficient for results; however, that has not turned out to be the case. As stated above the same exact SQL statement, no changes, is taking twice as long.

Can anyone explain this phenomonon?

Can anyone provide additional ways to speed up the DB execution?

FYI, I have an ASP page that connects to an AS/400 DB (via IBM Client Access OLE) and performs 4 sql searches with 3 - 5 sec response times. This page also returns a lot more information that what I am trying to do with FM.

------------------

Thanks,

Kevin

Yahoo! Messenger: prof_bunsen

[Kurt Knippel]

quote:

---

Originally posted by kwebb:

I have a FM DB with over 6,900 records. The size of the file is over 9MB. I am accessing the DB through an ASP page using the FileMaker ODBC Driver. It is working; however, very slowly.

My Server:

2- Pentium III 500MHz

768 MB of Memory

SCSI Hard Drive

Windows NT 4.x

IIS

FileMaker Pro 5.0 Unlimited

Web Server Connector

If I do a simple search, returning only two small text fields with about 63 records it was taking 12 seconds to execute. I found the way to Optimize the DB using the Save As command. However, now the same exact SQL statement (returning two small text fields for about 63 records) is taking about 35 seconds to execute. I thought that optimizing a DB not only made is small by removing deleted or no longer used information and make the DB more efficient for results; however, that has not turned out to be the case. As stated above the same exact SQL statement, no changes, is taking twice as long.

---

Start by seperating your web server from the FMP Unlimited. This could be the cause of the slowdown, and why it is so much quicker to access the AS/400 system.

Generally the setup is: Network users <--> FMP Server <--> FMP Unlimited <--> WebServer <--> Internet users. All configured on seperate machines.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[kwebb]

I don't want to push this issue; however, I copied another DB from FM to Access, everything the same. (Only 113 records)

FM takes 59 seconds to return the same information that it takes Access 2 seconds. The ASP page is exactly the same except for the DSN reference to the ODBC.

There has to be a better solution. Our server does not have any problem handling Microsoft SQL and IIS on the same box. Better performance without even trying to optimize.

------------------

Thanks,

Kevin

Yahoo! Messenger: prof_bunsen

[Kurt Knippel]

quote:

---

Originally posted by kwebb:

I don't want to push this issue; however, I copied another DB from FM to Access, everything the same.

(Only 113 records)

FM takes 59 seconds to return the same information that it takes Access 2 seconds. The ASP page is exactly the same except for the DSN reference to the ODBC.

There has to be a better solution. Our server does not have any problem handling Microsoft SQL and IIS on the same box. Better performance without even trying to optimize.

---

I would take a step back and look at the bigger picture.

If using Access (without any kind of optimization) is a better solution, then do so. It is probably not worth your time to make Filemaker perform exactly the same.

Now if there is a reason why you want to keep using Filemaker, then let's look at the rest of the solution.

I would ALWAYS (read that as EVERY SINGLE TIME) recommend that you run all of your servers seperately. DB on one machine, Web on another, FMP Unlimited on another machine, and so on.

This is not specifically a Filemaker recommendation. I have had clients who insisted on running MS SQL Server and IIS on the same machine and it never had the same security/performance/stability of running them on 2 seperate machines. MS products are also notorious for behaving better with each other than than with competitors products.

There are also a whole slew of possible issue at play when you run these applications together. There are memory issues, harddisk issue, network issues, processor issues, etc. Any or all of these can cause drops in performance, and they are nearly impossible to identify when these application are running concurrently. Seperating them onto different machines is the best way to make them all operate most efficiently.

Again if you are simply looking for the best performance and Access provides it, then use Access. Otherwise seperate the applications to seperate machines and then start observing/tweaking performance.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[kwebb]

I am curious if anyone has taken the time to run a performance test with FMPro Unlimited and IIS (or other web server) on the same server and then separated them.

What were the specs of the servers involved?

What was the SQL statement?

What was is an example of the data returned?

What was the size of the FM DB?

What were the performance times?

------------------

Thanks,

Kevin

Yahoo! Messenger: prof_bunsen