SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


best optimized query for the requirement


best optimized query for the requirement

Author
Message
Shanmuga Raj
Shanmuga Raj
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
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)))))
Andrew G
Andrew G
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4087 Visits: 2282

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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41899 Visits: 20008
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



“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
Exploring Recursive CTEs by Example Dwain Camps
Shanmuga Raj
Shanmuga Raj
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
Getting error :[color=red]
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.[/color]


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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41899 Visits: 20008
Shanmuga Raj (3/20/2013)
Getting error :[color=red]
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.[/color]


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
Exploring Recursive CTEs by Example Dwain Camps
Shanmuga Raj
Shanmuga Raj
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
when i run the below query i get error
[color=red]Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.[/color]


-- 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 :[color=red]
Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.[/color]


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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41899 Visits: 20008
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
Exploring Recursive CTEs by Example Dwain Camps
Shanmuga Raj
Shanmuga Raj
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41899 Visits: 20008
Shanmuga Raj (3/20/2013)
when i run the below query i get error
[color=red]Conversion failed when converting the varchar value ' | | |0|0|0' to data type int.[/color]

...


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
Exploring Recursive CTEs by Example Dwain Camps
Shanmuga Raj
Shanmuga Raj
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 352
Thanks.. its working !
can you let me know why you have used
HAVING COUNT(*) = 4
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search