0

I am just trying to understand why the below query is taking long time.

SELECT 
   t.dp_id
  ,d.hide_flag
  ,d.metadata_release_date
  ,d.run_code
  ,d.instrument_code
  ,r.s_region 
FROM sampledb1.dbo.xshooter t 
INNER JOIN sampledb2.dbo.dp_access d 
ON t.dp_id = d.file_id 
INNER JOIN sampledb3.dbo.raw r 
ON t.dp_id = r.dp_id 
WHERE t.dp_id = 'TEST'

All the tables have indexes on the columns they are joining in (actually they are the primary keys)

Thomas
  • 11,958
  • 1
  • 14
  • 23
Maddy
  • 11
  • 4
  • As the id is a string, it can affect performance. See this question: https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-mysql-database – rafaelmi Mar 31 '23 at 13:24
  • If you run the query in SQL Server Management studio and turn on the query plan, it will tell you what it is doing and also recommend indexes that it needs. – Martin Brown Mar 31 '23 at 13:42
  • You are joining across three Databases, are they at least on the same Server? – Thomas Mar 31 '23 at 16:39
  • Does it take the time to show the result in SSMS? Have you tryed to insert it into a temp table? Try OPTION(FORCE ORDER) – jigga Apr 01 '23 at 08:08

0 Answers0