best optimized query for the requirement

Question

Post reply

best optimized query for the requirement

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
March 20, 2013 at 1:12 am

#291009

I have the below query and need the best query to optimize the requirement
SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=1 AND product_code in (1))
AND product_id IN (SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=2 AND product_code in (3))
AND product_id IN (SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=3 AND product_code in (2))
AND product_id IN (SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=4 AND product_code in (1)))))

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

SELECT DISTINCT product_id

FROM fact_sales

WHERE (agent_id=1 AND product_code in (1))

OR (agent_id=2 AND product_code in (3))

OR (agent_id=3 AND product_code in (2))

OR (agent_id=4 AND product_code in (1))

AND & OR operators

http://www.w3schools.com/sql/sql_and_or.asp

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Depending upon existing indexes, this could be a significant improvement:

DROP TABLE #fact_sales

CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))

INSERT INTO #fact_sales (agent_id, product_code, product_id)

VALUES

(1,1,'1'),

(1,1,'2'),

(1,1,'3'),

(1,1,'4'),

(1,1,'5'),

(2,3,'1'),

(3,2,'1'),

(4,1,'1'),

(4,1,'2')

CREATE CLUSTERED INDEX cx_Everything ON #fact_sales (Product_ID, agent_id, product_code)

-- existing version

SELECT product_id

FROM #fact_sales

WHERE (agent_id=1 AND product_code in (1))

AND product_id IN (

SELECT product_id

FROM #fact_sales

WHERE (agent_id=2 AND product_code in (3))

AND product_id IN (

SELECT product_id

FROM #fact_sales

WHERE (agent_id=3 AND product_code in (2))

AND product_id IN (

SELECT product_id

FROM #fact_sales

WHERE (agent_id=4 AND product_code in (1))

)

-- alternative version

SELECT Product_ID

FROM (

SELECT f.Product_ID, agent_id, product_code

FROM #fact_sales f

WHERE (agent_id = 1 AND product_code = 1)

OR (agent_id = 2 AND product_code = 3)

OR (agent_id = 3 AND product_code = 2)

OR (agent_id = 4 AND product_code = 1)

GROUP BY f.Product_ID, agent_id, product_code

) d

GROUP BY Product_ID

HAVING COUNT(*) = 4

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 3

Getting error :

Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.

ChrisM@Work (3/20/2013)
Depending upon existing indexes, this could be a significant improvement:
DROP TABLE #fact_sales
CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))
INSERT INTO #fact_sales (agent_id, product_code, product_id)
VALUES
(1,1,'1'),
(1,1,'2'),
(1,1,'3'),
(1,1,'4'),
(1,1,'5'),
(2,3,'1'),
(2,3,'1'),
(3,2,'1'),
(3,2,'1'),
(4,1,'1'),
(4,1,'2')
CREATE CLUSTERED INDEX cx_Everything ON #fact_sales (Product_ID, agent_id, product_code)
-- existing version
SELECT product_id
FROM #fact_sales
WHERE (agent_id=1 AND product_code in (1))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=2 AND product_code in (3))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=3 AND product_code in (2))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=4 AND product_code in (1))
)
)
)
-- alternative version
SELECT Product_ID
FROM (
SELECT f.Product_ID, agent_id, product_code
FROM #fact_sales f
WHERE (agent_id = 1 AND product_code = 1)
OR (agent_id = 2 AND product_code = 3)
OR (agent_id = 3 AND product_code = 2)
OR (agent_id = 4 AND product_code = 1)
GROUP BY f.Product_ID, agent_id, product_code
) d
GROUP BY Product_ID
HAVING COUNT(*) = 4

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Shanmuga Raj (3/20/2013)
Getting error :
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.
ChrisM@Work (3/20/2013)
Depending upon existing indexes, this could be a significant improvement:
DROP TABLE #fact_sales
CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))
INSERT INTO #fact_sales (agent_id, product_code, product_id)
VALUES
(1,1,'1'),
(1,1,'2'),
(1,1,'3'),
(1,1,'4'),
(1,1,'5'),
(2,3,'1'),
(2,3,'1'),
(3,2,'1'),
(3,2,'1'),
(4,1,'1'),
(4,1,'2')
CREATE CLUSTERED INDEX cx_Everything ON #fact_sales (Product_ID, agent_id, product_code)
-- existing version
SELECT product_id
FROM #fact_sales
WHERE (agent_id=1 AND product_code in (1))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=2 AND product_code in (3))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=3 AND product_code in (2))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=4 AND product_code in (1))
)
)
)
-- alternative version
SELECT Product_ID
FROM (
SELECT f.Product_ID, agent_id, product_code
FROM #fact_sales f
WHERE (agent_id = 1 AND product_code = 1)
OR (agent_id = 2 AND product_code = 3)
OR (agent_id = 3 AND product_code = 2)
OR (agent_id = 4 AND product_code = 1)
GROUP BY f.Product_ID, agent_id, product_code
) d
GROUP BY Product_ID
HAVING COUNT(*) = 4

What query results in this error message? Your post is vague and unhelpful.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 5

when i run the below query i get error

Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.

-- alternative version

SELECT Product_ID

FROM (

SELECT f.Product_ID, agent_id, product_code

FROM #fact_sales f

WHERE (agent_id = 1 AND product_code = 1)

OR (agent_id = 2 AND product_code = 3)

OR (agent_id = 3 AND product_code = 2)

OR (agent_id = 4 AND product_code = 1)

GROUP BY f.Product_ID, agent_id, product_code

) d

GROUP BY Product_ID

HAVING COUNT(*) = 4

ChrisM@Work (3/20/2013)
Shanmuga Raj (3/20/2013)
Getting error :
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.
ChrisM@Work (3/20/2013)
Depending upon existing indexes, this could be a significant improvement:
DROP TABLE #fact_sales
CREATE TABLE #fact_sales (agent_id INT, product_code INT, product_id VARCHAR(5))
INSERT INTO #fact_sales (agent_id, product_code, product_id)
VALUES
(1,1,'1'),
(1,1,'2'),
(1,1,'3'),
(1,1,'4'),
(1,1,'5'),
(2,3,'1'),
(2,3,'1'),
(3,2,'1'),
(3,2,'1'),
(4,1,'1'),
(4,1,'2')
CREATE CLUSTERED INDEX cx_Everything ON #fact_sales (Product_ID, agent_id, product_code)
-- existing version
SELECT product_id
FROM #fact_sales
WHERE (agent_id=1 AND product_code in (1))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=2 AND product_code in (3))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=3 AND product_code in (2))
AND product_id IN (
SELECT product_id
FROM #fact_sales
WHERE (agent_id=4 AND product_code in (1))
)
)
)
-- alternative version
SELECT Product_ID
FROM (
SELECT f.Product_ID, agent_id, product_code
FROM #fact_sales f
WHERE (agent_id = 1 AND product_code = 1)
OR (agent_id = 2 AND product_code = 3)
OR (agent_id = 3 AND product_code = 2)
OR (agent_id = 4 AND product_code = 1)
GROUP BY f.Product_ID, agent_id, product_code
) d
GROUP BY Product_ID
HAVING COUNT(*) = 4
What query results in this error message? Your post is vague and unhelpful.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Post the ddl (the CREATE TABLE script) for table fact_sales.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 7

CREATE TABLE [dbo].[fact_sales](

[sales_id] [bigint] IDENTITY(1,1) NOT NULL,

[Product_ID] [bigint] NOT NULL,

[agent_id] [int] NOT NULL,

[product_code] [varchar](50) NULL,

[created_by] [int] NULL,

[created_date] [datetime] NOT NULL,

[modified_by] [int] NULL,

[modified_date] [datetime] NULL,

CONSTRAINT [PK_answer] PRIMARY KEY CLUSTERED

(

[sales_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Shanmuga Raj (3/20/2013)
when i run the below query i get error
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.
...

Ah, that's because I used INT constants for product_code, as you did. This query sets up the constants as character type:

SELECT Product_ID

FROM (

SELECT f.Product_ID, agent_id, product_code

FROM #fact_sales f

WHERE (agent_id = 1 AND product_code = '1') -- changed constant from INT to character

OR (agent_id = 2 AND product_code = '3')

OR (agent_id = 3 AND product_code = '2')

OR (agent_id = 4 AND product_code = '1')

GROUP BY f.Product_ID, agent_id, product_code

) d

GROUP BY Product_ID

HAVING COUNT(*) = 4

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 9

Thanks.. its working !

can you let me know why you have used

HAVING COUNT(*) = 4

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

foxxo (3/20/2013)
SELECT DISTINCT product_id FROM fact_sales WHERE (agent_id=1 AND product_code in (1)) OR (agent_id=2 AND product_code in (3)) OR (agent_id=3 AND product_code in (2))
OR (agent_id=4 AND product_code in (1))
AND & OR operators
http://www.w3schools.com/sql/sql_and_or.asp

I'm pretty sure the OP wants a return of only those things that meet all 4 pairs of conditions. By itself, OR just isn't going to do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Shanmuga Raj (3/20/2013)
Thanks.. its working !
can you let me know why you have used
HAVING COUNT(*) = 4

That's to make it so that only those product_ids that have all 4 distinct product condition pairs are returned.

So far, there are some good answers to this problem, especially Chris' most recent post. My question is, what are you going to do when the conditions change where you might need to meet fewer or more than 4 pairs of conditions? Is this a one off or something where the conditions need to be passed by a GUI or passed by something else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Jeff Moden (3/20/2013)
Shanmuga Raj (3/20/2013)
Thanks.. its working !
can you let me know why you have used
HAVING COUNT(*) = 4
That's to make it so that only those product_ids that have all 4 distinct product condition pairs are returned.
So far, there are some good answers to this problem, especially Chris' most recent post. My question is, what are you going to do when the conditions change where you might need to meet fewer or more than 4 pairs of conditions? Is this a one off or something where the conditions need to be passed by a GUI or passed by something else?

Thanks for picking this up, Jeff.

There's a solution to more than 4 pairs of conditions - depending on how the conditions are raised, as you suggest. Here it is;

;WITH IncludedAgentProducts AS (

SELECT DISTINCT *

FROM (VALUES

(1,'1'),

(2,'3'),

(3,'2'),

(4,'1')

) d (agent_id, product_code)

)

SELECT Product_ID

FROM (

SELECT f.Product_ID, f.agent_id, f.product_code

FROM #fact_sales f

INNER JOIN IncludedAgentProducts d

ON d.agent_id = f.agent_id

AND d.product_code = f.product_code

GROUP BY f.Product_ID, f.agent_id, f.product_code

) d

GROUP BY Product_ID

HAVING COUNT(*) = (SELECT COUNT(*) FROM IncludedAgentProducts)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden