Link to home
Start Free TrialLog in
Avatar of savvy95
savvy95

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rafrancisco
rafrancisco

savvy95, were you able to solve this problem?
Avatar of savvy95

ASKER

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.