July 24, 2003 at 12:46 pm
I have a query running really slow across two servers which are linked. is there anything specific that i could look into to see whats affecting them.
Thanks
July 24, 2003 at 12:55 pm
Can you post your query?
July 24, 2003 at 1:03 pm
Here it is. Also this on the source server takes few secs only
SELECT
DISTINCT
PROD_KEY,
CAST(1 AS BIT) AS DISPLAY_FLAG,
RIGHT(PROD_KEY, 7) AS PROD_ID,
PROD_NM,
LEFT('MARKET', 18) AS CLIENT_MKT_NM,
USC_CD,
ATC_CD,
CASE WHEN CORP_NM = 'UNKNOWN' THEN MFR_NM ELSE CORP_NM END,
NULL AS PROD_FORM_CD,
NULL AS STRNT_DESC,
MFR_ABRV,
RX_STAT,
CLIENT_MKT_CD,
CASE WHEN CORP_NM = 'UNKNOWN' THEN LEFT(PROD_NM + ':' + MFR_NM, 30)
ELSE LEFT(PROD_NM + ':' + CORP_NM, 30) END AS RED_PROD_NM,
LEFT('MARKET', 18) AS RED_CLIENT_MKT_NM,
USC_CD AS RED_USC_CD,
MFR_NM AS RED_MFR_NM,
NULL AS RED_PROD_FORM_CD,
NULL AS RED_STRNT_DESC,
0 AS FLAG,
'MARKET' AS GROUP_1_NAME,
COUNTRY_NM AS GROUP_2_NAME,
CAST(NULL AS VARCHAR(50)) AS GROUP_3_NAME,
CAST(NULL AS VARCHAR(50)) AS GROUP_4_NAME,
CAST(0 AS INT) AS GROUP_1_ID,
CAST(0 AS INT) AS GROUP_2_ID,
CAST(0 AS INT) AS GROUP_3_ID,
CAST(0 AS INT) AS GROUP_4_ID,
CAST(NULL AS NUMERIC(18, 3) ) AS MARKET_SORT,
CAST(NULL AS NUMERIC(18, 3) ) AS GROUP_1_SORT,
CAST(NULL AS NUMERIC(18, 3) ) AS GROUP_2_SORT,
CAST(NULL AS NUMERIC(18, 3) ) AS GROUP_3_SORT,
CAST(NULL AS NUMERIC(18, 3) ) AS GROUP_4_SORT,
CAST(NULL AS NUMERIC(18, 3) ) AS RED_PROD_SORT
FROM
TEST1SERVER.TESTDB.DBO.VW_IMC_MIDAS_PROD_DIM
WHERE PROD_KEY IN
(SELECT PROD_KEY FROM TEST1SERVER.TESTDB.dbo.VW_IMC_MIDAS_PROD_DIM WHERE ATC_CD LIKE 'J01%'
UNION
(SELECT PROD_KEY FROM TEST1SERVER.TESTDB.dbo.VW_IMC_MIDAS_PROD_MLCL WHERE MLCL_NM = 'FOSFOMYCIN TROMETAMOL')
UNION
(SELECT PROD_KEY FROM TEST1SERVER.TESTDB.dbo.VW_IMC_MIDAS_PROD_DIM WHERE PROD_KEY IN
(SELECT PROD_KEY FROM TEST1SERVER.TESTDB.dbo.VW_IMC_MIDAS_PROD_MLCL WHERE MLCL_NM = 'ERYTHROMYCIN')
AND PROD_KEY IN
(SELECT PROD_KEY FROM TEST1SERVER.TESTDB.dbo.VW_IMC_MIDAS_PROD_MLCL WHERE MLCL_NM = 'ACETYLSULFAFURAZOLE')))
AND
PROD_FORM_CD LIKE '[ABCDEFG]%'
July 25, 2003 at 9:34 pm
Ok,
First the LIKE statements in your Unions may be hurting you. If the like was LIKE '%dog%' I know it would be hurting you.
Is there an index on ATC_CD in VW_IMC_MIDAS_PROD_DIM?
How about
VW_IMC_MIDAS_PROD_MLCL on MLCL_NM?
Also could you replace your union with an or?
Finally is this SQL Server 7 or 2K?
Henry
July 29, 2003 at 8:47 am
sql version is 2000, will that make any difference? please advise
July 29, 2003 at 8:57 am
Its strange that the same query takes 20 secs to return the results when linked to a diff server but with test1server it goes on for a long time.
July 29, 2003 at 9:04 am
Could this be a networking problem? Maybe your testserver1 has a slower connection for some reason.
First of all you can check response times using PING. Another possible issue is discovery of the servername (DNS).
August 7, 2003 at 10:23 am
Why 7 vs. 2K matters. The optimizer in 2K does a better job at using some indexes and developes better plans.
The network may be an issue. You may want to try running the part of the query that goes against the remote box aginst both the slow and fast boxes from the 'primary' server and see if there is a speed difference there.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply