How to increases optimize SQL query in SQL Server?-CodePudding

I have a action for login user but I need UserInfo & UserPostAccess.

How to get both with best way and higher optimize and performance?

I try write this code, using a temp table - is there another way?

SELECT TOP 1 id, Code, Name, PostId
INTO #User
FROM Users
WHERE UseName = 'myUser' AND Password = 'myPassword'

SELECT * FROM #User

SELECT PermetionId
FROM UserPostAccess
WHERE Id = (SELECT TOP (1) PostId FROM #User)

SELECT TOP 1 id, Code, Name, PostId
FROM Users
WHERE UseName = 'myUser' AND Password = 'myPassword'

SELECT * FROM #User

SELECT PermetionId
FROM UserPostAccess
WHERE Id = (SELECT TOP (1) PostId FROM Users 
            WHERE UseName = 'myUser' AND Password = 'myPassword')

CodePudding user response：

you get all data together like below

SELECT U.*, P.PermetionId
INTO #User
FROM 
(
 SELECT 
  TOP 1 U.id, U.Code, U.Name, U.PostId
 FROM Users 
 WHERE UseName = 'myUser' AND Password = 'myPassword'

)U
LEFT JOIN UserPostAccess P 
ON P.ID=U.PostId

SELECT * FROM #User

CodePudding user response：

If your query already returns only one record, no need to pull into a #temp table, just do the query and JOIN to the post access directly

SELECT TOP 1 
      u.id, 
      u.Code, 
      u.Name, 
      u.PostId,
      pa.PermetionId
   FROM 
      Users u
         JOIN UserPostAccess pa
            on u.PostId = pa.id
   WHERE 
          u.UseName = 'myUser' 
      AND u.Password = 'myPassword'