CODEWITHSUNDEEP
sqlsql-server
JoJo
JoJo

Reputation: 4923

any improvements to SQL query with subquery of view?

I am returning 250,000 records in about 1:10 seconds... too long for a Job?

I feel like SQL Server should be faster..

SELECT ColOne 
    FROM TableA WITH (nolock) 
    WHERE EXISTS (SELECT ColOne 
                        FROM VIEW_1 WITH (nolock) 
                         WHERE LastDate < GETDATE() - 30)

Any ways to improve the speed? Or am I making an error?

*EDIT To clear up any questions regarding the view - there is a potential that VIEW_1 has more than one record for ColOne.

The View SQL:

    SELECT MAX(LastSeen) AS LastSeenDate, IDValue 
FROM dbo.IDValueTable_LASTSEEN WITH (nolock) 
WHERE (LastSeen IS NOT NULL) AND EXISTS (SELECT IDValue 
    FROM dbo.IDValueTable WITH (nolock)) GROUP BY IDValue

**UPdate I believe this to be the working query ( I still need to confrim there are not more or less records to be found )

SELECT DISTINCT IDValue FROM dbo.IDValueTable_LASTSEEN WITH (nolock) WHERE (LastSeen IS NOT NULL) AND (LastSeen < GETDATE() - 30) AND IDValue IN (SELECT IDValue FROM dbo.IDValueTable WITH (nolock))

We need to apply both DISTINCT and yous IN not EXISTS

Upvotes: 0

Views: 106

Answers (5)

pedromarce
pedromarce

Reputation: 5669

Assuming that the tables from the View are the ones also in the select I would suggest.

Add an index to table dbo.IDValueTable_LASTSEEN for the field LastSeen.

Forget about the VIEW, is not adding value in this case and it actually slows the query (why calculate the max of lastseen if later you are already filtering it).

Use following SELECT.

SELECT DISTINCT IDValue 
  FROM dbo.IDValueTable_LASTSEEN WITH (nolock) 
  WHERE (LastSeen IS NOT NULL) 
   AND (LastSeen < GETDATE() - 30) 
   AND EXISTS (SELECT IDValue 
                 FROM dbo.IDValueTable WITH (nolock))

Upvotes: 1

rwking
rwking

Reputation: 1032

I just wanted to address the first part of the question briefly - "too long for a job?"

This depends on your particular implementation. Are you running it from a network server, or as a local instance? Hardware, network traffic, server utilization, etc. can all impact your execution time.

For example: My company just upgraded the boxes that house our SQL Databases. With 250+ employees at this location, my experience of the performance improvements is marginal at best. I'd say the advice above should help.

Upvotes: 2

D Stanley
D Stanley

Reputation: 152521

As @valex points out in his comment I think youe EXISTS condition is wrong. If a record exists where LastDate < GETDATE() - 30 then you'll get the whole table back - otherwise you'll get nothing.

I think you want (with table names added for clarity)

SELECT ColOne 
    FROM TableA WITH (nolock) 
    WHERE TableA.ColOne IN (
        SELECT VIEW_1.ColOne 
        FROM VIEW_1 WITH (nolock) 
        WHERE LastDate < GETDATE() - 30)

The main performance improvements here would be indexes on LastDate and ColOne

Upvotes: 2

Smileek
Smileek

Reputation: 2782

Joining should work faster:

SELECT a.ColOne 
FROM TableA AS a
     INNER JOIN VIEW_1 AS b 
     ON a.ColOne = b.ColOne AND b.LastDate < GETDATE() - 30;

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Try joining the tables instead:

SELECT a.ColOne 
FROM TableA a
INNER JOIN VIEW_1 b ON a.ColOne = b.ColOne
WHERE b.LastDate < GETDATE() - 30;

Upvotes: 0

Related Questions