Speed up SQL import

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 11 months ago.

  • Author
    Posts
  • #5459

    by rj_connor at 2013-01-22 07:25:32

    Hi

    Not sure if this is the best place for this as it covers SQL and AD, so if incorrect please let me know.

    I have the below script that runs to export AD information in to a SQL database, we have 3 Domains EMEA, AMERS and APAC and the script is duplicated 3 time one under each other pointing to a different DC in each domain.

    The problem I’m having is that it takes over 3 hrs to pull the data in to SQL, can I tweak the script so that i can reduce that time.

    Script..

    if ( (Get-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue) -eq $null )
    {
    Add-PsSnapin Quest.ActiveRoles.ADManagement
    }

    $ldapQuery ="(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=*))"

    #Extract and Import for EMEA domain

    $oSqlConnection = New-Object System.Data.SqlClient.SqlConnection ("Data Source=LONSWEBBOX\SQLEXPRESS;Initial Catalog=IME_Decom_Reporting;Integrated Security=SSPI");
    $oSqlCmd = New-Object System.Data.SqlClient.SqlCommand ("sp_Import_EMEA_MBX_Stats",$oSqlConnection);
    $oSqlCmd.CommandType = [System.Data.CommandType] "StoredProcedure"
    $oSqlCmd.Parameters.Add("@EA13",[System.Data.SqlDbType]"VarChar", 1024)
    $oSqlCmd.Parameters.Add("@EA6",[System.Data.SqlDbType]"VarChar", 1024)
    $oSqlCmd.Parameters.Add("@EA5",[System.Data.SqlDbType]"VarChar", 1024)
    $oSqlCmd.Parameters.Add("@EA4",[System.Data.SqlDbType]"VarChar", 1024)
    $oSqlCmd.Parameters.Add("@hidden",[System.Data.SqlDbType]"VarChar", 256)
    $oSqlCmd.Parameters.Add("@Disabled",[System.Data.SqlDbType]"VarChar", 256)
    $oSqlCmd.Parameters.Add("@Email",[System.Data.SqlDbType]"VarChar", 256)
    $oSqlCmd.Parameters.Add("@TA",[System.Data.SqlDbType]"VarChar", 256)
    $oSqlCmd.Parameters.Add("@Sam",[System.Data.SqlDbType]"VarChar", 256)
    $oSqlCmd.Parameters.Add("@homeMDB",[System.Data.SqlDbType]"VarChar", 1024)
    $oSqlCmd.Parameters.Add("@LEDN",[System.Data.SqlDbType]"VarChar", 1024)

    Get-QADUser -Service "londemea1.emea.com" -SizeLimit 75000 -LdapFilter $ldapQuery -IncludeAllProperties | %{

    $oSqlCmd.Parameters["@EA13"].Value = $_.extensionAttribute13
    $oSqlCmd.Parameters["@EA6"].Value = $_.extensionAttribute6
    $oSqlCmd.Parameters["@EA5"].Value = $_.extensionAttribute5
    $oSqlCmd.Parameters["@EA4"].Value = $_.extensionAttribute4
    $oSqlCmd.Parameters["@hidden"].Value = $_.msExchHideFromAddressLists
    $oSqlCmd.Parameters["@Disabled"].Value = $_.AccountIsDisabled
    $oSqlCmd.Parameters["@Email"].Value = $_.Email
    $oSqlCmd.Parameters["@TA"].Value = $_.targetaddress
    $oSqlCmd.Parameters["@Sam"].Value = $_.SamAccountName
    $oSqlCmd.Parameters["@homeMDB"].Value = $_.homeMDB
    $oSqlCmd.Parameters["@LEDN"].Value = $_.legacyExchangeDN
    $oSqlConnection.Open()
    $oSqlCmd.ExecuteNonQuery()
    $oSqlConnection.Close()

    }

    by DonJ at 2013-01-22 13:40:56

    As a note, you can use the CODE button in the toolbar to format your code.

    Can you add some logging (e.g., Write-Host (Get-Date)) at various points? It would help to see what commands are taking all the time. I suspect it may just be the number of accounts you're querying; firing off 75,000 SQL queries can take time, depending on the SQL Server's abilities. At 1 second each, 75,000 users is over 20 hours. At three hours you're getting about 10 queries/second, which ain't bad.

You must be logged in to reply to this topic.