June 8, 2010

Just a little update to my previous posting on RightNow‘s .NET API performance.

This API has me CRYING for their Apex Data Loader. Apex was FAST!!! It was extremely fast for selecting records. But it was even fast inserting and updating records. That was an awesome tool — dump the records out, massage them in Access using SQL, pump the records back in, go home an eat lunch. Done in 3hrs.

So here are the latest metrics:

RightNow .Net API

Extract: 1.5MM records in 1hr 4min

Update: 1.5MM records 3days+

Some of you may ask what is the update and how involved is it? I believe it to be a fairly simple task, myself. I’m updating each contact record and setting a custom field’s value to an externally assigned primary key. The one excuse I will allow RightNow is that there are 250 or so custom fields for the contact. I would expect some performance degradation, but a data conversion of 1.5mm records taking longer than 3days is just stupid.

I always lose sight of this when I need it. That and RightNow’s website hides this from anyone’s view (side rant: really, how does one search in Google for RightNow the product?). Gartner says RightNow is #1 for customer support KB search, but I can never find shit using their support KB search. (side rant: seriously how much was Gartner paid?)

Create a New Report via SQL

To do this, we’re going to be using a custom script.

Define a filter that will always return zero rows.filter: 1=0

Select Level > Custom Scripts

Select the ‘Finish tab of custom scripts

Paste in the following while modifying the SQL to your liking:$temp=array();
$row_idx = 0;
//Run the desired query
$query=sql_prepare
(sprintf("
SELECT label,ac_id, header_code, init_code, process_code, exit_code
FROM ac_scripts a, labels l
where a.ac_id = l.label_id
and tbl=121
and (header_code is not NULL
or init_code is not NULL
or process_code is not NULL
or exit_code is not NULL)
"));
//Each column in the SELECT clause will need to be returned as the appropriate data-type (INT for integer, NTS for string, DTTM for datetime)
sql_bind_col($query,1,BIND_NTS,80);
sql_bind_col($query,2,BIND_INT,0);
sql_bind_col($query,3,BIND_NTS,1000);
sql_bind_col($query,4,BIND_NTS,1000);
sql_bind_col($query,5,BIND_NTS,1000);
sql_bind_col($query,6,BIND_NTS,1000);
//For each record returned in the above query, return it as one row of output, each field in its respective column
while ($temp = sql_fetch($query))
{
$exit_obj[$row_idx][0]->val = $temp[0];
$exit_obj[$row_idx][1]->val = $temp[1];
$exit_obj[$row_idx][2]->val = $temp[2];
$exit_obj[$row_idx][3]->val = $temp[3];
$exit_obj[$row_idx][4]->val = $temp[4];
$exit_obj[$row_idx][5]->val = $temp[5];
++$row_idx;
}
//Clear your buffer
sql_free($query);

$exit_obj is the array returned, whose values are subsequently displayed in the report