ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,036 through 4,050 (of 10,143 total)

RE: Ugly SQL Statement takes to long to run. I need speed up help

Your date arithmetic has two flaws which could cause your query to return incorrect results.
1.They overlap. The day6 upper bound is C.SCHED_DTTM <= GETDATE()+6 and the day7 lower bound is...
^{“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

September 4, 2013 at 7:07 am

#1647284
RE: Tally Tables

Hugo Kornelis (9/3/2013)
...- In the first plan, the NEWID() function is evaluated for each row. The results are then sorted, so that row numbers can be returned.
- In the second...
^{“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

September 4, 2013 at 3:41 am

#1647220
RE: Tally Tables

Toreador (9/4/2013)
ChrisM@Work (9/4/2013)
They don't yield the same result set using 2k8. The first query generates a different result set each time it's run.
I get the same result set every...
^{“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

September 4, 2013 at 2:03 am

#1647206
RE: Tally Tables

Rune Bivrin (9/3/2013)
Just look at the execution plan and tell me if there is no difference.
Or even try this, where NEWID() is completely eliminated from the second query.
-- 1
SELECT 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

September 4, 2013 at 1:32 am

#1647196
RE: How many columns in INCLUDED Index are 'Too Many' ???

The first plan is less than optimal; Grant Fritchey writes about "Reason for Early Termination" here[/url].
You've got a potentially redundant table in your FROM list - dbo.company_name -...
^{“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

September 3, 2013 at 9:00 am

#1647070
RE: Are the posted questions getting worse?

GilaMonster (9/3/2013)
Having fun preaching there Chris? :hehe:
Oi don't you start! :hehe:
^{“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

September 3, 2013 at 6:39 am

#1647008
RE: Please help on these error messages

What do you think this update statement should do?
UPDATE @Results SET
TQSDComment = SDAAFV.txtValue --AS TQSDComment
FROM ActivitiesAdditionalFieldsValues AS SDAAFV
LEFT JOIN ActivitiesAdditionalFields AS SDAAF
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

September 3, 2013 at 6:28 am

#1647000
RE: How many columns in INCLUDED Index are 'Too Many' ???

T.Ashish (8/27/2013)
Hi Chris,
Thanks again for your help.
I could not see any performance difference with/without function. I have pasted the function for you. Meanwhile I'm looking into third point you suggested.
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

September 3, 2013 at 6:23 am

#1646999
RE: Are the posted questions getting worse?

L' Eomot Inversé (9/3/2013)
Koen Verbeeck (9/3/2013)
ChrisM@Work (9/3/2013)
Jan Van der Eecken (9/3/2013)
SQLRNNR (9/2/2013)
Koen Verbeeck (9/1/2013)
GilaMonster (8/31/2013)
Just ....
Microsoft will no longer offer Masters and Architect level training rotations and will be retiring...
^{“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

September 3, 2013 at 6:20 am

#1646995
RE: sql query

Jeff Moden (9/2/2013)
ramya.usapp (9/1/2013)
Hello friends,
Please help me in writing a query!!
Question:
Write a query based off of the following data stored in the AccountingEntry table. The query needs to return...
^{“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

September 3, 2013 at 4:56 am

#1646969
RE: Updating using # table

Junglee_George (9/3/2013)
Lend me a helping hand rather than preaching.....
ChrisM@Work (9/3/2013)
...Start with your original query. Remove tables which don't contribute to your result set - including row count. Check if LEFT...
^{“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

September 3, 2013 at 4:46 am

#1646966
RE: Updating using # table

This isn't a specific problem, it's ordinary day to day TSQL development work. Start with your original query. Remove tables which don't contribute to your result set - including row...
^{“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

September 3, 2013 at 4:11 am

#1646948
RE: Updating using # table

Junglee_George (9/3/2013)
Please help me to modify the query so that temp table can be used for updating the various columns like
PhaseType, TQSDComment, TQPIComment, thus avoiding the left join using 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

September 3, 2013 at 3:33 am

#1646933
RE: Are the posted questions getting worse?

Jan Van der Eecken (9/3/2013)
SQLRNNR (9/2/2013)
Koen Verbeeck (9/1/2013)
GilaMonster (8/31/2013)
Just ....
Microsoft will no longer offer Masters and Architect level training rotations and will be retiring the Masters level certification exams as...
^{“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

September 3, 2013 at 1:15 am

#1646886
RE: Updating using # table

Junglee_George (9/3/2013)
How can I modify the query such that LEFT JOIN on activities, typeofactivities can be removed, so that query performance is good? This is my query:
...
What do you want...
^{“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

September 3, 2013 at 1:06 am

#1646881

Viewing 15 posts - 4,036 through 4,050 (of 10,143 total)