the below query takes too Long for me. doy ou have any ideas how to make it faster?
select * from
(select (200/[traveltimeLkw])*3.6 as v_lkw, [entrytime] ,[vLkwCommon] FROM [VRZ1010].[vdp_archiv_stv].[dbo].[STV_SVL_Archiv_20175] where
[roadcode]='A23_1' and [from2dk]>=9000 and [to2dk]<=10000 ) A inner join
(select * from [vdp_evis_db].[dbo].[DayCategory]) B on B.[day]= CONVERT(date,A.[entrytime]) and B.[weekday]=1
DayCategory hast the following columns : day, weekday, weekdayname, daycat
and the other query has the following columns: v_lkw, vlkwcommon and entrytime
select *
from
(select (200/[traveltimeLkw])*3.6 as v_lkw, [entrytime] ,[vLkwCommon]
FROM [VRZ1010].[vdp_archiv_stv].[dbo].[STV_SVL_Archiv_20175]
where [roadcode]=‘A23_1’ and [from2dk]>=9000 and [to2dk]<=10000 ) A
inner join
(select * from [vdp_evis_db].[dbo].[DayCategory]) B
on B.[day]= CONVERT(date,A.[entrytime]) and B.[weekday]=1
Now that Dennis has kindly formatted the code, the obvious problem is the 4 part naming convention which will take all the data from the remote server before filtering it.
One way around this is to use OPENQUERY:
WITH A
AS
(
SELECT v_lkw, entrytime, vLkwCommon
FROM OPENQUERY
(VRZ1010,
'SELECT (200/[traveltimeLkw])*3.6 AS v_lkw, [entrytime] ,[vLkwCommon]
FROM [vdp_archiv_stv].[dbo].[STV_SVL_Archiv_20175]
WHERE [roadcode]= ''A23_1'' and [from2dk]>=9000 and [to2dk]<=10000'
)
)
SELECT * -- columnlist
FROM A
JOIN [vdp_evis_db].[dbo].[DayCategory] B
ON B.[day] >= DATEADD(day, DATEDIFF(day, 0, A.[entrytime]), 0)
AND B.[day] < DATEADD(day, DATEDIFF(day, 0, A.[entrytime]), 1)
AND B.[weekday]=1;
That's still pretty horrid ... must be some way to code that out to make it SARGable?
Could have a computed column I suppose (and INDEX it!)
Would this help?
ON A.[entrytime] >=-B.[day]
AND B.[day] < DATEADD(day, DATEDIFF(day, 0, A.[entrytime]), 1)
Maybe doing the calculation on the Remote server would do?
...
SELECT v_lkw, entrytime, vLkwCommon
, entrytime_DAY
FROM OPENQUERY
(VRZ1010,
'SELECT (200/[traveltimeLkw])*3.6 AS v_lkw, [entrytime] ,[vLkwCommon]
, DATEADD(day, DATEDIFF(day, 0, [entrytime]), 1) AS entrytime_DAY
FROM [vdp_archiv_stv].[dbo].[STV_SVL_Archiv_20175]
WHERE [roadcode]= ''A23_1'' and [from2dk]>=9000 and [to2dk]<=10000'
)
...