SQL Server 2012 Database Slowness

Question

So we have a system that uses two columns as the unique ID the userid as well as the a date. We have to keep every record that has ever been associated with a particular subject so there are no deleted records. So one subject can have 50 records. The database designer created views to get the latest row for a subject. Database is really not that huge in terms of record count we are roughly at 750000 records.

The view is written for every table very similar to:

Select Username, 
UserID 
From users 
where USerID = 000 
and UserUpdatedDate = (
    Select MAX(UserUpdatedDate) 
    FROM 
    users a 
    WHERE a.USerID = UserID 
)

We are seeing a major slowness, any suggestions would be welcomed?

We are rewriting some queries using temp tables, it seems to be quicker. Is this a good thing or bad in long haul

Temp table usage isn't bad, unless the reason it needs to exist is bad code. Subselects aren't the best..try here https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date/19433107#19433107 Any loops used in code might also be hurting. Otherwise, watch your system resources (create traces) and locate that bottleneck. — Twelfth, Mar 29 '18 at 15:34
For query performance tuning you first need to know what the bottle neck is. This means that you need to look at the execution plan and see what's taking so long. — Zohar Peled, Mar 29 '18 at 15:35

score 1 · Accepted Answer · answered Mar 29 '18 at 15:31

1

Replace this subquery (Select MAX(UserUpdatedDate) FROM users a WHERE a.USerID = UserID ) with a join - subqueries are slow

answered Mar 29 '18 at 15:31

Daniel Marcus

2,686
1
7
13

See Twelfths first comment above - use a temp table and then join – Daniel Marcus Mar 29 '18 at 15:36

SQL Server 2012 Database Slowness

1 Answers1