ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,821 through 2,835 (of 10,143 total)

RE: Rank duplicates, but only rows involved in duplicates

Try again:
Like Jeff I set up a scaled-up dataset to test against but ran out of time over the weekend to post it up. After reading Jeff's post I've added...
^{“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

October 27, 2014 at 8:51 am

#1754958
RE: Rank duplicates, but only rows involved in duplicates

There appears to be a severe problem with posting at present.
^{“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

October 27, 2014 at 7:31 am

#1754926
RE: Rank duplicates, but only rows involved in duplicates

Like Jeff I set up a scaled-up dataset to test against but ran out of time over the weekend to post it up. After reading Jeff's post I've added the...
^{“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

October 27, 2014 at 7:27 am

#1754922
RE: Rank duplicates, but only rows involved in duplicates

sqldriver (10/24/2014)
ChrisM@home (10/24/2014)
sqldriver (10/24/2014)
ChrisM@Work (10/24/2014)
-- Changes MERGE JOIN to NESTED LOOPS, loses sort required for ROW_NUMBER()
Would you mind explaining some more about this part?
Thanks
Sure. Have a good look at the...
^{“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

October 27, 2014 at 7:01 am

#1754915
RE: Derived table query

You're welcome:
--------------------------------------------------------------------------------------
-- Items
--------------------------------------------------------------------------------------
IF OBJECT_ID ('tempdb..#Items') IS NOT NULL DROP TABLE #Items
SELECT DISTINCT
ipcm.KeyDepot,
KeyVendor,
ipcm.KeyItem,
StockingUnitofMeasure,
LeadTime,
LotSize,
ipcm.MinimumBalance,
ipcm.PurchasingBuyerCode
,CASE WHEN BuyerCodes.PrimaryCode IS NULL THEN 'No Buyer Code Linked' ELSE BuyerCodes.Description END AS...
^{“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

October 24, 2014 at 7:58 am

#1754675
RE: Derived table query

So now you understand that if you were to include this date column in your result set, it would increase your output rowcount by quite a lot - which was...
^{“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

October 24, 2014 at 7:48 am

#1754666
RE: Derived table query

Thanks.
Here's that same query reduced even more so it's much easier to understand:
--------------------------------------------------------------------------------------
-- PlannedRequirement (cut down even more)
--------------------------------------------------------------------------------------
SELECT
KeyDepot = fpo.KeyDepotPlannedOrder,
fpo.KeyItem,
MIN_keydate = MIN(fpo.KeyDatePlannedRelease),
MAX_keydate = MAX(fpo.KeyDatePlannedRelease)
FROM BI1_DW_Fact_FirmPlannedOrders...
^{“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

October 24, 2014 at 7:39 am

#1754659
RE: Derived table query

Thanks. Can you check this one please? I'm interested in the row count compared with the original, and also the difference between the min and max dates. "The original" in...
^{“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

October 24, 2014 at 7:14 am

#1754642
RE: Derived table query

frdrckmitchell7 (10/24/2014)
dbo.BI1_view_Dim_ItemMaster IM = keyitem
BI1_DW_Dim_CalendarDefinition = keydate
Here's that query again as a reminder. I need to know which table the column "keyitem" comes from:
/*
dbo.BI1_view_Dim_ItemMaster IM = keyitem
BI1_DW_Dim_CalendarDefinition = keydate
*/
--------------------------------------------------------------------------------------
--...
^{“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

October 24, 2014 at 7:00 am

#1754630
RE: Adding an 'ORDER BY' clause removes part of a list.

Hi Ben
ben.brugman (10/23/2014)
First of all thanks,
Now I see I did muck up a bit, the temp table ##WW should have been ordered, see the code below. Probably lost that part...
^{“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

October 24, 2014 at 6:51 am

#1754624
RE: Derived table query

Which table contains column "keyitem" as referenced in the original query?
^{“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

October 24, 2014 at 6:42 am

#1754620
RE: Derived table query

Can you describe the result set you get from this query please? I'm interested in the row count compared with the PlannedRequirement query (they should be the same), and any...
^{“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

October 24, 2014 at 6:20 am

#1754616
RE: Derived table query

Excellent! Now we can work with a much smaller query than the original. You want to see keydate in the output of the PlannedRequirement query, correct?
^{“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

October 24, 2014 at 5:45 am

#1754611
RE: Derived table query

Excellent. Next step, can you test this modification of your query please?
--------------------------------------------------------------------------------------
-- Items
--------------------------------------------------------------------------------------
IF OBJECT_ID ('tempdb..#Items') IS NOT NULL DROP TABLE #Items
SELECT DISTINCT
ipcm.KeyDepot,
KeyVendor,
ipcm.KeyItem,
StockingUnitofMeasure,
LeadTime,
LotSize,
ipcm.MinimumBalance,
ipcm.PurchasingBuyerCode
,CASE WHEN BuyerCodes.PrimaryCode IS...
^{“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

October 24, 2014 at 5:21 am

#1754598
RE: Derived table query

frdrckmitchell7 (10/24/2014)
Hi Chris
Thanks in advance....
No problem. Can you confirm that you are using SQL Server 2008?
^{“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

October 24, 2014 at 4:10 am

#1754584

Viewing 15 posts - 2,821 through 2,835 (of 10,143 total)