Using data from another application
Posted by Jeff Rix on September 13, 2011 under T-SQL | 
Be the First to Comment
Sometimes data that is necessary for the application is being stored in another database on another server. Executing a remote query, using functions, and joining to many table slows down performance.
My solution to this challenge was to execute a stored procedure nightly that inserts data from another data source (student data) as well as summarized data for each student. The stored procedure spi_LoadStudentSearch brings over some basic student data and uses local scalar-valued functions to get summarized data from the application.
When bringing in data from a different server/database insert the data into a temp table.
– Add the parameters for the stored procedure here
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;–temp table for data in another server
IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL
DROP TABLE #ResultsCREATE TABLE #Results
(Student_ID INT primary key,
GRADE CHAR(2),
BUILDING INT,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30))INSERT INTO #Results
SELECT CAST(STUDENT_ID AS INT) AS STUDENT_ID, GRADE, BUILDING, FIRST_NAME, LAST_NAME
FROM LinkedServer.Yes_Live.dbo.reg
WHERE CURRENT_STATUS =‘A’
–truncate student_search
truncate table Student_Search
DECLARE @CURRENTYEAR INT
SET @CURRENTYEAR = (SELECT top 1 schoolDist.SCHOOL_YEAR
WHERE schoolDist.DISTRICT = 101845)
insert into Student_Search
select distinct x.student_id, x.grade,x.building, x.first_name, x.last_name, ISNULL(y.Respect_Marks_Count, 0) as Respect_Marks_Count,
ISNULL(y.Responsibility_Marks_Count,0) AS Responsibility_Marks_Count,
ISNULL(y.Respect_Level, 0) AS Respect_Level, ISNULL(y.Responsiblity_Level,0) AS Responsibility_Level
from #Results as x left join
(
select a.student_id, dbo.RespectMarksCount(a.STUDENT_ID) as Respect_Marks_Count,
dbo.ResponsibilityMarksCount(a.STUDENT_ID) as Responsibility_Marks_Count,
dbo.RespectLevel(a.STUDENT_ID) as Respect_Level, dbo.ResponsibilityLevel(a.STUDENT_ID) as Responsiblity_Level
from RISE_MARKS as a
where a.SCHOOL_YEAR = @CURRENTYEAR
–@CURRENTYEAR
) as y
on x.student_id = y.STUDENT_ID
END
This stored procedure executes each night and is scheduled using the SQL Server Agent.
