Select top 5 across 2 databases very slow

Question

I have to select top 5 rows in another two databases and sort each databases columns.

This is my code.

SELECT TOP 5 DK.dbo.TIME.TIME_STRT, DB1.dbo.DATA.PLC_11
FROM DK.dbo.TIME, DB1.dbo.DATA
ORDER BY TIME_STRT DESC, PLC_11 desc;

It has been running for 10 minutes.

+edit

I think I made a strange explanation

DB1.dbo.DATA
PLC_TIME                 PLC_11
2019-11-24 23:25:48.443  110
2019-11-24 23:25:49.460  191
2019-11-24 23:25:57.037  599
2019-11-24 23:26:49.473  110
2019-11-24 23:26:50.490  195
2019-11-24 23:28:06.547  600
2019-11-24 23:28:36.720  109
2019-11-24 23:28:37.733  201
2019-11-24 23:29:06.553  600
2019-11-24 23:24:47.413  110
2019-11-24 23:24:48.437  186
2019-11-24 23:24:57.027  599
2019-11-24 23:29:37.747  109
2019-11-24 23:29:38.760  204
2019-11-24 23:30:06.570  600
2019-11-24 23:30:38.777  109
2019-11-24 23:30:39.793  210
2019-11-24 23:31:06.560  600
2019-11-24 23:31:39.817  109
2019-11-24 23:31:40.850  213

DK.dbo.TIME
TIME_STRT                TIME_CODE
2020-03-11 08:20:48.000  11619
2020-03-11 09:45:36.000  11608
2020-03-11 07:27:21.587  11616
2020-03-11 13:38:08.000  11672
2020-03-11 07:36:18.000  11627
2020-03-11 08:19:15.000  11629
2020-03-11 07:05:41.000  11626
2020-03-11 10:15:45.000  11609
2020-03-11 06:48:45.000  11624
2020-03-11 13:31:12.000  11669
2020-03-11 16:30:43.000  11685
2020-03-11 13:18:38.000  11671
2020-03-11 14:30:05.000  11684
2020-03-11 13:20:50.000  11645
2020-03-11 14:07:31.000  11683
2020-03-11 13:07:21.090  11673
2020-03-11 15:20:20.000  11663
2020-03-11 14:30:43.000  11662
2020-03-11 14:10:01.000  11661
2020-03-11 13:00:23.000  11647

I want this result.

2020-03-11 16:30:43.000  600
2020-03-11 15:20:20.000  600
2020-03-11 14:30:43.000  600
2020-03-11 14:30:05.000  600
2020-03-11 14:10:01.000  599

The two databases and tables are not related.

*Never* use commas in the `FROM` clause. *Always* use proper, explicit, **standard**, readable `JOIN` syntax. — Gordon Linoff, Mar 11 '20 at 02:00
not just 2 databases but a linked server as well. While linked servers can be very useful, they are fagile and easily abused. This is one such case - you are doing a cross-join involving every row of both tables. Without knowing your goal, there isn't much anyone can suggest. And another good practice is to give your tables useful but short aliases and to qualify every column with the appropriate alias. That will let the reader know which row comes from which table. — SMor, Mar 11 '20 at 02:09
There is no association between the two tables... I want one result in two tables. — Minsung Choi, Mar 11 '20 at 02:16
[Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952/62576) — Ken White, Mar 11 '20 at 02:29
One result in two tables? That does not makes sense. Can you provide an example with data? For instance, the TIME table has these 10 rows and the DATA table has these 15 rows and I want these 5 rows. — Isaac, Mar 11 '20 at 03:11
Also, you say you want 5 rows, but as @SMor stated above by selecting from two tables with no join your are doing a cross-join (or cartesian product) getting all combinations or rows from both tables. How many rows do each of your tables have. Let's say that have 10,000 and 15,000. Now your results are 150 million rows before being reduced to your TOP 5. Cross-joins have their place, but need to be used carefully. Check out this [question](https://stackoverflow.com/questions/11861417/what-is-the-difference-between-cartesian-product-and-cross-join) and its answers for some more background. — Isaac, Mar 11 '20 at 03:14
SQL Server versions prior to SQL Server 2012 had problems with statistics and linked servers. Linked servers can also suffer from the N+1 query problem. — Mitch Wheat, Mar 11 '20 at 05:02
SQL Server 2005 was out of any kind of support years ago!! (April 12, 2016 to be precise) — Mitch Wheat, Mar 11 '20 at 05:02

score 0 · Answer 1 · answered Mar 11 '20 at 13:08

I think I finally understand what you are after. I do not understand why you want this, but here is my solution.

Basically, you just need a simple query for each table ordering them in descending order. To join them and return them in the same table use the ROW_NUMBER() function and then join on that row number. With the ROW_NUMBER() function you can move the ORDER BY into the OVER clause.

SELECT t.TIME_STRT, p.PLC_11
FROM
(
SELECT TOP (5)
       TIME_STRT
     , TIME_CODE
     , ROW_NUMBER() OVER(ORDER BY TIME_CODE DESC) AS ROW_NUMBER
FROM TIME
) t 
INNER JOIN
(
SELECT TOP (5) 
       PLC_TIME
     , PLC_11
     , ROW_NUMBER() OVER(ORDER BY PLC_11 DESC) AS ROW_NUMBER
FROM DATA
) p
ON t.ROW_NUMBER = p.ROW_NUMBER

Which yields your desired result.

TIME_STRT                PLC_11
2020-03-11 16:30:43.000  600
2020-03-11 15:20:20.000  600
2020-03-11 14:30:43.000  600
2020-03-11 14:30:05.000  600
2020-03-11 14:10:01.000  599

Here is the dbfiddle to see it in action.

Select top 5 across 2 databases very slow

1 Answers1