How to Optimize T-SQL 1

[aalmeida]

I need to replace the following statment with one that would run faster, the @sTableName is a table that receives the data from a BCP in process from a text file.

Select @sql = "INSERT INTO #CustUpdate (CustID, CampID, Office, LastName, FirstName, MiddleInit, Address1, Address2, SSN, City, State, HomePhone, Zip, ResidCD, ResidDate, EmployerName, JobTitle, EmploymentDate, WorkPhone, OtherPhone, BTTC, BTTCCode, EmailAddress, CustType, ChangeDateTime)"+
" SELECT CustID, CampaignID, Office, LastName, FirstName, MiddleInitial, AddressLine1, AddressLine2, SSN, City, State, HomePhone, Zip, ResidCD, ResidDate, EmployerName, JobTitle, EmploymentDate, WorkPhone, OtherPhone, BTTC, BTTCCode, EmailAddress, CustType, ChangeDateTime"+
" FROM "+ @sTableName +
" WHERE (CustID + CampaignID + Office) NOT IN (SELECT (CustID + CampaignID + Office) FROM Customer)"
EXEC(@sql)
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[fluteplr]

Well for starters do you have an index on

CustID + CampaignID + Office

In customer?

 

[tlbroadbent]

Replace the SELECT statement with the following. Using LEFT JOIN will be much faster than using NOT IN.

" SELECT
t.CustID, t.CampaignID, t.Office,
t.LastName, t.FirstName,
t.MiddleInitial, t.AddressLine1,
t.AddressLine2, t.SSN,
t.City, t.State, t.HomePhone,
t.Zip, t.ResidCD, t.ResidDate,
t.EmployerName, t.JobTitle,
t.EmploymentDate, t.WorkPhone,
t.OtherPhone, t.BTTC,
t.BTTCCode, t.EmailAddress,
t.CustType, t.ChangeDateTime" +

" FROM "+ @sTableName + " As t"
" LEFT JOIN Customer As c"
" ON t.CustID=c.CustID AND t.CampaignID=c.CampaignID AND t.Office=c.Office"
" WHERE c.CustID IS NULL" Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[aalmeida]

Fluteplr! I do have indexes on table customer.
Terry! I'll give it a try and let you know.
Thanks
Al AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[aalmeida]

Thanks terry it seems it works AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[aalmeida]

Terry ohho! I got a violation of Primary Key, From the Temp table I have to insert into the main Customer Table, the reason is that it may that are duplicate recors on my source file, if that is true I want the last dated one to insert to the customer table, with that in mind I tryed a MAX date and group by but still the same violation of PK, any sugestion?
"INSERT INTO #CustUpdate (CustID, CampID, Office, LastName, FirstName, MiddleInit, Address1, Address2, SSN, City, State, HomePhone, Zip, ResidCD, ResidDate, EmployerName, JobTitle, EmploymentDate, WorkPhone, OtherPhone, BTTC, BTTCCode, EmailAddress, CustType, ChangeDateTime)"+
"SELECT t.CustID, t.CampaignID, t.Office,"+
" t.LastName, t.FirstName, t.MiddleInitial, t.AddressLine1,"+
" t.AddressLine2, t.SSN, t.City, t.State, t.HomePhone,"+
" t.Zip, t.ResidCD, t.ResidDate, t.EmployerName, t.JobTitle,"+
" t.EmploymentDate, t.WorkPhone, t.OtherPhone, t.BTTC,"+
" t.BTTCCode, t.EmailAddress, t.CustType, MAX(t.ChangeDateTime)"+
" FROM "+ @sTableName + " t " + " LEFT JOIN Customer c ON "+
" t.CustID=c.CustID AND"+
" t.CampaignID=c.CampaignID AND"+
" t.Office=c.Office"+
" WHERE c.CustID IS NULL"+
" GROUP BY t.CustID, t.CampaignID, t.Office,"+
" t.LastName, t.FirstName, t.MiddleInitial, t.AddressLine1,"+
" t.AddressLine2, t.SSN, t.City, t.State, t.HomePhone,"+
" t.Zip, t.ResidCD, t.ResidDate, t.EmployerName, t.JobTitle,"+
" t.EmploymentDate, t.WorkPhone, t.OtherPhone, t.BTTC,"+
" t.BTTCCode, t.EmailAddress, t.CustType, t.ChangeDateTime" AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"