• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

INSERT takes too long

The Plan:
To compare 2 tables that have the same structure and when the table which is updated daily has different information than the other table, to insert the revised record and move the old record to the AuditLog:

The Syntax:
insert into users
            (
            InsertDate
            ,ADT.Username
            ,ADT.initials
            ,ADT.facsimileTelephoneNumber
            ,ADT.streetAddress
            ,ADT.manager
            ,ADT.whenCreated
            ,ADT.uSNChanged
            ,ADT.DN
            ,ADT.objectClass
            ,ADT.[name]
            ,ADT.objectGUID
            ,ADT.[description]
            ,ADT.displayName
            ,ADT.userAccountControl
            ,ADT.sAMAccountName
            ,ADT.sn
            ,ADT.givenName
            ,ADT.memberOf
            ,ADT.homeMTA
            ,ADT.proxyAddresses
            ,ADT.homeMDB
            ,ADT.mDBUseDefaults
            ,ADT.mailNickname
            ,ADT.mail
            ,ADT.msExchHomeServerName
            ,ADT.physicalDeliveryOfficeName
            ,ADT.telephoneNumber
            ,ADT.department
            ,ADT.homeDirectory
            ,ADT.sIDHistory
            ,ADT.directReports
            ,ADT.homeDrive
            ,ADT.targetAddress
            ,ADT.c
            ,ADT.l
            ,ADT.st
            ,ADT.postalCode
            ,ADT.mAPIRecipient
            )
      select
            U.InsertDate
            ,U.Username
            ,U.initials
            ,U.facsimileTelephoneNumber
            ,U.streetAddress
            ,U.manager
            ,U.whenCreated
            ,U.uSNChanged
            ,U.DN
            ,U.objectClass
            ,U.[name]
            ,U.objectGUID
            ,U.[description]
            ,U.displayName
            ,U.userAccountControl
            ,U.sAMAccountName
            ,U.sn
            ,U.givenName
            ,U.memberOf
            ,U.homeMTA
            ,U.proxyAddresses
            ,U.homeMDB
            ,U.mDBUseDefaults
            ,U.mailNickname
            ,U.mail
            ,U.msExchHomeServerName
            ,U.physicalDeliveryOfficeName
            ,U.telephoneNumber
            ,U.department
            ,U.homeDirectory
            ,U.sIDHistory
            ,U.directReports
            ,U.homeDrive
            ,U.targetAddress
            ,U.c
            ,U.l
            ,U.st
            ,U.postalCode
            ,U.mAPIRecipient
      from AD_temp ADT
      join users U
      on u.objectGUID = ADT.objectGUID
      where       u.initials<>ADT.initials
            or u.facsimileTelephoneNumber<>ADT.facsimileTelephoneNumber
            or u.streetAddress<>ADT.streetAddress
            or u.manager<>ADT.manager
            or u.whenCreated<>ADT.whenCreated
            or u.DN<>ADT.DN
            or u.objectClass<>ADT.objectClass
            or u.[name]<>ADT.[name]
            or u.[description]<>ADT.[description]
            or u.displayName<>ADT.displayName
            or u.userAccountControl<>ADT.userAccountControl
            or u.sAMAccountName<>ADT.sAMAccountName
            or u.sn<>ADT.sn
            or u.givenName<>ADT.givenName
            or u.memberOf<>ADT.memberOf
            or u.homeMTA<>ADT.homeMTA
            or u.proxyAddresses<>ADT.proxyAddresses
            or u.homeMDB<>ADT.homeMDB
            or u.mDBUseDefaults<>ADT.mDBUseDefaults
            or u.mailNickname<>ADT.mailNickname
            or u.mail<>ADT.mail
            or u.msExchHomeServerName<>ADT.msExchHomeServerName
            or u.physicalDeliveryOfficeName<>ADT.physicalDeliveryOfficeName
            or u.telephoneNumber<>ADT.telephoneNumber
            or u.department<>ADT.department
            or u.homeDirectory<>ADT.homeDirectory
            or u.directReports<>ADT.directReports
            or u.homeDrive<>ADT.homeDrive
            or u.targetAddress<>ADT.targetAddress
            or u.c<>ADT.c
            or u.l<>ADT.l
            or u.st<>ADT.st
            or u.postalCode<>ADT.postalCode
            or u.mAPIRecipient<>ADT.mAPIRecipient
            and ADT.objectclass in ('user', 'contact')

The Delete:

delete from users where initials not in (select initials from users where initials in (select initials from AD_Temp))
delete from users where facsimileTelephoneNumber not in (select facsimileTelephoneNumber from users where facsimileTelephoneNumber in (select facsimileTelephoneNumber from AD_Temp))
delete from users where streetAddress not in (select streetAddress from users where streetAddress in (select streetAddress from AD_Temp))
delete from users where manager not in (select manager from users where manager in (select manager from AD_Temp))
delete from users where whenCreated not in (select whenCreated from users where whenCreated in (select whenCreated from AD_Temp))
delete from users where DN not in (select DN from users where DN in (select DN from AD_Temp))
delete from users where objectClass not in (select objectClass from users where objectClass in (select objectClass from AD_Temp))
delete from users where [name] not in (select [name] from users where [name] in (select [name] from AD_Temp))
delete from users where objectGUID not in (select objectGUID from users where objectGUID in (select objectGUID from AD_Temp))
delete from users where [description] not in (select [description] from users where [description] in (select [description] from AD_Temp))
delete from users where displayName not in (select displayName from users where displayName in (select displayName from AD_Temp))
delete from users where userAccountControl not in (select userAccountControl from users where userAccountControl in (select userAccountControl from AD_Temp))
delete from users where sAMAccountName not in (select sAMAccountName from users where sAMAccountName in (select sAMAccountName from AD_Temp))
delete from users where sn not in (select sn from users where sn in (select sn from AD_Temp))
delete from users where givenName not in (select givenName from users where givenName in (select givenName from AD_Temp))
delete from users where memberOf not in (select memberOf from users where memberOf in (select memberOf from AD_Temp))
delete from users where homeMTA not in (select homeMTA from users where homeMTA in (select homeMTA from AD_Temp))
delete from users where proxyAddresses not in (select proxyAddresses from users where proxyAddresses in (select proxyAddresses from AD_Temp))
delete from users where homeMDB not in (select homeMDB from users where homeMDB in (select homeMDB from AD_Temp))
delete from users where mDBUseDefaults not in (select mDBUseDefaults from users where mDBUseDefaults in (select mDBUseDefaults from AD_Temp))
delete from users where mailNickname not in (select mailNickname from users where mailNickname in (select mailNickname from AD_Temp))
delete from users where mail not in (select mail from users where mail in (select mail from AD_Temp))
delete from users where msExchHomeServerName not in (select msExchHomeServerName from users where msExchHomeServerName in (select msExchHomeServerName from AD_Temp))
delete from users where physicalDeliveryOfficeName not in (select physicalDeliveryOfficeName from users where physicalDeliveryOfficeName in (select physicalDeliveryOfficeName from AD_Temp))
delete from users where telephoneNumber not in (select telephoneNumber from users where telephoneNumber in (select telephoneNumber from AD_Temp))
delete from users where department not in (select department from users where department in (select department from AD_Temp))
delete from users where homeDirectory not in (select homeDirectory from users where homeDirectory in (select homeDirectory from AD_Temp))
delete from users where directReports not in (select directReports from users where directReports in (select directReports from AD_Temp))
delete from users where homeDrive not in (select homeDrive from users where homeDrive in (select homeDrive from AD_Temp))
delete from users where targetAddress not in (select targetAddress from users where targetAddress in (select targetAddress from AD_Temp))
delete from users where c not in (select c from users where c in (select c from AD_Temp))
delete from users where l not in (select l from users where l in (select l from AD_Temp))
delete from users where st not in (select st from users where st in (select st from AD_Temp))
delete from users where postalCode not in (select postalCode from users where postalCode in (select postalCode from AD_Temp))
delete from users where mAPIRecipient not in (select mAPIRecipient from users where mAPIRecipient in (select mAPIRecipient from AD_Temp))


Please don't laugh too hard at how inefficient my code is. That's why I turn to you for help.

I have 4 tables and the DTS works but takes 20 minutes to run.  I would like to cut the time by half.  There are approximately 5000  - 6000 records in AD_Temp and approx 2000 in USERS

Thank you in advance

0
savvy95
Asked:
savvy95
  • 2
1 Solution
 
rafranciscoCommented:
Create an index on ADTTemp (objectUID, objectClass) and Users(objectUID) then try this one and see if this is faster.  If this is already acceptable, we will deal with the delete later.

insert into users
          (
          InsertDate
          ,ADT.Username
          ,ADT.initials
          ,ADT.facsimileTelephoneNumber
          ,ADT.streetAddress
          ,ADT.manager
          ,ADT.whenCreated
          ,ADT.uSNChanged
          ,ADT.DN
          ,ADT.objectClass
          ,ADT.[name]
          ,ADT.objectGUID
          ,ADT.[description]
          ,ADT.displayName
          ,ADT.userAccountControl
          ,ADT.sAMAccountName
          ,ADT.sn
          ,ADT.givenName
          ,ADT.memberOf
          ,ADT.homeMTA
          ,ADT.proxyAddresses
          ,ADT.homeMDB
          ,ADT.mDBUseDefaults
          ,ADT.mailNickname
          ,ADT.mail
          ,ADT.msExchHomeServerName
          ,ADT.physicalDeliveryOfficeName
          ,ADT.telephoneNumber
          ,ADT.department
          ,ADT.homeDirectory
          ,ADT.sIDHistory
          ,ADT.directReports
          ,ADT.homeDrive
          ,ADT.targetAddress
          ,ADT.c
          ,ADT.l
          ,ADT.st
          ,ADT.postalCode
          ,ADT.mAPIRecipient
          )
     select
          U.InsertDate
          ,U.Username
          ,U.initials
          ,U.facsimileTelephoneNumber
          ,U.streetAddress
          ,U.manager
          ,U.whenCreated
          ,U.uSNChanged
          ,U.DN
          ,U.objectClass
          ,U.[name]
          ,U.objectGUID
          ,U.[description]
          ,U.displayName
          ,U.userAccountControl
          ,U.sAMAccountName
          ,U.sn
          ,U.givenName
          ,U.memberOf
          ,U.homeMTA
          ,U.proxyAddresses
          ,U.homeMDB
          ,U.mDBUseDefaults
          ,U.mailNickname
          ,U.mail
          ,U.msExchHomeServerName
          ,U.physicalDeliveryOfficeName
          ,U.telephoneNumber
          ,U.department
          ,U.homeDirectory
          ,U.sIDHistory
          ,U.directReports
          ,U.homeDrive
          ,U.targetAddress
          ,U.c
          ,U.l
          ,U.st
          ,U.postalCode
          ,U.mAPIRecipient
     from AD_temp ADT
     join users U
     on u.objectGUID = ADT.objectGUID           and ADT.objectclass in ('user', 'contact')
     where      u.initials<>ADT.initials
          or u.facsimileTelephoneNumber<>ADT.facsimileTelephoneNumber
          or u.streetAddress<>ADT.streetAddress
          or u.manager<>ADT.manager
          or u.whenCreated<>ADT.whenCreated
          or u.DN<>ADT.DN
          or u.objectClass<>ADT.objectClass
          or u.[name]<>ADT.[name]
          or u.[description]<>ADT.[description]
          or u.displayName<>ADT.displayName
          or u.userAccountControl<>ADT.userAccountControl
          or u.sAMAccountName<>ADT.sAMAccountName
          or u.sn<>ADT.sn
          or u.givenName<>ADT.givenName
          or u.memberOf<>ADT.memberOf
          or u.homeMTA<>ADT.homeMTA
          or u.proxyAddresses<>ADT.proxyAddresses
          or u.homeMDB<>ADT.homeMDB
          or u.mDBUseDefaults<>ADT.mDBUseDefaults
          or u.mailNickname<>ADT.mailNickname
          or u.mail<>ADT.mail
          or u.msExchHomeServerName<>ADT.msExchHomeServerName
          or u.physicalDeliveryOfficeName<>ADT.physicalDeliveryOfficeName
          or u.telephoneNumber<>ADT.telephoneNumber
          or u.department<>ADT.department
          or u.homeDirectory<>ADT.homeDirectory
          or u.directReports<>ADT.directReports
          or u.homeDrive<>ADT.homeDrive
          or u.targetAddress<>ADT.targetAddress
          or u.c<>ADT.c
          or u.l<>ADT.l
          or u.st<>ADT.st
          or u.postalCode<>ADT.postalCode
          or u.mAPIRecipient<>ADT.mAPIRecipient
0
 
rafranciscoCommented:
savvy95, were you able to solve this problem?
0
 
savvy95Author Commented:
My solution is as follows:
Insert records into a temp table (DELUSERS)
Insert DELUSERS into  AUDITLOG table with LOGTYPE = 'D'
Insert new users into USERS
Insert updated users into AUDITLOG with LOGTYPE = 'U'
 with the following code:

--INSERT UPDATED RECORDS INTO AUDITLOG

--USERS
insert into AuditLog
      (initials,facsimileTelephoneNumber,streetAddress,manager,whenCreated,uSNChanged,DN,objectClass
      ,[name],objectGUID,[description],displayName,userAccountControl,sAMAccountName,sn,givenName
      ,memberOf,homeMTA,proxyAddresses,homeMDB,mDBUseDefaults,mailNickname,mail,msExchHomeServerName
      ,physicalDeliveryOfficeName,telephoneNumber,department,homeDirectory,sIDHistory,directReports
      ,homeDrive,targetAddress,c,l,st,postalCode,mAPIRecipient,LogType)
      select
      ADT.initials,ADT.facsimileTelephoneNumber,ADT.streetAddress,ADT.manager,ADT.whenCreated
      ,ADT.uSNChanged,ADT.DN,ADT.objectClass,ADT.[name],ADT.objectGUID,ADT.[description]
      ,ADT.displayName,ADT.userAccountControl,ADT.sAMAccountName,ADT.sn,ADT.givenName,ADT.memberOf
      ,ADT.homeMTA,ADT.proxyAddresses,ADT.homeMDB,ADT.mDBUseDefaults,ADT.mailNickname,ADT.mail
      ,ADT.msExchHomeServerName,ADT.physicalDeliveryOfficeName,ADT.telephoneNumber,ADT.department
      ,ADT.homeDirectory,ADT.sIDHistory,ADT.directReports,ADT.homeDrive,ADT.targetAddress,ADT.c,ADT.l
      ,ADT.st,ADT.postalCode,ADT.mAPIRecipient,'U'
      from AD_TEMP ADT
            join Users u
            on ADT.objectguid = u.objectguid
            where U.username <> ADT.username or u.[initials] <> ADT.[initials]
            or u.[facsimileTelephoneNumber] <> ADT.[facsimileTelephoneNumber]
            or u.[streetAddress] <> ADT.[streetAddress] or u.[manager] <> ADT.[manager]
            or u.[whenCreated] <> ADT.[whenCreated] or u.[DN] <> ADT.[DN]
            or u.[objectClass] <> ADT.[objectClass] or u.[name] <> ADT.[name]
            or u.[description] <> ADT.[description] or u.[displayName] <> ADT.[displayName]
            or u.[userAccountControl] <> ADT.[userAccountControl]
            or u.[sAMAccountName] <> ADT.[sAMAccountName] or u.[sn] <> ADT.[sn]
            or u.[givenName] <> ADT.[givenName] or u.[memberOf] <> ADT.[memberOf]
            or u.[homeMTA] <> ADT.[homeMTA] or u.[proxyAddresses] <> ADT.[proxyAddresses]
            or u.[homeMDB] <> ADT.[homeMDB] or u.[mDBUseDefaults] <> ADT.[mDBUseDefaults]
            or u.[mailNickname] <> ADT.[mailNickname] or u.[mail] <> ADT.[mail]
            or u.[msExchHomeServerName] <> ADT.[msExchHomeServerName]
            or u.[physicalDeliveryOfficeName] <> ADT.[physicalDeliveryOfficeName]
            or u.[telephoneNumber] <> ADT.[telephoneNumber] or u.[department] <> ADT.[department]
            or u.[homeDirectory] <> ADT.[homeDirectory] or u.[directReports] <> ADT.[directReports]
            or u.[homeDrive] <> ADT.[homeDrive] or u.[targetAddress] <> ADT.[targetAddress]
            or u.[c] <> ADT.[c] or u.[l] <> ADT.[l] or u.[st] <> ADT.[st]
            or u.[postalCode] <> ADT.[postalCode] or u.[mAPIRecipient] <> ADT.[mAPIRecipient]

It seems to work and since nobody's around at 0600 each morning nobody complains about the time it takes.

Was it you rafrancisco that had another way of comparing tables using numbers?  or it might have been someone else.

Anyway there's my code.
I'll close the  questions and give you the points.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now