Record retreival in SQL vs. P4

[ambarnes]

After a couple of months of testing using Peregrine with SQL, we went ahead and moved our production system onto SQL. Everything went ok, EXCEPT:

When taking new calls (cc.incquick.g) we have a fill box for the contact.name field. When you enter the employee ID in that field, it retrieves lots of data to fill in other fields. If you just hit the button, it retrieves all the records, which takes like up to 1 minute.

I spoke with Peregrine regarding this performance problem and they said to make sure we have indexes built into SQL. Well I went and talked with our main DBA's that handle the SQL server stuff, and yes, ServiceCenter DID actually export all the correct indexes to SQL. The problem is that the indexes Peregrine uses and the indexes needed for SQL aren't really compatible. So instead of only retrieving the first 30-40 records in the entire contacts table, it retrieves ALL of them.

I was searching earlier on here for a similar problem, but didn't find one. if I missed it, someone please point me in the right direction for a possible solution. Our DBA people were of the opinion that there really isn't much that can be done.

[ambarnes]

One thing I noticed recently. When logging in and going to the maintenance tab (or support tab under falcon) and going to contacts, then clicking search, it only returns the first 32 records, which is preferable.

How is that query different from our one used in the call screen that retrieves them all? Is there a way to modify it to retrieve only a few?

[pcasey]

ServiceCenter on P4 does "partial" queries against extremely large tables. If, for example, you do a true query against a table with 100,000 records, ServiceCenter pulls up just enough records to show you on screen and then re-queries on an as-needed basis as you scroll.

ServiceCenter on an RDBMS does "semi-complete" queries. Thus, a true query on a 100,000 record table will result in the server retrieving all 100,000 index records. ServiceCenter will then query out complete records in blocks of about 32 records until it has enough to fill your screen. It'll then poll more complete records as you scroll.

ServiceCenter's record retrieval strategy on an RDBMS is as follows:

1) Execute a query which retrieves all matching key values e.g. "Select number from probsummarym1 where open=true".
2) It'll then get back a list of matching key values, say PM0001, PM00007 and PM0008.
3) ServiceCenter then makes a *second* pass at the server to collect sufficient complete records to draw the current screen e.g. "Select * from probsummarym1 where (number=PM0001 or number=PM00007 or number = PM0008)

The key thing to recognize is that ServiceCenter's full record retrieval does take place in blocks of about 32 records, and Servicecenter won't retreive more than it has too.

ServiceCenter's key retrieval strategy though, always gets the full set of keys on an RDBMS, making queries with large result sets take longer on an RDBMS than on P4.

[alhowarth]

Aaron,

We had a similar problem when we converted to SQL 7....

We have two user fields, reporter/requestor and customer. Both are linked to the contacts table. A help desk user found that when searching problem tickets be the requestor it would take 1-2 minutes.

After working with Peregrine we found that, due to garbage characters previously entered in that field, the field was varbinary in SQL instead of varchar.

We rolled problem and probsummary back to p4, cleaned up the data, and forced it to varchar. Problem solved.

[ambarnes]

When you say "forced it to varchar", can you be more specific on that?

[alhowarth]

1. When table is back to P4.
2. Go to SQL mappings
3. Under SQL type change to varchar(60) or whatever you want
4. When you begin to push table back to SQL, select to Manually review maps and when starting, choose to keep existing mapping.

You have to pay close attention when the mapping is presented again. SC likes to mess it up (especially). Although I have the most problem with the Incident table.

Definately have some backups readily available as this has failed on more than one occasion. And it can corrupt the table.

al

[ambarnes]

I may have to give that a try some time. Hopefully it will work on SQL 2000, that's what we use. Do you just limit all character fields to 60? Currently we have them ranging anywhere from 30 up to 140. But a majority of them are 60.

[alhowarth]

No, we don't limit them, they have a range as well. Service Center tries to think for you and makes a guess.