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.facsimileTelephoneNum ber
,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.physicalDeliveryOffic eName
,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.facsimileTelephoneNumbe r
,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.physicalDeliveryOfficeN ame
,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.facs imileTelep honeNumber
or u.streetAddress<>ADT.stree tAddress
or u.manager<>ADT.manager
or u.whenCreated<>ADT.whenCre ated
or u.DN<>ADT.DN
or u.objectClass<>ADT.objectC lass
or u.[name]<>ADT.[name]
or u.[description]<>ADT.[desc ription]
or u.displayName<>ADT.display Name
or u.userAccountControl<>ADT. userAccoun tControl
or u.sAMAccountName<>ADT.sAMA ccountName
or u.sn<>ADT.sn
or u.givenName<>ADT.givenName
or u.memberOf<>ADT.memberOf
or u.homeMTA<>ADT.homeMTA
or u.proxyAddresses<>ADT.prox yAddresses
or u.homeMDB<>ADT.homeMDB
or u.mDBUseDefaults<>ADT.mDBU seDefaults
or u.mailNickname<>ADT.mailNi ckname
or u.mail<>ADT.mail
or u.msExchHomeServerName<>AD T.msExchHo meServerNa me
or u.physicalDeliveryOfficeNa me<>ADT.ph ysicalDeli veryOffice Name
or u.telephoneNumber<>ADT.tel ephoneNumb er
or u.department<>ADT.departme nt
or u.homeDirectory<>ADT.homeD irectory
or u.directReports<>ADT.direc tReports
or u.homeDrive<>ADT.homeDrive
or u.targetAddress<>ADT.targe tAddress
or u.c<>ADT.c
or u.l<>ADT.l
or u.st<>ADT.st
or u.postalCode<>ADT.postalCo de
or u.mAPIRecipient<>ADT.mAPIR ecipient
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
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.facsimileTelephoneNum
,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.physicalDeliveryOffic
,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.facsimileTelephoneNumbe
,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.physicalDeliveryOfficeN
,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
or u.streetAddress<>ADT.stree
or u.manager<>ADT.manager
or u.whenCreated<>ADT.whenCre
or u.DN<>ADT.DN
or u.objectClass<>ADT.objectC
or u.[name]<>ADT.[name]
or u.[description]<>ADT.[desc
or u.displayName<>ADT.display
or u.userAccountControl<>ADT.
or u.sAMAccountName<>ADT.sAMA
or u.sn<>ADT.sn
or u.givenName<>ADT.givenName
or u.memberOf<>ADT.memberOf
or u.homeMTA<>ADT.homeMTA
or u.proxyAddresses<>ADT.prox
or u.homeMDB<>ADT.homeMDB
or u.mDBUseDefaults<>ADT.mDBU
or u.mailNickname<>ADT.mailNi
or u.mail<>ADT.mail
or u.msExchHomeServerName<>AD
or u.physicalDeliveryOfficeNa
or u.telephoneNumber<>ADT.tel
or u.department<>ADT.departme
or u.homeDirectory<>ADT.homeD
or u.directReports<>ADT.direc
or u.homeDrive<>ADT.homeDrive
or u.targetAddress<>ADT.targe
or u.c<>ADT.c
or u.l<>ADT.l
or u.st<>ADT.st
or u.postalCode<>ADT.postalCo
or u.mAPIRecipient<>ADT.mAPIR
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
savvy95, were you able to solve this problem?
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,facsimileTelepho neNumber,s treetAddre ss,manager ,whenCreat ed,uSNChan ged,DN,obj ectClass
,[name],objectGUID,[descri ption],dis playName,u serAccount Control,sA MAccountNa me,sn,give nName
,memberOf,homeMTA,proxyAdd resses,hom eMDB,mDBUs eDefaults, mailNickna me,mail,ms ExchHomeSe rverName
,physicalDeliveryOfficeNam e,telephon eNumber,de partment,h omeDirecto ry,sIDHist ory,direct Reports
,homeDrive,targetAddress,c ,l,st,post alCode,mAP IRecipient ,LogType)
select
ADT.initials,ADT.facsimile TelephoneN umber,ADT. streetAddr ess,ADT.ma nager,ADT. whenCreate d
,ADT.uSNChanged,ADT.DN,ADT .objectCla ss,ADT.[na me],ADT.ob jectGUID,A DT.[descri ption]
,ADT.displayName,ADT.userA ccountCont rol,ADT.sA MAccountNa me,ADT.sn, ADT.givenN ame,ADT.me mberOf
,ADT.homeMTA,ADT.proxyAddr esses,ADT. homeMDB,AD T.mDBUseDe faults,ADT .mailNickn ame,ADT.ma il
,ADT.msExchHomeServerName, ADT.physic alDelivery OfficeName ,ADT.telep honeNumber ,ADT.depar tment
,ADT.homeDirectory,ADT.sID History,AD T.directRe ports,ADT. homeDrive, ADT.target Address,AD T.c,ADT.l
,ADT.st,ADT.postalCode,ADT .mAPIRecip ient,'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.[facsimileTelephoneNumbe r] <> ADT.[facsimileTelephoneNum ber]
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.[physicalDeliveryOfficeN ame] <> ADT.[physicalDeliveryOffic eName]
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.
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,facsimileTelepho
,[name],objectGUID,[descri
,memberOf,homeMTA,proxyAdd
,physicalDeliveryOfficeNam
,homeDrive,targetAddress,c
select
ADT.initials,ADT.facsimile
,ADT.uSNChanged,ADT.DN,ADT
,ADT.displayName,ADT.userA
,ADT.homeMTA,ADT.proxyAddr
,ADT.msExchHomeServerName,
,ADT.homeDirectory,ADT.sID
,ADT.st,ADT.postalCode,ADT
from AD_TEMP ADT
join Users u
on ADT.objectguid = u.objectguid
where U.username <> ADT.username or u.[initials] <> ADT.[initials]
or u.[facsimileTelephoneNumbe
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.[physicalDeliveryOfficeN
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.