troubleshooting Question

linked server one connection whole procedure

Avatar of kvdi765
kvdi765 asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005
4 Comments1 Solution411 ViewsLast Modified:
i have following part of procedure

-- PRODUCT SPECIALIST
      -- Cursor om alle benodigde data op te halen
      DECLARE CSR_ProdSpec CURSOR FAST_FORWARD
      FOR      SELECT DISTINCT BrandID, ProductGroupID FROM Item.ItemTemp

      -- Cursor openen om de data te overlopen
      OPEN CSR_ProdSpec
      -- Eerste record opvragen
      FETCH NEXT FROM CSR_ProdSpec
      INTO @Brand, @ProdGroup

      -- Loopen zolang er records gevonden zijn
      WHILE (@@FETCH_STATUS = 0)
       BEGIN
            -- Voor het merk in Eproc enkel de eerste 5 characters nemen
            SET @TempBrand = LEFT(@Brand,5)

            -- De ProductSpecialist ophalen voor dit artikel
            SET @OracleCall = 'SELECT @TempEmplId = EmplId FROM OPENQUERY(Lnk_DolmenReportingEproc2, ''SELECT OFFR.PKG_CREART.sf_bepaal_productmanager(''''' + @TempBrand + ''''',''''' + @ProdGroup + ''''',''''' + @DefaultMgr + ''''') AS EmplId FROM DUAL'')'

            SET @OracleParams = N'@TempEmplId varchar(5) OUTPUT'
            EXEC sp_executesql @OracleCall, @OracleParams, @TempEmplId = @TempSpecialist OUTPUT

            -- De P van het EmployeeID afhalen
            SET @ProdSpecialist = LEFT(@TempSpecialist,4)

            -- Items met deze Brand en ProdGroup updaten met de product specialist
            UPDATE Item.ItemTemp
            SET ProductSpecialist = @ProdSpecialist
            WHERE BrandID = @Brand
            AND ProductGroupID = @ProdGroup

            -- Volgende record ophalen
            FETCH NEXT FROM CSR_ProdSpec
            INTO @Brand, @ProdGroup
        END

      -- Cursor afsluiten en verwijderen
      CLOSE CSR_ProdSpec
      DEALLOCATE CSR_ProdSpec

for each record in the cursor we submit a function on a oracle database with a linked server
my opinion is that if we have 5000 records in the cursor, that he is gonna make 5000 times
a connection with oracle through the linked server
for 5000 records it takes 15 minutes at the moment

is there a possibility to open the linked server connection once in the beginning of the procedure
and end it at the end of the procedure
this will save us much time i think

any help is welcome

greetings
ASKER CERTIFIED SOLUTION
twoboats

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros