ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,276 through 7,290 (of 10,143 total)

RE: Optimise multiple joins between two tables

chris.king (9/28/2010)
Chris Morris-439714 (9/28/2010)
If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table...
^{“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

September 28, 2010 at 6:13 am

#1228279
RE: Optimise multiple joins between two tables

If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times...
^{“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

September 28, 2010 at 4:36 am

#1228238
RE: Trying to optimize performance of query having Hash Matches & Agreegate

Jaya, I reckon the most cost-effective way to approach your problem at this point in time is to see how each of your queries performs when you sub in 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

September 28, 2010 at 2:59 am

#1228205
RE: Are the posted questions getting worse?

Alvin Ramard (9/27/2010)
WayneS (9/27/2010)
jcrawf02 (9/27/2010)
I of course immediately subscribed to your feed
Okay, stupid question time. How do you subscribe to a blog feed? Are any special programs required?
Since you said...
^{“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

September 28, 2010 at 2:36 am

#1228197
RE: Need to split and combine some data in a query

Brandie Tarvin (9/27/2010)
Kit G (9/27/2010)
The part makes my head hurt is the joining the table to itself. Know of any articles about that which might help make my head...
^{“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

September 28, 2010 at 2:28 am

#1228192
RE: VERY strange behaviour with invalid subquery

Brian McGee-355525 (9/27/2010)
MY_TABLE_2 does not have any rows with OTHER_FIELD = 'VALUE'. ...
Check that this is indeed the case. Using 2k8R1, the outer query returns 20 rows as if...
^{“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

September 27, 2010 at 9:23 am

#1227771
RE: VERY strange behaviour with invalid subquery

krishnakumar-293737 (9/27/2010)
Select top 20 * from Database1..MY_TABLE (nolock)
Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')
Order By MY_COL desc
Now....the subquery is invalid, there is no column THE_ID on...
^{“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

September 27, 2010 at 9:17 am

#1227767
RE: VERY strange behaviour with invalid subquery

steveb. (9/27/2010)
I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.
It definetly throws an...
^{“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

September 27, 2010 at 8:25 am

#1227714
RE: Need to split and combine some data in a query

Brandie Tarvin (9/27/2010)
Chris, your code doesn't do what he wants Menu1 and Menu2 to do.
Also, you forgot to remove the # sign from your table name. Since he's a newbie...
^{“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

September 27, 2010 at 8:08 am

#1227700
RE: Eliminating Duplicate Rows using The PARTITION BY clause

hugh.hemington (9/26/2010)
...I'm trying to apply this to a situation where I need the first duplicate record, but I need to return the name and address, while partitioning only on 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

September 27, 2010 at 7:19 am

#1227677
RE: Today's Random Word!

Fat freakin' Freddy's Cat.
^{“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

September 27, 2010 at 7:00 am

#1227666
RE: Today's Random Word!

Hiber-freakin'-nate
^{“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

September 27, 2010 at 6:46 am

#1227653
RE: VERY strange behaviour with invalid subquery

Is there a CTE in here anywhere?
If there is, then this article is probably worth a read:
http://www.kbalertz.com/2322209/Incorrect-results-parentheses-around-SELECT-statement-common-table-expression-Server.aspx
Edit: found the link.
^{“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

September 27, 2010 at 6:43 am

#1227649
RE: Need to split and combine some data in a query

And another one:
;WITH OrderedPartitionedData AS
(SELECT RowID = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY EventDateTime), *
FROM #DataTest)
SELECT dy1.venue, dy1.address1, dy1.city, dy1.[state], dy1.zip,
EDate1 = CONVERT(VARCHAR, dy1.eventdatetime, 101),
Edate2...
^{“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

September 27, 2010 at 6:42 am

#1227648
RE: Sum Up the value accodring to the Department Structure.

Here's a simple solution which assumes only three levels in the hierarchy, as in your sample data. Note how the sample data has been set up using a script:
DROP TABLE...
^{“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

September 27, 2010 at 5:12 am

#1227613

Viewing 15 posts - 7,276 through 7,290 (of 10,143 total)