ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,561 through 1,575 (of 10,143 total)

RE: Translate function for SQL Server

Alan.B (6/1/2016)
ChrisM@Work (6/1/2016)
h.tobisch (6/1/2016)
everything fine, but what's that checksum for ?
Currently the fastest known method for converting a bigint to an int.
I learned this technique from Eirikur Eiriksson. One of...
^{“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 2, 2016 at 2:02 am

#1882736
RE: Split two delimited strings in table

Evolution at work: here's Alan's excellent solution tweaked to read the source table only once.
;WITH _Tally (n) AS (
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))...
^{“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 2, 2016 at 1:58 am

#1882734
RE: Translate function for SQL Server

Nice job Alan.
A few years ago DC and I played with numerous variations of this method as a spinoff of the PatternSplit project. One of the most promising methods 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

June 1, 2016 at 8:57 am

#1882507
RE: Translate function for SQL Server

h.tobisch (6/1/2016)
everything fine, but what's that checksum for ?
Currently the fastest known method for converting a bigint to an int.
^{“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 1, 2016 at 8:53 am

#1882506
RE: Query Designer vs Query Window Speed

Igor Micev (6/1/2016)
Hi,
There are differences in the execution plans: Degree of parallelism, Memory Grant, Set options.
Other than arithabort, the remaining differences - as far as I can see - 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 1, 2016 at 8:50 am

#1882501
RE: Split two delimited strings in table

J Livingston SQL (6/1/2016)
mikes84 (6/1/2016)
.....using a method that does not require a Tally table, CLR, or any functions.
any reason why no Tally or functions?
and does this exclusion apply to inline...
^{“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 1, 2016 at 6:59 am

#1882432
RE: T Sql Query

Your script generates an error.
Text data should be delimited with single quotes, columns should be delimited with commas. Each row should be delimited with ( and ), and you...
^{“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 1, 2016 at 6:54 am

#1882429
RE: require Optimize the sql query

TheSQLGuru (5/31/2016)
Has anyone asked for an estimated execution plan yet? What about table create scripts (complete with all keys and indexes)? Perhaps there is an unintended cartesian to be found,...
^{“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 1, 2016 at 1:39 am

#1882348
RE: What is SQL Fertilization any links?

Lowell (5/31/2016)
ChrisM@Work (5/31/2016)
Google "SQL Fertilization" with the quotes and most of the links appear to relate to a single job advert, something to do with Broadridge. Best guess? A dopey...
^{“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

May 31, 2016 at 10:14 am

#1882197
RE: What is SQL Fertilization any links?

Google "SQL Fertilization" with the quotes and most of the links appear to relate to a single job advert, something to do with Broadridge. Best guess? A dopey recruiter has...
^{“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

May 31, 2016 at 9:59 am

#1882188
RE: require Optimize the sql query

Can you post an estimated execution plan for this query please, as a .sqlplan file attachment:
SELECT bo.MedRecReviewID, mrr.MedRecReviewDetailID
INTO #MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK)...
^{“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

May 31, 2016 at 4:14 am

#1882108
RE: require Optimize the sql query

Focus your attention first on constructing something which can be used as a reference query – 24 hours is too long to wait for two queries to run to see...
^{“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

May 31, 2016 at 3:40 am

#1882097
RE: require Optimize the sql query

Can you confirm that these two queries are logically the same apart from the DISTINCT?
SELECT DISTINCT bo.MedRecReviewID
FROM [CPROD1].dbo.TABLE_A bo WITH (NOLOCK)
INNER JOIN [CPROD1].[dbo].[TABLE_B] mrr WITH (NOLOCK) 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

May 31, 2016 at 2:58 am

#1882082
RE: Need help on query performance

jc85 (5/25/2016)
How should i post the table with data on this forum?
How many rows do you need for ann_events_Tech_Details table?
ChrisM@Work (5/25/2016)
Ideally you would do this by posting a script to...
^{“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

May 25, 2016 at 10:30 am

#1881381
RE: Need help on query performance

jc85 (5/25/2016)
ChrisM@Work (5/25/2016)
Sorry my bad, missed the correlation:
SELECT
[date] = CAST(b.start_time AS DATE),
[report_item] = (b.[source] + '-' + 'a.report_item'),
[EN] = SUM(CASE WHEN b.lang = 'EN' THEN 1 ELSE 0...
^{“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

May 25, 2016 at 9:13 am

#1881354

Viewing 15 posts - 1,561 through 1,575 (of 10,143 total)