x

[Closed] 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?

more ▼

asked Sep 05, 2014 at 03:35 PM in Default

avatar image

nidheesh.r.pillai
9.5k 23 59 49

(comments are locked)
10|1200 characters needed characters left

The question has been closed Mar 27, 2015 at 09:29 AM by nidheesh.r.pillai for the following reason:

The question is answered, right answer was accepted


1 answer: sort voted first

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.

more ▼

answered Sep 05, 2014 at 07:28 PM

avatar image

GPO
4.9k 41 51 58

(comments are locked)
10|1200 characters needed characters left

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170
x150
x144
x46
x33

asked: Sep 05, 2014 at 03:35 PM

Seen: 631 times

Last Updated: Sep 10, 2014 at 12:34 PM

Copyright 2017 Redgate Software. Privacy Policy