ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,211 through 6,225 (of 10,143 total)

RE: Aggregate Query question

dwain.c (6/22/2012)
Note that removing the check column as you have done does get you about a 10% speed improvement.
If you were right though, does that mean my version is 3125x...
^{“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

June 22, 2012 at 2:46 am

#1504387
RE: Aggregate Query question

dwain.c (6/22/2012)
I see you also removed CHARINDEX. However I can't verify your results. I get this running with n<=2 in the recursive leg:
-- Original
(115681 row(s) affected)
SQL Server...
^{“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

June 22, 2012 at 2:37 am

#1504384
RE: Aggregate Query question

dwain.c (6/22/2012)
...
Sorry, I don't get it. Are you talking my query here or yours?
Yours:
SELECT 1, Tuples = CAST(Prod AS VARCHAR(8000)), value
FROM @t
UNION ALL
SELECT n.n+1, t.Prod + ',' + n.Tuples,...
^{“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

June 22, 2012 at 2:02 am

#1504369
RE: Aggregate Query question

dwain.c (6/21/2012)
ChrisM@Work (6/21/2012)
Dwain's code, incidentally, is a flash of genius. It runs 2,500 times faster than mine, because it only works with the rows it has to.
Wow Chris! That's...
^{“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

June 22, 2012 at 1:45 am

#1504362
RE: Aggregate Query question

jeffem (6/21/2012)
... if order of results creates uniqueness (for example, "1 2 3" is distinct from "2 3 1"). If order of results does NOT create uniqueness, then you would...
^{“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

June 21, 2012 at 8:30 am

#1503967
RE: Aggregate Query question

jeffem (6/21/2012)
ChrisM@Work (6/21/2012)
David Webb-200187 (6/19/2012)
Hmmmm....
Let's say you had 50 rows, all with a quantity of 1. The target quantity was 5.
How would you want that brought back? All...
^{“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

June 21, 2012 at 7:36 am

#1503922
RE: Aggregate Query question

David Webb-200187 (6/19/2012)
Hmmmm....
Let's say you had 50 rows, all with a quantity of 1. The target quantity was 5.
How would you want that brought back? All the possible...
^{“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

June 21, 2012 at 6:13 am

#1503881
RE: Case With Group by Row Values into Column

farooq.hbs (6/21/2012)
Hey
My Metric Values returning two times Lyk

IsNUll(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY11,
IsNull(Sum(Case When Datepart(year,mdate)= 2012...
^{“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

June 21, 2012 at 1:41 am

#1503798
RE: Concatenate 4 Columns with Dynamic SQL

You don't need dynamic SQL for this:
SELECT NewColumn
= CAST(Col1 AS VARCHAR(10)) + ' - '
+ CAST(Col2 AS VARCHAR(10)) + ' - '
+ CAST(Col3 AS VARCHAR(10))...
^{“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

June 20, 2012 at 6:52 am

#1503373
RE: Concatenate 2 rows based on grouping

Since GENERAL_COMMODITY_CD table has a different cardinality to your result set, having multiple rows per BOOKING_ID, you will need to preaggregate it, either as a CROSS APPLY or as a...
^{“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

June 20, 2012 at 6:35 am

#1503366
RE: Concatenate 2 rows based on grouping

pwalter83 (6/20/2012)
ChrisM@Work (6/20/2012)
Are you seeing one row per BOOKING_ID in your output?
- here's where adequate sample data would have helped 🙂
yeah, I get only one row per BOOKING_ID and...
^{“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

June 20, 2012 at 5:51 am

#1503344
RE: Concatenate 2 rows based on grouping

Are you seeing one row per BOOKING_ID in your output?
- here's where adequate sample data would have helped 🙂
^{“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

June 20, 2012 at 5:33 am

#1503334
RE: Concatenate 2 rows based on grouping
pwalter83 (6/20/2012)
... I did the following but somehow it doesnt work. Could you please tell what I may be doing wrong:
---------------------------------
CROSS APPLY (
SELECT
=
STUFF(
(SELECT ', ' + mbc.GENERAL_COMMODITY_CD...
^{“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
June 20, 2012 at 5:18 am

#1503326
RE: Concatenate 2 rows based on grouping

Thanks Paul: try this...
SELECT
MB.BOOKING_ID,
BOOKING_NUM,
--------------------------------------------------------
a.[BOOKING EQUIPMENT],
--------------------------------------------------------
GENERAL_COMMODITY_CD + ',' AS GENERAL_COMMODITY_CD -- qualify this column with a table alias so others know which table it's from
FROM MG_BOOKING MB
LEFT JOIN (
SELECT
BOOKING_ID,...
^{“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

June 20, 2012 at 4:42 am

#1503314
RE: Concatenate 2 rows based on grouping

pwalter83 (6/20/2012)
... I have some other tables that are also being used in the query as well. Can that be taken into account as well ?
Thanks.
Of course. Can you post...
^{“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

June 20, 2012 at 2:11 am

#1503262

Viewing 15 posts - 6,211 through 6,225 (of 10,143 total)