CODEWITHSUNDEEP
sqlsql-server
Somomo1q
Somomo1q

Reputation: 675

SQL Server which query runs faster

UPDATE User 
SET Name = (SELECT NameSpace.NameId 
            FROM NameSpace 
            WHERE NameSpace.Name = 'BlaBlaBla') 
WHERE UserId = 1453

This is faster or 

int Value = Select NameSpace.NameId from NameSpace 
            where NameSpace.Name = 'BlaBlaBla';

UPDATE User 
SET Name = "+Value +" 
WHERE UserId = 1453

and 

Select 
    UserName,
    UserAge,
    (Select * from AdressesTable where Adresses.AdresID=User.AdresID) 
from 
    UserTable
where
    UserId='123'

OR

Select * 
from AdressesTable, UserTable 
where Adresses.AdresID = User.AdresID AND UserID = '123'

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

There are a variety of assumptions to be made in determining which is faster.

First, if you are concerned about speed, then you want indexes on users(userid) and namespace(name).

Second, the assignment query should look like this in SQL Server:

declare @Value int;

select @Value = NameSpace.NameId
from NameSpace 
where NameSpace.Name = 'BlaBlaBla';

Your variable declarations and subqueries are not correct for SQL Server.

Finally, even with everything set up correctly, it is not possible to say which is faster. If I assume that there is only one matching record for UserId, then the single update is probably faster -- although perhaps by so little that it is not noticeable. It may not be faster. The update may cause some sort of lock to be taken on NameSpace that would not otherwise be taken. I would actually expect the two to be quite comparable in speed.

However, if many users have the same userid (which is unlikely given the name of the column), then you are doing updates on multiple rows. Storing the calculated result once and using that is probably better than running the subquery multiple times. Even so, with the right indexes, I would expect the difference in performance to be negligible.

Upvotes: 1

Related Questions