ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,221 through 2,235 (of 10,143 total)

RE: Huge difference between estimated rows and actual rows

coolchaitu (8/21/2015)
Dear Chris sir,
Openquery can tune/improve performance but the architect told not to replace linked server with openquery. This is because we are migrating to cloud and architect said that...
^{“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

August 21, 2015 at 6:41 am

#1821658
RE: Huge difference between estimated rows and actual rows

coolchaitu (8/21/2015)
Good morning,
There is a stored procedure. It uses linked server. As we will be migrating to amazon cloud, our architect instructed not to replace linked server with openquery. Please...
^{“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

August 21, 2015 at 5:44 am

#1821639
RE: how to overcome from the tempdb spill over ?

squvi.87 (8/20/2015)
ChrisM@Work (8/20/2015)
squvi.87 (8/20/2015)
replaced with the new spliter but it tooks more time
Did you try the code I posted? Don't be shy about asking for explanations and further assistance.
Yes I...
^{“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

August 21, 2015 at 4:30 am

#1821627
RE: Metrics Table or Performance Tuning

Good catch Scott. I'd write it like this which is more or less the same:
SELECT m.reportMonth,
m.reportYear,
ex.[service],
SUM(m.Vol) AS Vol,
SUM(m.Effort) AS effort
FROM (
SELECT reportMonth,
reportYear,
queueID,
SUM(volume) AS Vol,
SUM(cost) AS Effort
FROM [cts].[exception_Main] -- 14.5M rows...
^{“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

August 21, 2015 at 3:36 am

#1821623
RE: Are the posted questions getting worse?

Sean Lange (8/20/2015)
Luis Cazares (8/20/2015)
Lynn Pettis (8/19/2015)
Silver spoon, silver spoon, silver spoon, when are you going to admit you are in over your head and really need to find 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

August 21, 2015 at 2:05 am

#1821607
RE: OUTER JOIN Not Producing Non Existent Record with IS NULL

sgmunson (8/20/2015)
serviceaellis (8/20/2015)
sgmunson,
thank you! This is closer than ever! It's producing the results in the detail format ...
Like J Livingston SQL came up with EXCEPT it was in a crosstab...
^{“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

August 21, 2015 at 1:59 am

#1821606
RE: Fine Tuning Sub Queries

mufadalhaiderster (8/20/2015)
Thanks for all effort guys,
I am still getting an error message on the FROM, outer apply clause(Incorrect syntax near the keyword from)
OUTER APPLY (
SELECT
COUNT(*) AS FundedLastYear,
COUNT(CASE WHEN a.DateDetailDisposed...
^{“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

August 20, 2015 at 9:43 am

#1821453
RE: Metrics Table or Performance Tuning

-- create this index
CREATE INDEX ix_Helper ON [cts].[exception_Main]
(reportYear, reportMonth, exceptionDateTime, productArea)
INCLUDE (volume, cost, queueID, reportable)
-- run this query, capture the actual execution plan and post back
SELECT m.reportMonth
,m.reportYear
,ex.service
,sum(m.volume) 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

August 20, 2015 at 9:32 am

#1821447
RE: Fine Tuning Sub Queries

mufadalhaiderster (8/20/2015)
I apologize guys..I posted in the wrong section. I have R2 and it is giving me an error
'DATEFROMPARTS' is not a recognized built-in function name'
any other techniques 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

August 20, 2015 at 8:18 am

#1821418
RE: Fine Tuning Sub Queries

mufadalhaiderster (8/20/2015)
I apologize guys..I posted in the wrong section. I have R2 and it is giving me an error
'DATEFROMPARTS' is not a recognized built-in function name'
any other techniques 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

August 20, 2015 at 8:14 am

#1821414
RE: how to overcome from the tempdb spill over ?

squvi.87 (8/20/2015)
replaced with the new spliter but it tooks more time
Did you try the code I posted? Don't be shy about asking for explanations and further assistance.
^{“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

August 20, 2015 at 7:54 am

#1821398
RE: SQL Turn Row data to Column

Dee Dee-422077 (8/20/2015)
Hi Jeff,
Thanks for a quick reply.
The maximum price per itemid is 2.
Also the ItemID is dynamic
Thanks,
Dee
SELECT ItemID, ItemName,
Price1 = MIN(Price),
Price2 = MAX(Price)
FROM #TBL_SAMPLE_DATA
GROUP BY ItemID,...
^{“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

August 20, 2015 at 5:35 am

#1821341
RE: Help with T-Sql

maxlezious (8/20/2015)
Really hope I become as good as you soon. Would you recommend me any t-sql books or just practicing on daily basis?
I'm planning on buying these at the weekend:
Grant...
^{“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

August 20, 2015 at 5:27 am

#1821336
RE: Help with T-Sql

maxlezious (8/20/2015)
Absolute star, i am your fan now. Thank you again 🙂
Oh gosh :blush: I'm just a coder. You'll find this stuff as natural as counting on your fingers in...
^{“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

August 20, 2015 at 5:12 am

#1821330
RE: Help with T-Sql

-- Sure, just remove ActType and Points from the GROUP BY...
SELECT
h.[hours],
Activities = COUNT(a.ActType),
Points = SUM(x.points)
FROM #tblActivities a
CROSS APPLY (
SELECT
[hours] = CASE
WHEN a.ActType = 'Motion' THEN DATEPART(HOUR,...
^{“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

August 20, 2015 at 4:47 am

#1821324

Viewing 15 posts - 2,221 through 2,235 (of 10,143 total)