Linked Server Query in Temp Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server Query in Temp Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-26 : 11:04:53

Bruce writes "I have a Linked Server Query which times out under load. Basically, I need to pull everything from the Linked Server database into a temp table, then perform the queries I need on that (I think). The joins across the network are killing this, as eveident from the execution plan and trace. Here is my code, which puts the fields from the Linked Server database tables into a temp table then performs the joins and conditional statements:

CREATE PROCEDURE dbo.spELRMCcardXtionByDateTest @dcid nvarchar(255), @startDate datetime, @endDate datetime
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
GO
SET ANSI_WARNINGS OFF
GO
SET ANSI_NULLS OFF
GO

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
card_number varchar(10),
program_number varchar(9),
start_date datetime(8),
end_date datetime(8),
event_number numeric(17),
event_number numeric(17),
status int(4),
budget money(8),
scheduled_date datetime(8),
tx_time numeric(17),
purchase_amount numeric(17),
merchant_name varchar(15),
location_number numeric(17),
location_number numeric(17)
)

-- Insert the rows from LinkedServer into the temp. table
INSERT INTO #TempItems
(card_number,
program_number,
start_date,
end_date,
event_number,
event_number,
status,
budget,
scheduled_date,
tx_time,
purchase_amount,
merchant_name,
location_number,
location_number)

SELECT card_number,
program_number,
start_date,
end_date,
event_number,
status,
budget,
scheduled_date,
status FROM LinkServer.MC_Card.webuser.EVENT

SELECT event_number, tx_time, purchase_amount, location_number FROM LinkServer.MC_Card.webuser.POS_TX,

SELECT merchant_name, location_number FROM LinkServer.MC_Card.webuser.LOCATION

SELECT STORE.[Str#], STORE.[Dcid#], STORE.[Chain], E.card_number, E.program_number,
E.start_date, E.end_date, E.event_number, P.event_number, E.status, E.budget, E.scheduled_date,
P.tx_time, P.purchase_amount, L.merchant_name, L.location_number, P.location_number

FROM (STORE INNER JOIN STORE.[DemoID#] = E.event_number)
LEFT JOIN (L.location_number = P.location_number)
ON E.event_number = P.event_number)

FROM #TempItems
WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
ORDER BY E.program_number

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

What I'm concerned about are these lines (not sure how to get all the fields in one select statement because they're from different tables on the Linked Server database:

SELECT card_number,
program_number,
start_date,
end_date,
event_number,
status,
budget,
scheduled_date,
status FROM LinkServer.MC_Card.webuser.EVENT

SELECT event_number, tx_time, purchase_amount, location_number FROM LinkServer.MC_Card.webuser.POS_TX,

SELECT merchant_name, location_number FROM LinkServer.MC_Card.webuser.LOCATION

So, basically my question is: AM I GOING ABOUT THIS CORRECTLY? If not, could you please provide an example of how I can correct this, either in theory and/or syntax?

Thank you,
Bruce "

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 11:35:46

How about creating a view on the linked server that does the joins?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources