Removing many records while using Enterprise


#1

What command will allow the removal of many records en mass?


#2

There is no command for removal of many records at a time when using the server. You have to write a loop and delete one record at a time.


#3

Or select them if they have a common attribute - and how else would you define them if they didn’t?


#4

Depening on the number involved it can sometimes be faster to convert to Single User, delete and reshare.


#5

Not really a worthy option in this particular situation. I am using Panorama Enterprise as a server receiving ‘heartbeats’ from remote machines on an every minute basis telling me the status of modules at each remote computer. Now in addition to receiving reports from each computer on an every minute basis, I must also be deleting historical reports on that same every minute basis so as to not unnecessarily burden the speed of reporting. Historical reports have value but not much beyond a few days.

I was hoping for some sort of ServerUpdate statement that turned off record locking, a mass delete locally, then some sort of Synchronize that would push the client set to the server. I’ve been doing the Loop but found it to be quite a burden effectively doubling my server busy time.

I thought of a scheduled conversion to Single User but do not see a way to automate this.

Robert Ameeti


#6

The synchronization system Panorama Server uses requires that it keep a record of deleted records. It was designed with the assumption that the number of deleted records would be a fraction of the active records. The only thing that clears out the record of previously deleted records is converting to single user.

Your application is going to quickly have many more deleted records than actual records. This shouldn’t affect most operations, but it will cause synchronization to take longer and longer on the server end.

I once had a nearly identical data application to what you are doing, with data coming in from the web that became stale quickly. I made that database a web published database, but NOT a shared database. This made it very easy to quickly delete stale data, which I did at midnight. This removes the convenience of automatic synchronization of data to the client, but it can still be downloaded periodically.

I was hoping for some sort of ServerUpdate statement that turned off record locking, a mass delete locally, then some sort of Synchronize that would push the client set to the server.

That would work as far as it went, but then would cause performance issues in synching to other clients.

I suppose there could be a mode where only one client is allowed to connect to the server, that would solve a lot of these problems. And certainly that might be useful in many cases where perhaps only one admin is actually connecting to a particular database. Perhaps that could be a feature to think about in Panorama X Server in the future.


#7

This removes the convenience of automatic synchronization of data to the client, but it can still be downloaded periodically.

Would manual Synchronization (via the File menu) still be available? Or would the process be to run a simple procedure locally to delete the appropriate records, then manually upload the new version? Or could the upload/replace be an automated Panorama process?

Then too, during development (which never seems to end with databases), I am constantly modifying/enhancing many items which currently have many benefits from being shared. If it was a product that ever reached v 1.0 and I left it alone, perhaps the fantastic feature of constant synchronization would be unnecessary.

Robert Ameeti


#8

I’m a bit confused by your last message. You quote part of my reply, then a question appears in the quote, but I think the question is something new, not part of the quote? I’ll go with that.

Would manual Synchronization (via the File menu) still be available?

No. It has to be done thru the Transfer menu in the Database Sharing Options wizard. There is no synchronization – only upload or download. In my application, I occasionally download data to the client to look at it, but that is not part of the normal operation of the database. There is code on the server that automatically transfers summarized information from this database into other databases at midnight, and also archives the data in logs and deletes old data.

I am constantly modifying/enhancing many items which currently have many benefits from being shared

Yep, this database I am talking about was a pain to set up. I am not going to argue that this is super convenient or easy. However, this was not the scenario that Panorama Server was designed for. So you can bend it out of shape to get this done, but it is definitely going to be more work on your part. I still think this was a lot easier to do in Panorama than in other programs – I did very seriously look at using other database programs for this particular application. By the way, if you do some google searches, you’ll find that applications requiring massive database deletions are a difficult problem in any database program, at least if done at large scale and performance is an issue. In single user mode Panorama can do the deletions in almost zero time, but that comes at a cost of making it somewhat difficult to access the data off the server.

For my application I was able to do most of the development work in single user mode on a client, then I transferred it to the server. It turns out I had an archived data stream so I was able to run simulations on my local computer with real data to debug the code. Once it was working, I haven’t have to change it at all. This database is part of a set, all of the other databases in the set use regular shared databases. Summarized data from the intake database gets passed along into the other databases. Only the intake database is not shared. Since it is handling data that is coming in in a fixed format that doesn’t change, it hasn’t needed to change either. Hopefully you could also your design your intake database with enough flexibility that it wouldn’t need to change much after deployment.