1

This is my first post. I have this data model: enter image description here

I want fetch some profile, with checking if they have any page or user assigned or not. I have tried multiple queries and finally came to these tows:

Query 1:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

SELECT TOP ( 1 )
        p.[ProfileId] AS [ProfileId] ,
        p.[Username] AS [Username] ,
        p.[UsernameCleaned] AS [UsernameCleaned] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Users] u WITH ( NOLOCK )
                             WHERE  u.UserId = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsUser] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Pages] pg WITH ( NOLOCK )
                             WHERE  pg.[PageId] = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsPage]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token 

Query 2:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

DECLARE @ProfileId BIGINT= NULL ,
    @Username NVARCHAR(256)= NULL ,
    @UsernameCleaned NVARCHAR(256)= NULL ,
    @IsPage BIT= 0 ,
    @IsUser BIT= 0;

SELECT TOP ( 1 )
        @ProfileId = p.[ProfileId] ,
        @Username = p.[Username] ,
        @UsernameCleaned = p.[UsernameCleaned]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token;

IF @ProfileId IS NULL
    BEGIN
        RETURN
    END 

SELECT  @IsUser = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Users] u WITH ( NOLOCK )
                                       WHERE    u.UserId = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT  @IsPage = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Pages] pg WITH ( NOLOCK )
                                       WHERE    pg.[PageId] = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT TOP ( 1 )
        @ProfileId AS [ProfileId] ,
        @Username AS [Username] ,
        @UsernameCleaned AS [UsernameCleaned] ,
        @IsUser AS [IsUser] ,
        @IsPage AS [IsPage];

Which one would be better and would have a higher performance?

p.s. I'm not an exact SQL-guy. So, it's hard for me to determine which query would have a better performance and executes faster -I mean as fast as possible. Any help/advise would be absolutely helpful. Thanks in advance.

2

I'd probably write something like the following:

SELECT TOP 1 -- Top command optional and may not actually help speed things up
  p.ProfileId -- No need to alias to the same thing
  , p.Username
  , p.UsernameCleaned
  , IsUser = CAST(CASE WHEN u.UserId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
  , IsPage = CAST(CASE WHEN pg.PageId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM dbo.Profiles p
  LEFT JOIN dbo.Users u ON u.UserId = p.ProfileId -- Left join here instead of exists above
  LEFT JOIN dbo.Pages pg ON pg.PageId = p.ProfileId
WHERE
  p.UsernameCleaned = @Token;

If you notice I basically just traded EXISTS for LEFT JOIN. This should give you reasonable performance and be fairly clear for people who review your code. Of course the standard disclaimer applies for any performance question.

You should profile this on a representative workload to determine what is fastest, and indexes are your friend.

In general though you want to avoid subqueries in the SELECT portion of your query because they will probably be run once for each row in the result set. Here that isn't a big deal but if you have more results then the difference can be dramatic.

Try at all costs to avoid the NOLOCK query hint. It looks nice on paper and can give you an apparent boost, but nothing is free. For more detailed information on why this probably isn't a good idea I'd advise you read this blog post by Aaron Bertrand.

Also of note rewriting your query made it clear that a profile can be both a user and a page. I don't know your use cases but that feels wrong to me especially since this query seems to be designed to tell the difference. If that is true then you might need to rework your data model.

Finally as a personal preference I like to have my aliases on the left if the column is computed/derived like IsUser above. My reasoning goes the alias can get lost in the confusion of the calculations so it is best to put it first. Of course you should go with what you and your coworkers find is most legible.

|improve this answer|||||
0

There will not be much difference between those two queries in terms of performance and this question to me is all about indexing. If your model is as simple as your diagram, then all you really need is one covering index on dbo.Profiles, ie

CREATE INDEX idx_Profiles__UserNameCleaned ON dbo.Profiles ( UserNameCleaned ) INCLUDE ( UserName );

I have indexed column UsernameCleaned because it is the main search criteria in the dbo.Profiles query and this will allow an index seek. I have added column UserName as an included column as we SELECT it but don't have a WHERE clause or JOIN against it. I have not included ProfileId in the index as it will automatically be included as it is the primary key of the table. Have a look at these articles if you want to understand covering indexes better: here and here. The primary keys on the other two tables should ensure you are getting clustered index seeks there although check your execution plans to confirm.

A few other notes:

  • I would favour query 2 as it's easier to read
  • make sure data-types match, ie where variable @Token is NVARCHAR(256) make sure the column is also, where variable @ProfileId is BIGINT make sure the table columns are too
  • you could do without the excessive TOP 1 references in the EXISTS queries as EXISTS will drop out as soon as it's found a record. However this won't make a make much of a difference in performance, just tidier code.

I created a simple test rig which you can use to try out different options:

USE tempdb
GO

IF OBJECT_ID('dbo.Pages') IS NOT NULL DROP TABLE dbo.Pages
IF OBJECT_ID('dbo.Profiles') IS NOT NULL DROP TABLE dbo.Profiles
IF OBJECT_ID('dbo.Users') IS NOT NULL DROP TABLE dbo.Users
GO

CREATE TABLE dbo.Users
(
    UserId          INT PRIMARY KEY,
    SomeProperty    UNIQUEIDENTIFIER
)
GO


CREATE TABLE dbo.Profiles
(
    ProfileId       INT PRIMARY KEY REFERENCES dbo.Users ( UserId ),
    UserName        NVARCHAR(256) NOT NULL,
    UserNameCleaned NVARCHAR(256) NOT NULL
)
GO


CREATE TABLE dbo.Pages
(
    PageId          INT PRIMARY KEY REFERENCES dbo.Profiles ( ProfileId ),
    SomeProperty    UNIQUEIDENTIFIER
)
GO


CREATE INDEX idx_Profiles__UserNameCleaned ON dbo.Profiles ( UserNameCleaned ) INCLUDE ( UserName )
GO


-- Generate test data; scale 1m
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.Users ( UserId, SomeProperty )
SELECT rn, NEWID()
FROM cte
GO


INSERT INTO dbo.Profiles( ProfileId, UserName, UserNameCleaned )
SELECT UserId, SomeProperty, LEFT( SomeProperty, 8 )
FROM dbo.Users


INSERT INTO dbo.Pages( PageId, SomeProperty )
SELECT UserId, SomeProperty
FROM dbo.Users


-- Create our test case
UPDATE dbo.Profiles
SET UsernameCleaned = N'amiry'
WHERE ProfileId = ( SELECT MAX(ProfileId) - 1 FROM dbo.Profiles )
GO




DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

SELECT TOP ( 1 )
        p.[ProfileId] AS [ProfileId] ,
        p.[Username] AS [Username] ,
        p.[UsernameCleaned] AS [UsernameCleaned] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Users] u WITH ( NOLOCK )
                             WHERE  u.UserId = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsUser] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Pages] pg WITH ( NOLOCK )
                             WHERE  pg.[PageId] = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsPage]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token 
GO



DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

DECLARE @ProfileId BIGINT= NULL ,
    @Username NVARCHAR(256)= NULL ,
    @UsernameCleaned NVARCHAR(256)= NULL ,
    @IsPage BIT= 0 ,
    @IsUser BIT= 0;

SELECT TOP ( 1 )
        @ProfileId = p.[ProfileId] ,
        @Username = p.[Username] ,
        @UsernameCleaned = p.[UsernameCleaned]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token;

IF @ProfileId IS NULL
    BEGIN
        RETURN
    END 

SELECT  @IsUser = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Users] u WITH ( NOLOCK )
                                       WHERE    u.UserId = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT  @IsPage = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Pages] pg WITH ( NOLOCK )
                                       WHERE    pg.[PageId] = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT TOP ( 1 )
        @ProfileId AS [ProfileId] ,
        @Username AS [Username] ,
        @UsernameCleaned AS [UsernameCleaned] ,
        @IsUser AS [IsUser] ,
        @IsPage AS [IsPage];

And my annotated test results, showing little performance difference bewteen the two queries and improvements after non-clustered index applied:

Annotated test results

Let us know how you get on.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.