Home How to create high performance SQL select query, if I need a condition for referrer table's records?
Reply: 0

How to create high performance SQL select query, if I need a condition for referrer table's records?

user1006
1#
user1006 Published in July 17, 2018, 7:56 am

For example, I have 2 tables, which I need for my query, Property and Move for history of moving properties.

I must create a query which will return all properties + 1 additional boolean column, IsInService, which will have value true, in cases, when Move table has a record for property with DateTo = null and MoveTypeID = 1 ("In service").

I have created this query:

SELECT  
    [ID], [Name], 
    (SELECT COUNT(*) 
     FROM [Move]
     WHERE PropertyID = p.ID 
       AND DateTo IS NULL 
       AND MoveTypeID = 1) AS IsInService
FROM 
    [Property] as p
ORDER BY 
    [Name] ASC
OFFSET 100500 ROWS FETCH NEXT 50 ROWS ONLY;

I'm not so strong in SQL, but as I know, subqueries are the evil :)

How to create high performance SQL query in my case, if it is expected that these tables will include millions of records?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.32695 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO