Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

best optimized query for the requirement Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 1:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:38 AM
Points: 61, Visits: 319
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)))))
Post #1433023
Posted Wednesday, March 20, 2013 1:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:34 AM
Points: 1,143, Visits: 1,585
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
Post #1433045
Posted Wednesday, March 20, 2013 2:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1433062
Posted Wednesday, March 20, 2013 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:38 AM
Points: 61, Visits: 319
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

Post #1433067
Posted Wednesday, March 20, 2013 3:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1433069
Posted Wednesday, March 20, 2013 3:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:38 AM
Points: 61, Visits: 319
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.
Post #1433099
Posted Wednesday, March 20, 2013 3:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1433102
Posted Wednesday, March 20, 2013 4:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:38 AM
Points: 61, Visits: 319
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
Post #1433114
Posted Wednesday, March 20, 2013 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1433125
Posted Wednesday, March 20, 2013 5:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:38 AM
Points: 61, Visits: 319
Thanks.. its working !
can you let me know why you have used
HAVING COUNT(*) = 4
Post #1433150
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse