How to make this query faster

1

Hello,

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

thank you

2

Let's indent this so we can read it:

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

Do you have any indexes?

3

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;
4

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'
	)
...