March 21, 2013 at 2:15 pm
Hi,
SELECT FS.Bol#, FS.CustNum, FS.PostedDate
FROM dbo.FACT_SalesAnalysis FS where
ocr IN ('r') AND IsUsedForFACT <> 1
group by FS.CustNum, FS.Bol#,FS.PostedDate
having COUNT(*) >= 1
EXCEPT
SELECT FS.Bol#, FS.CustNum, FS.PostedDate
FROM dbo.FACTTAbleFS where
ocr IN ('c') AND IsUsedForFact <> 1
group by FS.CustNum, FS.Bol#,FS.PostedDate
having COUNT(*) >= 1
EXCEPT
SELECT FS.Bol#, FS.CustNum, FS.PostedDate
FROM dbo.FACTTable FS where
ocr IN ('o') AND IsUsedForFact <> 1
group by FS.CustNum, FS.Bol#,FS.PostedDate
having COUNT(*) >= 1
The above query works but it is really poor performance query. Any alternatives to this ?
SELECT FS.Bol#, FS.CustNum, FS.PostedDate
FROM dbo.FACT_SalesAnalysis FS where
ocr IN ('o') AND IsUsedForFACT <> 1
group by FS.CustNum, FS.Bol#,FS.PostedDate
having COUNT(*) >= 1
INTERSECT
SELECT FS.Bol#, FS.CustNum, FS.PostedDate
FROM dbo.FACTTAbleFS where
ocr IN ('r) AND IsUsedForFact <> 1
group by FS.CustNum, FS.Bol#,FS.PostedDate
having COUNT(*) >= 1
EXCEPT
SELECT FS.Bol#, FS.CustNum, FS.PostedDate
FROM dbo.FACTTable FS where
ocr IN ('c') AND IsUsedForFact <> 1
group by FS.CustNum, FS.Bol#,FS.PostedDate
having COUNT(*) >= 1
The above query works but it is really poor performance query. Any alternatives to this ?
March 21, 2013 at 2:21 pm
You are not new around here so you shouldn't be surprised that we need a bit more to go on. Once you post ddl (including indexes), sample data and desired output based on that sample data we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2013 at 2:44 pm
In this case Sean, since he's looking more for tuning help than code help, I'd request something a bit different.
As mentioned, we want to see the DDL for the source tables, but in particular we want to see the indexes that are built on them as well as the datatypes.
Also, I'd really rather work from the actual .sqlplan for optimization concerns. Please check the link in my sig for index/tuning help for what we'll really need to dig into this with you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 21, 2013 at 2:54 pm
Evil Kraig F (3/21/2013)
In this case Sean, since he's looking more for tuning help than code help, I'd request something a bit different.As mentioned, we want to see the DDL for the source tables, but in particular we want to see the indexes that are built on them as well as the datatypes.
Also, I'd really rather work from the actual .sqlplan for optimization concerns. Please check the link in my sig for index/tuning help for what we'll really need to dig into this with you.
I did mention indexes but yes the execution plan would also be a big help. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2013 at 3:06 pm
CREATE TABLE dbo.FACTTAble
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Bol# INT,
CustNum INT,
PostedDate DATE,
ocr VARCHAR(1),
IsUsedforFact BIT
)
INSERT INTO dbo.FACTTAble (Bol#,CustNum,PostedDate,ocr,IsUsedforFact)
SELECT 213,1111,'10/1/2012','r',0
UNION
SELECT 213,1111,'10/16/2012','c',0
UNION
SELECT 213,11121,'10/11/2012','o',0
UNION
SELECT 213,1111,'10/1/2012','r',0
UNION
SELECT 213,11121,'10/11/2012','r',0
UNION
SELECT 213,11121,'10/11/2012','c',0
First Query should return 213,1111,'10/1/2012','r
Second query should return 213,11121,'10/11/2012','o'
AND
213,11121,'10/11/2012','r'
March 21, 2013 at 3:08 pm
Indexes? Actual Execution Plan?
March 21, 2013 at 5:25 pm
Very little to go on ... but here's my best guess so far:
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'FACT_SalesAnalysis')
DROP TABLE FACT_SalesAnalysis
--
CREATE TABLE dbo.FACT_SalesAnalysis
(
Bol# INT,
CustNum INT,
PostedDate DATE,
ocr VARCHAR(1),
IsUsedforFact BIT
)
SET NOCOUNT ON
INSERT INTO dbo.FACT_SalesAnalysis (Bol#,CustNum,PostedDate,ocr,IsUsedforFact)
SELECT 213,1111,'10/1/2012','r',0
UNION
SELECT 213,1111,'10/16/2012','c',0
UNION
SELECT 213,11121,'10/11/2012','o',0
UNION
SELECT 213,1111,'10/1/2012','r',0
UNION
SELECT 213,11121,'10/11/2012','r',0
UNION
SELECT 213,11121,'10/11/2012','c',0
SET NOCOUNT OFF
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'FACTTable')
DROP TABLE FACTTable
--
CREATE TABLE dbo.FACTTAble
(
Bol# INT,
CustNum INT,
PostedDate DATE,
ocr VARCHAR(1),
IsUsedforFact BIT
)
SET NOCOUNT ON
INSERT INTO dbo.FACTTAble (Bol#,CustNum,PostedDate,ocr,IsUsedforFact)
SELECT 213,1111,'10/1/2012','r',0
UNION
SELECT 213,1111,'10/16/2012','c',0
UNION
SELECT 213,11121,'10/11/2012','o',0
UNION
SELECT 213,1111,'10/1/2012','r',0
UNION
SELECT 213,11121,'10/11/2012','r',0
UNION
SELECT 213,11121,'10/11/2012','c',0
SET NOCOUNT OFF
--drop temp tables------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#FACT_SalesAnalysis') IS NOT NULL
DROP TABLE #FACT_SalesAnalysis
IF OBJECT_ID('tempdb..#FACTTable') IS NOT NULL
DROP TABLE #FACTTable
--create temp tables----------------------------------------------------------------------------------------------------
SET NOCOUNT ON
SELECT
FS.CustNum, FS.Bol#, FS.PostedDate, FS.ocr
INTO #Fact_SalesAnalysis
FROM dbo.FACT_SalesAnalysis FS
WHERE
0 = 1
CREATE CLUSTERED INDEX Fact_SalesAnalysis__CL ON #Fact_SalesAnalysis ( CustNum, Bol#, PostedDate, ocr )
--
SELECT
FT.CustNum, FT.Bol#, FT.PostedDate, FT.ocr
INTO #FACTTable
FROM dbo.FACTTable FT
WHERE
0 = 1
CREATE CLUSTERED INDEX FACTTable__CL ON #FACTTable ( CustNum, Bol#, PostedDate, ocr )
SET NOCOUNT OFF
--load temp tables------------------------------------------------------------------------------------------------------
INSERT INTO #Fact_SalesAnalysis
SELECT DISTINCT
FS.CustNum, FS.Bol#, FS.PostedDate, FS.ocr
FROM dbo.FACT_SalesAnalysis FS
WHERE
FS.ocr IN ('o', 'r') AND
FS.IsUsedForFACT <> 1
ORDER BY
FS.CustNum, FS.Bol#, FS.PostedDate, FS.ocr
INSERT INTO #FACTTable
SELECT DISTINCT
FT.CustNum, FT.Bol#, FT.PostedDate, FT.ocr
FROM dbo.FACTTAble FT
WHERE
FT.ocr IN ('c', 'o', 'r') AND
FT.IsUsedForFACT <> 1
ORDER BY
FT.CustNum, FT.Bol#, FT.PostedDate, FT.ocr
--do table comparisons--------------------------------------------------------------------------------------------------
--comparison1
SELECT FS.Bol#, FS.CustNum, FS.PostedDate, FS.ocr
FROM #FACT_SalesAnalysis FS
LEFT OUTER JOIN #FACTTable FT ON
FT.CustNum = FS.CustNum AND
FT.Bol# = FS.Bol# AND
FT.PostedDate = FS.PostedDate AND
FT.ocr IN ( 'c', 'o' )
WHERE
FS.ocr = 'R' AND
FT.CustNum IS NULL
--comparison2
SELECT
FS.Bol#, FS.CustNum, FS.PostedDate, FS.ocr
FROM #FACT_SalesAnalysis FS
INNER JOIN #FACTTable FT ON
FT.CustNum = FS.CustNum AND
FT.Bol# = FS.Bol# AND
FT.PostedDate = FS.PostedDate AND
FT.ocr IN ( 'r' )
LEFT OUTER JOIN #FACTTable FT2 ON
FT2.CustNum = FS.CustNum AND
FT2.Bol# = FS.Bol# AND
FT2.PostedDate = FS.PostedDate AND
FT2.ocr IN ( 'c' )
WHERE
FS.ocr = 'o' AND
FT2.CustNum IS NULL
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply