Delete All Records on server

[Mandu]

I've moved a solution onto FM Server and am hitting into a few bad performance surprises. I don't know how Filemaker divides its work between the server and the client, but I have a hard time understanding why Delete All Records is as slow as it is.

Basically, I have a "work" table that needs to be emptied every time, prior to being used. It can have typically around 7000 records in it, and it just crawls on the Delete All Records step.

Import is also a problem. I'm simply importing into my work table from the found set in another table in the same database, but it only manages about 5 per second when it runs on the server.

The server runs FMS9. I don't know a whole lot else about it at this point.

What accounts for this terrible performance; and how can it be remedied?

Thanks,

Chap

[Steven H. Blackwell]

What accounts for this terrible performance;

Hard to say from the sparse description you gave. FIle architecture could be one issue. Serevr configuration is likely another including drives, memory, etc. The slow delete might also be the result of cascading deletes in the graph.

Much of this happens on the client, and then is updated to the server.

Steven

[mr_vodka]

As Steven said, we need more info on your setup. What are your server specs (especially hard drive)? What kind of network are you running on? Do you have lots of images?

You can also try deleting from a very simple layout with perhaps just the key field for optimization of speed.

[Mandu]

I'm waiting for the client to get me contact info for the internet-based Filemaker server host, at which point I will be able to provide more info. Here's what I can provide right now (sorry for the formatting):

Customers:

address........Text......Indexed 

city...........Text 

company........Text......Indexed 

Counter........Number....Serial Number on creation with Current Value: “1” Increment: “1” 

Customer_ID....Text......Serial Number on creation with Current Value: “C-00001” Increment: “1” 

email..........Text......Indexed 

firstName......Text......Indexed 

gx_RowColorSwatch...Container [3]...Global 

lastName.......Text......Indexed 

phone..........Text 

state..........Text 

sum_uniqueEmails...Summary...(Number) = Total of uc_Unique (running) 

uc_numberOfCustomers...Calculation...(Number) Unstored, from Customers, = Get ( FoundCount ) 

uc_RowColor....Calculation...(Container) Unstored, from Customers, = GetRepetition ( gx_RowColorSwatch ; ( 1 + Mod (Get ( RecordNumber ) ; 3 ) ) ), Evaluate even if all referenced fields are empty 

uc_Unique......Calculation...(Number) Unstored, from Customers, = If ( Counter = Customers_SelfJoinedOnEmail::Counter; 1; 0 ) 

uc_UniqueEmails...Calculation...(Number) Unstored, from Customers, = Sum ( uc_Unique) 

zip............Text......Indexed 

Customers table is involve in only one relationship, a self-join, without cascading delete.

My laptop is wirelessly connected to an Apple Base Station Extreme ("g"), then to a cable modem, to the Internet, and to the host. My upload speed is ordinarily around 140 KB/sec (download 2.0 MB/sec). I am seeing traffic rates of 3-5 KB/sec during the Delete. (Measured with MenuMeters.)

As suggested, I'll also try deleting from a minimal layout.

Will post server specs when they become available.

[Mandu]

This is what I hear from the host, www.pointinspace.com:

Your database is on a server that is average 5% loaded, and on a tier-1 3.1Gbps network. That said, the issue would not be with server performance but most definitely with the architecture of your solution or client-side network/machine performance.

I also learned that their hardware includes: OS X based Macintosh Xserve Intel Xeon, G5 and G4 servers. Their software includes FileMaker Server 9v3 Advanced.

Seems to me it's safe to guess at this point that the host's hardware/software is not the issue.

[mr_vodka]

Ah well you neglected to mention that this was going through a Hosting provider. Perhaps its network traffic or your ISP.

[Mandu]

Well, during the initial connection, while I'm waiting for the first window to appear, I'm seeing inbound network rates of between 60 KB/s and 160 KB/s. I realize there are myriad factors affecting network throughput, but this is still a heck of a difference from the 3-5KB/s I observe waiting for Delete All Records to complete.

Like I say, I really don't know how FM distributes the data and the processing between the server and the client. I do know that my laptop has 4G of RAM, and a 2.4GHz Core-2 Duo processor, and shows almost no impact on CPU, Disk, or paging activity during this.

I tried deleting from a minimal layout (consisting solely of Customer_ID), and I removed the global image and the unstored calcs that return containers from the table definition, to no avail.

If someone can point to a technical document that describes how FMS and FMC distribute data and share the processing when running a script, that might provide me with some valuable clues.

Thanks,

Chap

[xochi]

I suspect your problem is latency, not bandwidth. I think for a record to be deleted, the client must tell the server, which must then send a confirmation back to the clients. Many packets going back & forth.

Are you able to use server-scheduled-scripts (SSS)? These run on the server directly and would probably be 10x to 100x faster, though they are tricky to script and have limited functionality. However, a simple "delete all records" SSS should be pretty easy.