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

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

Vitaliy
1#
Vitaliy Published in 2017-12-07 17:11:15Z

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?

Katherine Elizabeth Lightsey
2#
Katherine Elizabeth Lightsey Reply to 2017-12-08 13:59:18Z

I've updated the code based on your comment. If you need something else, please provide input and output data expected. This is about all I can do based on inference from the existing comments. Further, this isn't intended to give you an exact working solution. My intention was to give you a prototype from which you can build your solution.

That said:

The code below is the basic join that you need. However, keep in mind that indexing is probably going to play as big a part in performance as the structure of the table and the query. It doesn't matter how you query the tables if the indexes aren't there to support the queries once you reach a certain size. There are a lot of resources online for indexing but viewing querying plans should be at the top of your list.

As a note, your column [dbo].[Property] ([Name]) should probably be NVARCHAR to allow SQL to minimize data storage. Indexes on that column will then be smaller and searches/updates faster.

DECLARE @Property AS TABLE
  (
       [ID]     INT
       , [Name] NVARCHAR(100)
  );

INSERT INTO @Property
            ([ID]
             , [Name])
VALUES      (1,N'A'),
            (2,N'B'),
            (3,N'C');

DECLARE @Move AS TABLE
  (
       [ID]           INT
       , [DateTo]     DATE
       , [MoveTypeID] INT
       , [PropertyID] INT
  );

INSERT INTO @Move
            ([ID]
             , [DateTo]
             , [MoveTypeID]
             , [PropertyID])
VALUES      (1,NULL,1,1),
            (2,NULL,1,2),
            (3,N'2017-12-07',1,2);

SELECT [Property].[ID]     AS [property_id]
       , [Property].[Name] AS [property_name]
       , CASE
             WHEN [Move].[DateTo] IS NULL
                  AND [Move].[MoveTypeID] = 1 THEN
                 N'true'
             ELSE
                 N'false'
         END               AS [in_service]
FROM   @Property AS [Property]
       LEFT JOIN @Move AS [Move]
              ON [Move].[PropertyID] = [Property].[ID]
WHERE  [Move].[DateTo] IS NULL
       AND [Move].[MoveTypeID] = 1; 
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO