CODEWITHSUNDEEP
sql-servert-sql
Coding Duchess
Coding Duchess

Reputation: 6929

Improving the query performance

I have the following table for document storage. The document could be either a book with multiple pages, page numbering starting from 0 or a singlepage document.

Category
Title
PageNo - integer
LineNo integer
Key
Value

I need a query that pulls all the keys from the table that belong to one-page documents and Category1.

I have the following query:

SELECT DISTINCT Key 
FROM Materials
WHERE Category='Category1' and Key NOT IN 
(SELECT DISTINCT Key 
from Materials 
WHERE Category='Category1'
and PageNo>0)

The query works fine excluding all the materials that have more than 1 page. I wanted to rewrite the query to improve it in terms of performance and also to avoid any repeating code such as keyword DISTINCT and WHERE clause, if possible. I'd appreciate any pointers in the right direction.

Upvotes: 0

Views: 62

Answers (2)

paparazzo
paparazzo

Reputation: 45106

if PageNo is not null

SELECT Key 
FROM Materials
WHERE Category='Category1'
GROUP BY Key 
having  max(PageNo) <= 0

if PageNo is nullable 

SELECT Key 
FROM Materials
WHERE Category='Category1'
GROUP BY Key 
having  max(isnull(PageNo,0)) <= 0

Upvotes: 3

Leptonator
Leptonator

Reputation: 3529

Remove the second DISTINCT

  • Adding DISTINCT will add additional table scans

Query should be:

SELECT DISTINCT Key 
FROM Materials
WHERE Category='Category1'
 and Key NOT IN 
(SELECT Key 
from Materials 
WHERE Category='Category1'
and PageNo>0)

Upvotes: 1

Related Questions