February 22, 2010 at 9:01 am
Hi,
I hope you can help me out, I'm trying to do the following JOIN
SELECT
o.ordernumber ,
o.status,
o1.orderdesc,
l.location
FROM orders o
INNER JOIN LINKEDSERVER.ORDERS.dbo.orders ord ON o.ordernumber=ord.ordernumber
INNER JOIN LINKEDSERVER.ORDERS.dbo.loc l ON l.pk_id=ord.loc_pk_id
where o.status <> 3 and o.ordernumber IN (
'order1'
'order2'
'order3'
..
'order200')
This works but it's TOO SLOW. (I just delimited to 200 records and it takes up 2 min..! :crying:)
My server is a SQL Server 2K and the linked server is a SQL Server 2005
Both ordernumber fields (local and in linked server) are indexed. and the relationship between orders and loc in the linked server seems to be ok.
Is there anything I am doing wrong?
Your help would be highly appreciated.
February 22, 2010 at 9:12 am
Could the problem just be network speed?
_________________________________
seth delconte
http://sqlkeys.com
February 22, 2010 at 9:37 am
Linked servers are slow to join against.
here's the reason:
you are joining LINKEDSERVER.ORDERS.dbo.orders to your table.
the result is that the entire table LINKEDSERVER.ORDERS.dbo.orders gets pulled into the temp database, and then the join is performed.
All that data over the wire and into temp takes time.
Your objective would be to limit the results coming from the linked server.
for example, if you did something like this:
SELECT
o.ordernumber ,
o.status,
o1.orderdesc,
l.location
FROM orders o
INNER JOIN (SELECT *
FROM LINKEDSERVER.ORDERS.dbo.orders
WHERE ordernumber IN (
'order1'
'order2'
'order3'
...
'order200') ) ord ON o.ordernumber=ord.ordernumber
note how we are asking for a subset of the linked server based on the IN() criteria?
I believe that will help;
you'd need to do the same for the LINKEDSERVER.ORDERS.dbo.loc table; you probably need to join those two linked tables tegether as a subselect instead.
Lowell
February 22, 2010 at 9:45 am
maybe something like this as a prototype?
trying to join the two linked tables together.
SELECT
o.ordernumber ,
o.status,
o1.orderdesc,
l.location
FROM orders o
INNER JOIN (SELECT
ordinner.ordernumber,
l.loc
FROM LINKEDSERVER.ORDERS.dbo.orders ordinner
INNER JOIN LINKEDSERVER.ORDERS.dbo.loc l ON l.pk_id=ordinner.loc_pk_id
WHERE ordinner.ordernumber
IN (
'order1',
'order2',
'order3',
'order200') ) ord ON o.ordernumber=ord.ordernumber
where o.status <> 3 and o.ordernumber IN (
'order1',
'order2',
'order3',
'order200')
Lowell
February 22, 2010 at 10:19 am
Hi Lowell,
Thanks a lot for your explanation about what i was trying to do.
You were right, delimiting the query to the linked server using a subselect worked perfectly. Less than 1 second, Great! :-D.
Just another question. If I use this query How can I achieve something like:
SELECT
o.ordernumber ,
o.status,
o1.orderdesc,
l.location
FROM orders o
INNER JOIN (SELECT
ordinner.ordernumber,
l.loc
FROM LINKEDSERVER.ORDERS.dbo.orders ordinner
INNER JOIN LINKEDSERVER.ORDERS.dbo.loc l ON l.pk_id=ordinner.loc_pk_id
WHERE ordinner.ordernumber
IN (
--All Serials from local database orders with a status different from 3
--??? select ordernumber from orders where status <>3
)
) ord ON o.ordernumber=ord.ordernumber
where o.status <> 3
With the same performance as the example you provide??
Thanks a lot again.
-Adán.
February 22, 2010 at 10:56 am
Adan, you might need to switch to dynamic SQL to do that I think; you need to get the data into a comma delomited list for the IN() statement to be performed on the linked server side.
does this return a comma delimited list? is ordernumber a varchar or an integer? i didn't have an example table, so the code is WAG.
--- Concatenated Format
SELECT status,stuff(( SELECT ',' + ordernumber
FROM orders s2
WHERE s2.status= s1.status --- must match GROUP BY below
ORDER BY ordernumber
FOR XML PATH('')
),1,1,'') as [OrderNumber]
FROM orders s1
GROUP BY s1.status --- without GROUP BY multiple rows are returned
ORDER BY s1.status
Lowell
February 22, 2010 at 11:54 am
Hi Lowell,
Nope I didn't get a comma separated list. and ordernumber is a varchar(20).
My main server is SQL 2000 and the instruction FOR XML PATH.. is not available.
I tried using XML RAW and AUTO. but I received an error:
Incorrect Syntax near FOR
--- Concatenated Format
SELECT status,stuff(( SELECT ',' + ordernumber
FROM orders s2
WHERE s2.status= s1.status --- must match GROUP BY below
ORDER BY ordernumber
FOR XML AUTO
),1,1,'') as [OrderNumber]
FROM orders s1
GROUP BY s1.status --- without GROUP BY multiple rows are returned
ORDER BY s1.status
I tried to run just the select inside STUFF and it runs ok.
SELECT ',' + ordernumber
FROM orders s2
-- WHERE s2.status= s1.status --- must match GROUP BY below
ORDER BY ordernumber
FOR XML AUTO
This is What i got
<s2 OrdNum=",008101BP00019D" />
<s2 OrdNum=",0611ACBN53235K" />
<s2 OrdNum=",4354AABP01782J" />
<s2 OrdNum=",4354AABP02808J" />
<s2 OrdNum=",5091ABBQ00003A" />
<s2 OrdNum=",5091ABBQ00323A" />
<s2 OrdNum=",5091ABBQ00417A" />
<s2 OrdNum=",5091ABBQ00639A" />
<s2 OrdNum=",5091ABBQ00827A" />
<s2 OrdNum=",5091ABBQ00910A" />
<s2 OrdNum=",5091ABBQ00966A" />
<s2 OrdNum=",5091ABBQ01588A" />
<s2 OrdNum=",5091ACBQ00092A" />
<s2 OrdNum=",5091ACBQ00122A" />
<s2 OrdNum=",5091ACBQ00457A" />
<s2 OrdNum=",5091ACBQ00716A" />
<s2 OrdNum=",5091ACBQ01979A" />
<s2 OrdNum=",564201BN00110J" />
<s2 OrdNum=",608704BQ00186B" />
<s2 OrdNum=",729702BQ02227B" />
<s2 OrdNum=",729702BQ16269B" />
<s2 OrdNum=",729702BQ17930B" />
<s2 OrdNum=",729702BQ31934B" />
<s2 OrdNum=",930201BQ01267E" />
<s2 OrdNum=",930201BQ02492F" />
<s2 OrdNum=",930201BQ05130F" />
<s2 OrdNum=",930201BQ08265D" />
<s2 OrdNum=",930201BQ09117D" />
<s2 OrdNum=",930201BQ11033D" />
<s2 OrdNum=",930201BQ12286F" />
<s2 OrdNum=",930201BQ12606F" />
<s2 OrdNum=",930201BQ13522F" />
<s2 OrdNum=",930201BQ14230F" />
<s2 OrdNum=",930201BQ16979F" />
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply