PDA

View Full Version : Optimising a grid...



karieanis
21 Jul 2008, 2:28 PM
Hi guys,

Just looking for some suggestions on how I would go about optimising a grid i'm working on. It works fine, it's just so damn slow that the browser will throw a prompt giving the user the option of terminating the script as it takes so long. I think the problem may be with processing the amount of data i'm getting on the client side... just not sure what I can do about. Anyway, here's the server side code:



if($_SERVER['HTTP_X_REQUESTED_WITH'])
{
if($_GET['option']=='sendEmails')
{
$userIDs = array();
$data = json_decode(stripslashes($_POST['data']));
array_walk($data,create_function('&$v,$k','$v = "\"".$v."\"";'));
$stringData = implode(", ",$data);

$sql = "SELECT userID
FROM user
WHERE userEmail IN (".$stringData.")";

if($result = run_mysql_query($sql))
{
while($row = mysql_fetch_assoc($result))
{
$userIDs[] = $row['userID'];
}

if(!empty($userIDs))
{
if(!function_exists("email_NEW_STAFF_EMAIL"))
include_once("../includes/code/mail.php");

foreach($userIDs as $regUser)
{
email_NEW_STAFF_EMAIL($regUser);
}
}
}
else
{
print "Error: ".$result."\n".$sql."\n";
}
}
else
{

if($_POST)
{
if(array_key_exists('sort',$_POST))
{
$sort['by'] = stripslashes($_POST['sort']);
$sort['dir'] = stripslashes($_POST['dir']);
}
}

$dataStore = array();

$sql = "SELECT CONCAT(userFName,' ',userSName) as userName, userEmail,
portfolio.listElementText as portfolio,
workArea.listElementText as workArea,
function.listElementText as function
FROM user
JOIN listElement as function ON function.listElementID=userDepartment
JOIN listElement as workArea ON workArea.listElementID=function.listElementParentID
JOIN listElement as portfolio ON portfolio.listElementID=workArea.listElementParentID";

if(isset($sort) && is_array($sort))
{
$sql .= " ORDER BY ".$sort['by']." ".$sort['dir'];

if($sort['by']!='userName')
$sql .= ", userName ASC";
}

if($result = run_mysql_query($sql))
{
while($row=mysql_fetch_assoc($result))
{
$currentKey = sizeof($dataStore);

foreach($row as $key=>$value)
{
$dataStore[$currentKey][$key] = htmlspecialchars_decode($value,ENT_QUOTES);
}
}
}

print json_encode(array("users"=>$dataStore));
}

unset($sort);
exit();
}
and here's the extjs code (note that the ajax_handler var is defined via the contents of a smarty variable):



<script type="text/javascript">

var ajax_handler = '{$ajaxHandler}';
{literal}

Ext.onReady(function()
{
var xg = Ext.grid;
var storeCounter;

var json_store = new Ext.data.Store(
{
proxy: new Ext.data.HttpProxy(
{
url: ajax_handler
}),
reader: new Ext.data.JsonReader(
{
root: 'users',
id: 'userEmail',
fields: ['userName','userEmail','portfolio','workArea','function']
}),
remoteSort: true
});

json_store.setDefaultSort('userName','asc');

var selectModel = new xg.CheckboxSelectionModel();
var userGrid = new xg.GridPanel(
{
id: 'user-grid',
loadMask:
{
msg: 'Loading...',
msgCls: 'loading-indicator'
},
store: json_store,
cm: new xg.ColumnModel(
[
selectModel,
{id: 'username', header: 'Username', width: 70, sortable: true, dataIndex: 'userName'},
{id: 'useremail', header: 'User Email', width: 100, sortable: true, dataIndex: 'userEmail'},
{id: 'portfolio', header: 'Portfolio', width: 100, sortable: true, dataIndex: 'portfolio'},
{id: 'workArea', header: 'Work Area', width: 100, sortable: true, dataIndex: 'workArea'},
{id: 'function', header: 'Function', width: 100, sortable: true, dataIndex: 'function'}
]),
sm: selectModel,
viewConfig: {forceFit:true},
buttons: [
{
text: 'Send',
handler: function()
{
if(userGrid.getSelectionModel().hasSelection())
{
var selected = userGrid.getSelectionModel().getSelections();
var emails = new Array();

for(var i=0;i<selected.length;i++)
{
emails.push(selected[i].id);
}

var encoded = Ext.util.JSON.encode(emails);
var gridEl = Ext.get(userGrid.getGridEl());
gridEl.mask('Sending...');

Ext.Ajax.request(
{
url: ajax_handler+"?option=sendEmails",
method: "POST",
params:
{
data: encoded
},
success: function(response)
{
gridEl.unmask();
Ext.Msg.alert("Email notification","Registration emails have been successfully sent.");
userGrid.getSelectionModel().clearSelections();
},
failure: function(response)
{
var result = Ext.util.JSON.decode(response.responseText);
gridEl.unmask();
Ext.Msg.alert('Ajax request failure',result);
}
});
}
}
}],
buttonAlign: 'center',
width: 650,
height: 300,
frame: true,
renderTo: 'registration'
});

json_store.load(
{
callback: function(r, options, success)
{
storeCounter = r.length;
}
});
});

{/literal}
</script>


Generally the XmlHttpRequest will take about 2-4 seconds, then the browser will freeze for another couple of seconds before loading. Oh, and i'm generally dealing with >2K results from the SQL DB. Any suggestions (other than using paging)?

mjlecomte
21 Jul 2008, 3:00 PM
Yeah, that's a lot of data for the browser to render. You could look at the liveGrid extension. Someone else is working on another extension but hasn't released it to my knowledge. I think he's handling 10,000 records.

karieanis
21 Jul 2008, 4:03 PM
Yeah, the liveGrid extension looks like it could be the go. Thanks for the suggestion.