ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,371 through 2,385 (of 10,143 total)

RE: How to make actual execution plan better?

Tac11 (6/30/2015)
...
I didn't understand why execution plan suggesting me to create non-clustered index on this while the table already has a clustered index!!!
Then you need to read this article[/url].
^{“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

July 1, 2015 at 3:35 am

#1809404
RE: PatReplace8K

Alan.B (6/30/2015)
...Based on my testing, the function appears to perform a bit faster using CHECKSUM over ROW_NUMBER() instead of the conversion to BIGINT in the TOP clause. ...
So CHECKSUM over...
^{“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

July 1, 2015 at 1:58 am

#1809381
RE: Using a Recursive CTE to Generate a List

Paul White (6/30/2015)
ChrisM@Work (6/30/2015)
If you remember a year or four back when quite a few of us were messing around with rCTE's and figuring out what they were good for,...
^{“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 30, 2015 at 9:54 am

#1809196
RE: Using a Recursive CTE to Generate a List

Jeff Moden (6/30/2015)
ChrisM@Work (6/30/2015)
Paul White (6/28/2015)
...All this frequently confuses DBAs and developers who are used to tuning performance based solely on logical reads (not a school of thought I subscribe...
^{“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 30, 2015 at 8:31 am

#1809152
RE: PatReplace8K

Nice job, Alan.
One question: what's the CHECKSUM() for? It doesn't appear to change the result from ROW_NUMBER().
^{“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 30, 2015 at 7:22 am

#1809109
RE: Using a Recursive CTE to Generate a List

IdRatherCodeIt (6/29/2015)
I used both the cte and for xml to retrieve a grouped list of about 938 rows from a table with 3458 records as the source (filtered for my...
^{“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 30, 2015 at 6:54 am

#1809097
RE: Using a Recursive CTE to Generate a List

Paul White (6/28/2015)
...All this frequently confuses DBAs and developers who are used to tuning performance based solely on logical reads (not a school of thought I subscribe to by 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

June 30, 2015 at 6:53 am

#1809096
RE: Using a Recursive CTE to Generate a List

Paul White (6/28/2015)
The reason I often refer to logical reads of worktables (including spools) being measured in rows rather than pages is an aid to understanding for people who are...
^{“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 30, 2015 at 6:48 am

#1809093
RE: Using a Recursive CTE to Generate a List

Jeff Moden (6/27/2015)
I'm still working on it. Everything I come up with has had a hole in it.
Sorry it's been a while Jeff, crazy busy times. Next gig booked...
^{“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 30, 2015 at 6:43 am

#1809091
RE: Using a Recursive CTE to Generate a List

Ed Pollack (6/26/2015)
...
If their documentation is wrong...
It isn't wrong at all - it's just incomplete.
^{“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 26, 2015 at 9:54 am

#1808460
RE: Using a Recursive CTE to Generate a List

Jeff Moden (6/26/2015)
Ed Pollack (6/26/2015)
MSDB has pretty straightforward definitions for the data in question:
https://msdn.microsoft.com/en-us/library/ms184361.aspx
If their documentation is wrong, then I'd definitely like to see some sort of proof of that...
^{“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 26, 2015 at 9:53 am

#1808457
RE: Using a Recursive CTE to Generate a List

Jeff Moden (6/26/2015)
ChrisM@Work (6/26/2015)
Ed Pollack (6/26/2015)
This is an innovative way to generate a comma-delimited list, but performance is my main concern here. If you SET STATISTICS IO ON 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 26, 2015 at 8:50 am

#1808429
RE: Using a Recursive CTE to Generate a List

Ed Pollack (6/26/2015)
This is an innovative way to generate a comma-delimited list, but performance is my main concern here. If you SET STATISTICS IO ON and turn on your...
^{“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 26, 2015 at 6:57 am

#1808381
RE: Check to see if columns are NOT NULL in a record

-- Have you tried a computed column?
DROP TABLE #NameAndAddress
CREATE TABLE #NameAndAddress (
NameAndAddressID INT NOT NULL identity(1,1),
Firstname VARCHAR(20),
Lastname VARCHAR(20),
Address1 VARCHAR(50),
Address2 VARCHAR(50),
Address3 VARCHAR(50),
Address4 VARCHAR(50),
RowIsComplete AS CASE
WHEN Firstname IS NULL...
^{“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 25, 2015 at 9:33 am

#1808127
RE: Stairway to Advanced T-SQL Level 6: Creating Rows Of Data Using The UNPIVOT Operator

g.britton (6/24/2015)
Greg Larsen (4/27/2015)
Comments posted to this topic are about the item <A HREF="/articles/Stairway+Series/125504/">Stairway to Advanced T-SQL Level 6: Creating Rows Of Data Using The UNPIVOT Operator</A>
Consider using CROSS APPLY...
^{“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 25, 2015 at 7:45 am

#1808063

Viewing 15 posts - 2,371 through 2,385 (of 10,143 total)