This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
asked
Tracking a scenario - Best practise method - OpenQuery and Index placement
The following query is part of a stored procedure that I currently have on a server instance B of SQL Server 2008. My data resides on Server instance A of SQL Server 2008 and B connects to A via a linked server called DataServer. -- beginning of a stored procedure CREATE TABLE #MyTempTable ( AccountID INT NOT NULL, AccountRate NUMERIC(14,4) NOT NULL, EffectiveDate DATE NOT NULL ) -- create indexes CREATE CLUSTERED INDEX [CIX_AccountID] ON #MyTempTable ( AccountID ) CREATE NONCLUSTERED INDEX [NCIX_EffectiveDate] ON #MyTempTable ( EffectiveDate, AccountRate ) -- insert data using OPENQUERY Declare @CmdString1 varchar(2000) Declare @CmdString2 varchar(2000) Set @CmdString1 = 'EXECUTE [MyDatabase].dbo.spLINK_CalcProc1 ' Set @CmdString1 = @CmdString1 + convert(varchar(15), @AccountID) + ', ' Set @CmdString1 = @CmdString1 + convert(varchar(15), @AccountRate) + ', ' Set @CmdString1 = @CmdString1 + convert(varchar(15), @EffectiveDate) -- variable parameters from PROC Set @CmdString2 = 'INSERT INTO #MyTempTable ( AccountID, AccountRate, EffectiveDate ) SELECT * FROM OPENQUERY (DataServer, ''' + @CmdString1 + ''')' EXECUTE (@CmdString2) -- further logic continues using the above created temp table I modfied it in a way that eliminates OPENQUERY and also adjusts Index placement to as follows- -- beginning of a stored procedure CREATE TABLE #MyTempTable ( AccountID INT NOT NULL, AccountRate NUMERIC(14,4) NOT NULL, EffectiveDate DATE NOT NULL ) -- Insert data INSERT INTO #MyTempTable ( AccountID, AccountRate, EffectiveDate ) EXEC [DataServer].[MyDatabase].dbo.spLINK_CalcProc1 @AccountID,@AccountRate,@EffectiveDate -- create indexes CREATE CLUSTERED INDEX [CIX_AccountID] ON #MyTempTable ( AccountID ) CREATE NONCLUSTERED INDEX [NCIX_EffectiveDate] ON #MyTempTable ( EffectiveDate, AccountRate ) -- further logic continues using the above created temp table **My questions are-** Q1. Which of the following is a good practise method, considering inserted data can be in the order of 50000+ in most cases Q2. What is the specific significance of OPENQUERY thais is used here?
Where you say "...Q1. Which of the following is a good practise..." do you mean "...which of the above is the best method..."? Without the benefit of being able to test, or know what your security setup is, I like the second method better. If you don't need dynamic SQL there's probably not much point in using it. Incidentally we're in the process of removing hard-coded linked server syntax from all of our code and replacing it with synonyms. The advantage of this is that when our friendly DBA comes to us one day and says "we're moving the db to a different server" it's not a disaster of broken code. We simply change the synonym and all the code can stay the same.
14 People are following this question.
