ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,041 through 5,055 (of 10,143 total)

RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/14/2013)
Mark-101232 (3/14/2013)
Eugene Elutin (3/14/2013)
"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number 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

March 14, 2013 at 4:01 am

#1596900
RE: Complex SQL QUERY with DateDIFF

Mark-101232 (3/14/2013)
Eugene Elutin (3/14/2013)
"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they...
^{“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

March 14, 2013 at 3:37 am

#1596887
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
Those logtimes look sequential to me - are you sure?
What do you mean by "sequential"? These LogTimes are for the same UserId, they are unique and they grow...
^{“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

March 14, 2013 at 2:37 am

#1596869
RE: Split a String

Steven Willis (3/13/2013)
DelimitedSplit8K is far from overkill. Why use a hammer when you have a nail gun?
+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

March 14, 2013 at 1:39 am

#1596852
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
Cause some log data can be a bit different.
Try this one:
SET DATEFORMAT dmy;
CREATE TABLE #Source (LogIndex int, UserID nvarchar (10), LogTime datetime, Tipo smallint);
CREATE TABLE #Temp (LogIndex 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

March 13, 2013 at 9:49 am

#1596596
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
ChrisM@Work (3/13/2013)
Eugene Elutin (3/13/2013)
Still running after 12 minutes.
Some one around was looking for CPU killer query... I need to cancel that one!
Hahahaha!
I didn't say it would be...
^{“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

March 13, 2013 at 9:24 am

#1596575
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
Still running after 12 minutes.
Some one around was looking for CPU killer query... I need to cancel that one!
Hahahaha!
I didn't say it would be faster, but 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

March 13, 2013 at 8:44 am

#1596551
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
Yep, that is right now.
And it does look less code. But...
Cause it's based on recursive CTE and hidden triangular join (via cross apply), it's performance very unlikely...
^{“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

March 13, 2013 at 7:20 am

#1596486
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
ChrisM@Work (3/13/2013)
Eugene Elutin (3/13/2013)
I'm sure you didn't mark rows to be "ok" for UserId 01552 correctly, please check carefully!
01552 is correct, 01551 is incorrect:
;WITH OrderedData 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

March 13, 2013 at 6:04 am

#1596454
RE: Complex SQL QUERY with DateDIFF

Eugene Elutin (3/13/2013)
I'm sure you didn't mark rows to be "ok" for UserId 01552 correctly, please check carefully!
01552 is correct, 01551 is incorrect:
;WITH OrderedData AS (
SELECT *, seq = 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

March 13, 2013 at 5:51 am

#1596451
RE: Cross Join Trick

SELECT m.*, Amount = ISNULL(a.Amt,0)
FROM #MAster m
LEFT JOIN #Amount a
ON a.YoA = m.YoA AND a.Currency = m.Currency
ORDER BY m.YoA, m.Currency
^{“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

March 13, 2013 at 2:37 am

#1596387
RE: Split a String

AndrewSQLDBA (3/12/2013)
I was actually looking for something else. That really did not help.
Andrew SQLDBA
What's the problem with it, Andrew? There is an alternative; I'm just curious.
^{“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

March 12, 2013 at 9:38 am

#1596094
RE: Looping without cursor not updating properly

TheSQLGuru (3/12/2013)
ChrisM@Work (3/12/2013)
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.
Thanks for the reminder, Kevin. I wouldn't say "never" however,...
^{“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

March 12, 2013 at 9:02 am

#1596072
RE: Looping without cursor not updating properly

Siobhan Perricone (3/12/2013)
Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. 🙂 I'm still poking at...
^{“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

March 12, 2013 at 8:36 am

#1596049
RE: Looping without cursor not updating properly

TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.
Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say...
^{“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

March 12, 2013 at 8:31 am

#1596045

Viewing 15 posts - 5,041 through 5,055 (of 10,143 total)