This is a little script I made as a sort of performance test for CRM. Basically, how it works is it queries 1000 Contact records from CRM and tracks the time it takes to loop through them and make an update to each one. It's designed to update a single field (in this case, 'fax') with a dummy value, then change it back. So, to not overwrite valid data it queries only records where the field to be updated is null, makes the update, then it changes it back to null.
Furthermore, it writes the time it takes to make all the updates to a csv file of your choosing and then restarts itself to run after a certain amount of time (currently 1 hour). Over time it creates a log of these durations which you can build charts and reports off of.
One thing to point out is that the list of 1000 contact records is queried through the SQL database, so as written this script only works for on premise deployments. In order to make it work for CRM Online instances you'll need to change the SQL query to a CRM query call to the API. A few of my other posts should show you how to accomplish this.
Please remember, this is a performance test. Speedy, efficient code is not the goal here, but rather understanding the time it takes to run. You may notice some unnecessary steps being performed. I'm just trying to hit as many areas of the system as possible to get as much of a complete read as possible.
This script might be useful to measure differences in speed throughout the day, or to compare the system before and after large changes. In my own case, I built this script to get some kind of measurement of the system prior to us enabling CRM Auditing. There were concerns that this feature would slow down CRM. And for those interested, there was no noticeable difference shown to us.
I hope this is useful to someone out there.
#Add SDK references
Add-Type -Path "[mySDKPath]\Bin\Microsoft.Xrm.Sdk.dll";
Add-Type -Path "[mySDKPath]\Bin\Microsoft.Xrm.Client.dll";
Add-Type -Path "[mySDKPath]\Bin\Microsoft.Crm.Sdk.Proxy.dll";
#CRM Connection
$CRMUrl = "https://[myCRMPath]";
$CRMLogin = "[myCRMAccount]";
$CRMPwd = Read-Host "Enter password for account $CRMLogin"
$CRMConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$CRMUrl; Username=$CRMLogin; Password=$CRMPwd");
$CRMService = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $CRMConnection;
#SQL Connections
$sqlUser = "[mySQLAccount]"
$sqlPwd = Read-Host "Enter password for SQL account $sqlUser" -AsSecureString
#Source datasource
$sourceDatabaseServer = "[mySQLDatabaseServer]"
$sourceDatabase = "[mySQLDatabase]"
$sourceConnectionString = "Server=$sourceDatabaseServer;uid=$sqlUser; pwd=$sqlPwd;Database=$sourceDatabase;Integrated Security=true;"
$sourceConnection = New-Object System.Data.SqlClient.SqlConnection
$sourceConnection.ConnectionString = $sourceConnectionString
function PerformanceTest{
#SQL Query
#Source Database Query
$top = "1000"
$sourceConnection.Open()
$sourceQuery = “SELECT TOP $top contactid, fax
FROM [mySQLTable]
WHERE fax is null”
$sourceCommand = $sourceConnection.CreateCommand()
$sourceCommand.CommandText = $sourceQuery
$sourceResult = $sourceCommand.ExecuteReader()
$Global:sourceTable = New-Object “System.Data.DataTable”
$sourceTable.Load($sourceResult)
$sourceConnection.Close()
#Looping Through Records
$count = 0
$startTime = Get-Date
foreach($record in $sourceTable){
$count ++;
#Query Record
$CRMQuery = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "contact";
$CRMQuery.ColumnSet.AddColumn("firstname");
$CRMQuery.ColumnSet.AddColumn("lastname");
$CRMQuery.ColumnSet.AddColumn("telephone1");
$CRMQuery.ColumnSet.AddColumn("telephone2");
$CRMQuery.ColumnSet.AddColumn("gendercode");
$CRMQuery.ColumnSet.AddColumn("address1_country");
$CRMQuery.ColumnSet.AddColumn("ownerid");
$CRMQuery.ColumnSet.AddColumn("address2_country");
$CRMQuery.ColumnSet.AddColumn("address1_line1");
$CRMQuery.ColumnSet.AddColumn("address2_line1");
$CRMQuery.ColumnSet.AddColumn("emailaddress1");
$CRMQuery.ColumnSet.AddColumn("emailaddress2");
$CRMQuery.Criteria.AddCondition("contactid", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, $record.contactid.Guid);
$CRMQuery.Criteria.AddCondition("fax", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Null);
$CRMQueryResults = $CRMService.RetrieveMultiple($CRMQuery);
$CRMRecords = $CRMQueryResults.Entities
#Create Entity Object and identify
$targetRecord = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "contact";
$targetRecord.Id = $CRMRecords[0].Id.Guid;
$targetRecord["fax"] = "test data";
$CRMService.Update($targetRecord);
$targetRecord["fax"] = $null;
$CRMService.Update($targetRecord);
Write-Host "Record"$record.contactid": $count/$top"
}
$endTime = Get-Date
Write-Host "`n`nStarted: "$startTime
Write-Host "Ended: "$endTime
$duration = New-TimeSpan $startTime $endTime
Write-Host "`nDuration: $duration"
Add-Content -Path "[myPath]\PerformanceTest.csv" "$startTime,$duration,$top,PROD" -Force
Write-Host "Timing out for 1 hours"
Start-Sleep -Seconds 3600
PerformanceTest
}
PerformanceTest
