Rewrite my working SQL Script

Aug 22nd, 2013
DotaCN
Category:
Programming
Price: $40 USD

Question description

I'm a very weak SQL programmer, but this script does work properly. Problem is that it is very slow, and a pig. Please rewrite it to produce the same result, and work much more efficiently.

/**This batch script compares every person's profile to each other person in the database.

The matches are tallied (ie # job titles, # past companies, # schools) and using weights, 

a total Score is calculated. The top matches are recorded - the individual's top match is himself**/

SELECT COUNT(ExFcode) FROM Executive

SET NOCOUNT ON;

DECLARE @Person int

DECLARE PersonCursor CURSOR

FOR SELECT  ExFcode FROM Executive ORDER BY ExFcode ASC;

OPEN PersonCursor;

FETCH NEXT FROM PersonCursor

INTO @Person;

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM PersonCursor

INTO @Person

--Get the myFields data for the person being analyzed

SELECT DISTINCT Executive.ExFcode, JobFunction.Code, Company.coFcode, 

ExecutiveJobs.IsCurrent, ExecutiveJobs.isBoardMember, ExecutiveJobs.IsPrimary,

Company.industry, Company.sic, ExecutiveEducation.CollegeName, 

Company.city, Company.state INTO #myFields

FROM Executive 

LEFT JOIN ExecutiveJobs 

ON ExecutiveJobs.ExFcode=Executive.ExFcode

LEFT JOIN ExecutiveJobFunction 

ON ExecutiveJobFunction.ExFcode=Executive.ExFcode

LEFT JOIN JobFunction 

ON JobFunction.Code=ExecutiveJobFunction.JobFunction

LEFT JOIN Company

ON Company.coFcode=ExecutiveJobs.CoFcode

LEFT JOIN ExecutiveEducation

ON ExecutiveEducation.ExFcode=Executive.ExFcode

WHERE Executive.ExFcode=@Person

--Put individual Fields into temp tables

SELECT DISTINCT Code INTO #myJobs FROM #myFields --all jobs

SELECT DISTINCT coFcode INTO #myFcode FROM #myFields

SELECT DISTINCT industry INTO #myindustry FROM #myFields

SELECT DISTINCT sic INTO #mysic FROM #myFields

SELECT DISTINCT CollegeName INTO #myCollege FROM #myFields

SELECT DISTINCT City INTO #myCity FROM #myFields

SELECT DISTINCT coFcode INTO #myBoards FROM #myFields WHERE isBoardMember=1 

--Temporary results table

CREATE TABLE #Results (ExFcode int, CurrCo int, Titles int, PastCo int, 

Indust int, sic int, College int, City int, Board int, Score int)

--Populate results (matches) for each field among myFields

--Current Company

INSERT INTO #Results (ExFcode, CurrCo)

SELECT DISTINCT ExFcode, COUNT(DISTINCT CoFcode) AS CurrCo

FROM ExecutiveJobs

WHERE ExecutiveJobs.CoFcode IN (SELECT * FROM #myFcode)

AND ExecutiveJobs.IsCurrent=1

GROUP BY ExFcode

ORDER BY ExFcode

--All job titles past and present

INSERT INTO #Results (ExFcode, Titles)

SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT JobFunction) AS Titles

FROM ExecutiveJobFunction

INNER JOIN ExecutiveJobs 

ON ExecutiveJobs.ExFcode=ExecutiveJobFunction.ExFcode

WHERE ExecutiveJobFunction.JobFunction IN (SELECT * FROM #myJobs)

--AND ExecutiveJobs.IsPrimary=1

--AND ExecutiveJobs.IsCurrent=1

GROUP BY ExecutiveJobs.ExFcode

ORDER BY ExecutiveJobs.ExFcode

--Past Companies

INSERT INTO #Results (ExFcode, PastCo)

SELECT DISTINCT ExFcode, COUNT(DISTINCT CoFcode) AS PastCo

FROM ExecutiveJobs

WHERE ExecutiveJobs.CoFcode IN (SELECT * FROM #myFcode)

AND ExecutiveJobs.IsCurrent=0

GROUP BY ExFcode

ORDER BY ExFcode

--All industries

INSERT INTO #Results (ExFcode, Indust)

SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.industry) AS Indust

FROM ExecutiveJobs

INNER JOIN Company

ON Company.CoFcode=ExecutiveJobs.CoFcode

WHERE Company.industry IN (SELECT * FROM #myindustry)

GROUP BY ExFcode

ORDER BY ExFcode

--All SubIndustries

INSERT INTO #Results (ExFcode, sic)

SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.industry) AS sic

FROM ExecutiveJobs

INNER JOIN Company

ON Company.CoFcode=ExecutiveJobs.CoFcode

WHERE Company.sic IN (SELECT * FROM #mysic)

GROUP BY ExFcode

ORDER BY ExFcode

--Everyone from All Colleges attended by person

INSERT INTO #Results (ExFcode, College)

SELECT DISTINCT ExecutiveEducation.ExFcode, COUNT(DISTINCT ExecutiveEducation.CollegeName) AS College

FROM ExecutiveEducation

WHERE ExecutiveEducation.CollegeName IN (SELECT * FROM #myCollege)

GROUP BY ExFcode

ORDER BY ExFcode

--Everyone in the City of primary company of person

INSERT INTO #Results (ExFcode, City)

SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT Company.city) AS city

FROM ExecutiveJobs

INNER JOIN Company

ON Company.CoFcode=ExecutiveJobs.CoFcode

WHERE Company.city IN (SELECT * FROM #myCity)

GROUP BY ExFcode

ORDER BY ExFcode

--Board Members

INSERT INTO #Results (ExFcode, Board)

SELECT DISTINCT ExecutiveJobs.ExFcode, COUNT(DISTINCT CoFcode) as Board

FROM ExecutiveJobs

WHERE CoFcode IN (SELECT * FROM #myBoards)

GROUP BY ExFcode

ORDER BY ExFcode

--Consolidate the results into permanent Connections Table, Create Score

INSERT INTO Connections

SELECT TOP 20 ExFcode As Connections, @Person As Executive, Sum(CurrCo) As SameCompany, --The TOP N connections only

Sum(Titles) AS SameTitle, 

SUM(Indust) As SameIndustry, SUM(sic) As SameSubInd, 

SUM(College) AS SameCollege, SUM(City) AS SameCity, SUM(Board) As SameBoard, 

--HERE IS THE SCORE WEIGHT (and Limit for Title matches)

(5*Sum(isnull(CurrCo,0)) + 

SUM(isnull(Indust,0)) + 

2*SUM(isnull(sic,0)) + 

3*SUM(isnull(College,0)) + 

2*SUM(isnull(City,0)) + 

10*SUM(isnull(Board,0)) +

Case 

WHEN SUM(Titles)>3

THEN 3

ELSE Sum(isnull(Titles,0))

END)

As Score, GETDATE() As Date

--INTO Connections

FROM #Results

GROUP BY ExFcode

ORDER BY Score DESC

--Clean Up temp tables

DROP TABLE #myFields

DROP TABLE #myJobs

DROP TABLE #myFcode

DROP TABLE #myindustry

DROP TABLE #mysic

DROP TABLE #myCollege

DROP TABLE #myCity

DROP TABLE #myBoards

DROP TABLE #Results 

--DROP TABLE Connections;

END --FETCH_STATUS LOOP

CLOSE PersonCursor;

DEALLOCATE PersonCursor


Tutor Answer

(Top Tutor) Daniel C.
(997)
School: Rice University
PREMIUM TUTOR

Studypool has helped 1,244,100 students

8 Reviews


Summary
Quality
Communication
On Time
Value
kevin12622
Dec 4th, 2016
" Goes above and beyond expectations ! "
kiln82
Nov 25th, 2016
" awesome work thanks "
ashleyisgod
Nov 21st, 2016
" Top quality work from this guy! I'll be back! "
likeplum4
Nov 12th, 2016
" Excellent work as usual "
Molly_Moon
Oct 29th, 2016
" AMAZING as always! "
kpcutie
Oct 20th, 2016
" Excellent job "
Hemapathy
Oct 7th, 2016
" all I can say is wow very fast work, great work thanks "
pmallory
Sep 28th, 2016
" Totally impressed with results!! :-) "
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1823 tutors are online

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors