ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,471 through 4,485 (of 10,143 total)

RE: help with datediff

While you're investigating JLS' questions, this is worth playing with:
SELECT
[DTstart date],
[DTend date],
SecondsDifference = DATEDIFF(second,[DTend date],[DTstart date]),
SecondsDifference = ABS(DATEDIFF(second,[DTstart date],[DTend date]))
FROM (
SELECT
[DTstart date] = CONVERT(DATETIME,STUFF(STUFF(CAST(d.[start date] AS VARCHAR(8)),3,0,':'),6,0,':'),114),
[DTend date]...
^{“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 24, 2013 at 6:34 am

#1626760
RE: Delete Operation on table with 60 Million records

Can you post the actual plan (not the estimated plan) for this batch, please?
-- Determine what is in the patch container
SELECT TOP(1000) DISTINCT M_SCNTYPE, M_FAMILY0, M_MLABEL0, M_SLABEL0, ContainerID = @TargetContainerID
INTO...
^{“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 24, 2013 at 4:32 am

#1626718
RE: Append characters in a sentence after certain length

Jan Van der Eecken (6/21/2013)
Now why does this InlineTally function look so familiar? 😉
Same reason as Feynman diagrams are now so familiar, Jan 😉
^{“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, 2013 at 2:31 am

#1626241
RE: convert int int to date

ashwinboinala (6/20/2013)
i am getting this value from sysjobhistroy table from msdb there its in int format if u can give me solution where i can filter that table thats fine
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

June 20, 2013 at 9:27 am

#1625981
RE: convert int int to date

You might need an input style for CONVERT:
;WITH Sampledata AS (
SELECT lastrundate = CAST(20130620 AS INT)
)
SELECT
lastrundate_INT = lastrundate,
lastrundate_CHAR8 = CAST(lastrundate AS CHAR(8)),
lastrundate_DATE = CONVERT(DATE, CAST(lastrundate AS CHAR(8)),112)
FROM Sampledata
^{“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, 2013 at 9:25 am

#1625977
RE: Merge Output combination for a cascaded update.

Sean Lange (6/20/2013)
Is there a question here?
Yes there is, but not up there 😉
^{“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, 2013 at 9:18 am

#1625967
RE: Append characters in a sentence after certain length

Venkata-433090 (6/19/2013)
Thanks for your response. Its kind of when the client retrieves the data.
again, the 32 character count should start from where the pipe was placed.
Shouldn't take too much...
^{“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, 2013 at 8:53 am

#1625952
RE: Usage of CTE - Trick with Dates

sagesmith (6/18/2013)
What I haven't seen is this "inverse relationship" i.e. where the rCTE fails to scale. Do you have an example?
The first article by Jeff Moden in your signature makes...
^{“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 18, 2013 at 9:12 am

#1625089
RE: Usage of CTE - Trick with Dates

whenriksen (6/18/2013)
If "Show Actual Execution Plan" is set prior to executing the code and then both the queries (the CURSOR based one and the recursive CTE based one) are executed,...
^{“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 18, 2013 at 8:53 am

#1625074
RE: Usage of CTE - Trick with Dates

sagesmith (6/18/2013)
ChrisM@Work (6/18/2013)
sagesmith (6/18/2013)
Please post the times each approach takes, this is the only basis for a conclusion. As others have already mentioned, in many scenarios the cursor far...
^{“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 18, 2013 at 7:55 am

#1625039
RE: Cleaning the cache for a Query.

T.Ashish (6/18/2013)
Dear All,
While testing a query when we run it second time, it uses cache so performs better then previous run.
...
^{“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 18, 2013 at 6:27 am

#1624986
RE: SQL to split row by date (split into multiple rows)

This appears to work:
SELECT
ID = ROW_NUMBER() OVER(ORDER BY MIN(DateRange)),
Employee, Job, [Workload],
StartDate = MIN(DateRange),
EndDate = MAX(DateRange)
FROM (
SELECT ID, Employee, Job, [Workload], DateRange,
Grouper = DENSE_RANK() over (order by...
^{“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 18, 2013 at 6:17 am

#1624983
RE: Usage of CTE - Trick with Dates

sagesmith (6/18/2013)
Please post the times each approach takes, this is the only basis for a conclusion. As others have already mentioned, in many scenarios the cursor far outperforms 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 18, 2013 at 6:13 am

#1624981
RE: Usage of CTE - Trick with Dates

Jonathan AC Roberts (6/18/2013)
I thought this looked a good candidate for CROSS APPLY:
insert into dbo.def (SeqNo, Date_Field, Payment)
SELECT A.Seqno, X.Date_Field, A.Payment
FROM dbo.abc A
CROSS APPLY (SELECT DATEADD(mm, T.N,...
^{“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 18, 2013 at 3:58 am

#1624931
RE: The Cascading (CROSS) APPLY

dwain.c (6/17/2013)
ChrisM@Work (6/17/2013)
Hi Amy
This runs about 4 times faster than the original against the sample data set.
SELECT
w.ID,
Wage = MIN(w.Wage),
Sector = MIN(w.Sector),
tw = MIN(a.tw)
FROM #WageData w
INNER...
^{“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 18, 2013 at 3:31 am

#1624924

Viewing 15 posts - 4,471 through 4,485 (of 10,143 total)