P4 vs RDBMS

[tommy]

Pat, what are your recommendations for which files to either move or shadow to RDBMS ?

[pcasey]

That depends on what your goals are for bringing in an RDBMS in the first place.

A common configuration is to put the "active" tables on an RDBMS and leave the system and "lookup" tables behind in P4. In that way, you can use the RDBMS backup system to backup the RDBMS whenever you need too, and just do a file system backup on the P4 file system.

If you truly get all your active tables onto the RDBMS, then your P4 file system won't ever change since there's in insert/update/delete activity going on there, so you can dispense with daily backups of the P4 file system and just back it up once.

By "active" tables I mean problem, probsummary, device, etc.

System tables are things like format, code, displayoption, etc.

Lookup tables are things with essentially static data that nonetheless get queried a lot like category or devicetype.

You'll notice, of course, that not all of the system or lookup tables are truly static. If you, for example, log on as administrator and move a misplaced label on a form, then you just alters some "system" data. Likewise if you add a new category to the system, you have, again, just altered some lookup data.

In practise then, people generally end up backup up both their RDBMS and the "rump" of their P4 file system in a heterogenous system as I described above.

A mixed system will give you better performance than a pure RDBMS system, while still letting you feel comfortable that your critical data e.g. the list of problems, inventory, etc. is stored on, and backed up by, for example Oracle. Likewise it'll be in Oracle to be reported on or otherwise manipulated through any industry standard tool that can read Oracle data.

The downside to a mixed system is that, its got more moving parts than either a pure P4 or a pure RDBMS system and as a result its harder to back up, generally more awkward to recover, and logically would seem more failure prone.

So its really going to depend on your organization's goals. If you want to get on an RDBMS to simplify and centralize your data store, I'd recommend you just get a very powerful RDBMS server and fully migrate.

If, on the other hand, you are already on an RDBMS and things are slow and/or the fastest RDBMS server you could get isn't quite fast enough but you still have a requirement to get data on an RDBMS, then look at doing a mixed implementation.

I hope this helps,

--- Pat

[tommy]

Thanks Pat.

That answer is more usefull than any answer I have received every time I have asked Peregrine support about this. And it is the backup/restore that I am most concerned about. We run hot backups every second hour because that is that max data loss our helpdesk will accept in case of recovery.

The main purpose of us to move or shadow files to RDBMS is for reporting purposes and online viewing for customers.

[tommy]

Pat, You said all things equal SC/P4 would be faster than SC/RDBMS on the same hardware.

Is that true no matter how much data is in P4 / RDBMS given that indexes are setup properly?

[Andy Horwood]

In our environment we have a lot of ODBC access for reporting purposes. For this reason, we are going to migrate our operator file back again to P4 as we have only recently discovered that the passwords are visible !! ops:

Andy

[pcasey]

Pat, You said all things equal SC/P4 would be faster than SC/RDBMS on the same hardware.

Is that true no matter how much data is in P4 / RDBMS given that indexes are setup properly?

In my experience it is Tommy.

Again, the issue isn't that P4 is a faster database system than, Oracle or MS-SQL in a specific atomic operation. It isn't. The issue is that, given the way ServiceCenter runs, ServiceCenter on P4 runs faster than ServiceCenter on a commercial RDBMS (ServiceCenter was, after all, designed to run on P4, not a commercial RDBMS).

[ResidentGenius]

I've often heard that the amount of data that is contained plays a big role in how fast P4 is. For larger systems (> 1,000,000 records in probsummary etc) an RDBMS will be faster than P4, but for smaller systems, using P4 is faster due to the fact that SC was designed around it, and that there is no network traffic to contend with.

[outlance]

Hi,

I have heard this as well, but we won't have to think about it any more in the future, 'cause with SM7 the P4 database is removed... ;-)

Lars