ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,396 through 1,410 (of 10,143 total)

RE: Date Range overlaps

J Livingston SQL (7/11/2016)
ChrisM@Work (7/11/2016)
Following on from this, I had a few hours to spare on Saturday morning to spend on JLS' test harness.
IBG's first query looks like this:
SELECT...
^{“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 11, 2016 at 6:45 am

#1889393
RE: Date Range overlaps

Jeff Moden (7/7/2016)
ChrisM@Work (7/7/2016)
You might find this version a little easier to follow than the IBG original and it only reads the data once, so it's quite a bit quicker:
;WITH...
^{“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 11, 2016 at 5:03 am

#1889377
RE: Date Range overlaps

Following on from this, I had a few hours to spare on Saturday morning to spend on JLS' test harness.
IBG's first query looks like this:
SELECT code, startdate AS ts,...
^{“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 11, 2016 at 4:31 am

#1889374
RE: Stored Procedure execution

Raghavender (7/8/2016)
ChrisM@Work (7/7/2016)
Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring...
^{“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 11, 2016 at 1:43 am

#1889345
RE: Date Range overlaps

That's awesome G - I've been in and out of meetings all day so haven't had a chance to play yet, but I bet there's some tweaking potential!
^{“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 8, 2016 at 9:18 am

#1889215
RE: How to Insert select result into Variable which returns more than one row

Sangeeth878787 (7/8/2016)
Hi,
I am trying to load select output result into variable, but I am not able to achieve either using SET or Select method.
Eg @
declare @v-2 varchar(max) =...
^{“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 8, 2016 at 3:15 am

#1889114
RE: Query performance change depending where condition date value

Luis Cazares (7/7/2016)
Nice Chris, I hope that this helps Eliseo.
I think that I understand what you're saying about the TOP filtering before the aggregate, even if the execution plan indicates...
^{“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 8, 2016 at 3:12 am

#1889113
RE: Stored Procedure execution

Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition...
^{“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 7, 2016 at 9:50 am

#1888998
RE: Stored Procedure execution

Raghavender (7/7/2016)
One more question:
Is substring() not compatible with SQL Server 2014 ?
It is compatible. Can you tell us more about the issue you have with it?
^{“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 7, 2016 at 9:36 am

#1888983
RE: Query performance change depending where condition date value

After a bit more fiddling with this, I can get stable and predictable behaviour using an index tailored for my specific query. Adjusting it for the OP's environment yields this:
CREATE...
^{“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 7, 2016 at 9:32 am

#1888980
RE: Query performance change depending where condition date value

This was using local data but I could share actual plans with you privately?
^{“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 7, 2016 at 8:01 am

#1888931
RE: Query performance change depending where condition date value

Luis Cazares (7/7/2016)
...The TOP 1000 is not helping because it needs to group the data before limiting the results....
That was my first thought too Luis so I set up 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

July 7, 2016 at 7:32 am

#1888910
RE: Date Range overlaps

You might find this version a little easier to follow than the IBG original and it only reads the data once, so it's quite a bit quicker:
;WITH DateSequence AS (
SELECT...
^{“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 7, 2016 at 4:49 am

#1888880
RE: Are the posted questions getting worse?

BWFC (7/6/2016)
ChrisM@Work (7/6/2016)
BWFC (7/6/2016)
Phil Parkin (7/5/2016)
tindog (7/5/2016)
Michael L John (7/5/2016)
Can anyone in the U.K. (or is it K minus U these days?) make a recommendation for technical training? We...
^{“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 6, 2016 at 3:03 am

#1888634
RE: query performance tuning

ekant_alone (7/5/2016)
yes this is expected. I need data in that particular range of hours in order to keep the result set small.
So your range might be several days long, capturing...
^{“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 6, 2016 at 2:19 am

#1888626

Viewing 15 posts - 1,396 through 1,410 (of 10,143 total)