ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,066 through 1,080 (of 10,143 total)

RE: Let the optimizer do it's thing -- wrong

Indianrock (12/9/2016)
Yes, yes and yes. And correct me if I'm wrong, but once the front end of a plan ( right side as viewed in an execution plan...
^{“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

December 9, 2016 at 8:39 am

#1917088
RE: Are the posted questions getting worse?

Brandie Tarvin (12/9/2016)
rodjkidd (12/9/2016)
ChrisM@Work (12/8/2016)
rodjkidd (12/7/2016)
Eirikur,
I'm about a 20 minute walk to the High Holborn Caffe 1882. Let me know if you still want to meet up today?
Chris, are you...
^{“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

December 9, 2016 at 7:51 am

#1917074
RE: Performance issue with nested query

rgp151 (12/8/2016)
Here is a plan where its just the query encapsulated in SELECT * FROM ()
Remember, if I run just the part inside the outer nesting it runs in under...
^{“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

December 9, 2016 at 7:45 am

#1917073
RE: Let the optimizer do it's thing -- wrong

"This query was written by a tool". ORM tools can only go so far and this query goes way beyond that. It's unwieldy, inefficient and difficult to engineer into 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

December 9, 2016 at 7:23 am

#1917060
RE: Parsing paired relationships.

You could accelerate it by saving off DedupedSet as a temp table and indexing it - a clustered index on column A, possibly.
^{“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

December 9, 2016 at 7:18 am

#1917057
RE: Parsing paired relationships.

Recursion would usually be used for this Richard.
Try the following:
IF 0 = 1 BEGIN; -- Knock up some sample data
WITH MyData (A, B) AS (
SELECT 'ProdA', 'ProdB' UNION ALL
SELECT 'ProdB',...
^{“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

December 9, 2016 at 5:11 am

#1917017
RE: Query Tunning

moosamca (12/9/2016)
Hi All,
COuld you please help me to tune the below query. I have attached the query plan of this query. Its going ont he nested loop and its cost...
^{“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

December 9, 2016 at 3:29 am

#1917001
RE: Groups and Islands problem

drew.allen (12/8/2016)
Peter Brinkhaus (12/8/2016)
You have to test it yourself if it performs better, but here's simplified alternative:
SELECT
Q.memid,
Q.EffectiveDate,
Q.termdate,
...
^{“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

December 9, 2016 at 2:32 am

#1916985
RE: SQL comma separated values and a loop

Thom A (12/8/2016)
...On the side note, I do get a LEFT SEMI JOIN when doing to following:
USE DevTestDB;
GO
CREATE TABLE Jobs (JOBID INT IDENTITY(1,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

December 9, 2016 at 2:07 am

#1916978
RE: Performance issue with nested query

rgp151 (12/8/2016)
Yeah, I know its a lot. My main question is just what would cause performance to degrade so much just going from:
SELECT
x,
y,
z
FROM
...
^{“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

December 8, 2016 at 9:50 am

#1916835
RE: Performance issue with nested query

rgp151 (12/8/2016)
Here is a plan where its just the query encapsulated in SELECT * FROM ()
Remember, if I run just the part inside the outer nesting it runs in under...
^{“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

December 8, 2016 at 9:38 am

#1916831
RE: SQL comma separated values and a loop

paul 69259 (12/8/2016)
Awesome thank you Chris, that seems to have done the trick for me.
Thanks for the feedback Paul, but it's really for Phil and Thom, who did all 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

December 8, 2016 at 7:47 am

#1916775
RE: SQL comma separated values and a loop

Phil Parkin (12/8/2016)
First thing I'd suggest is removing this line:
INNER JOIN dbo.Drops D ON J.JobID = D.JOBID
As 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

December 8, 2016 at 7:35 am

#1916770
RE: SQL comma separated values and a loop

Thom A (12/8/2016)
paul 69259 (12/8/2016)
it is something to do with that line
WHERE J.JOBID IN (SELECT sq.JOBID FROM dbo.Drops sq)
that it doesn't like
That statement should have very little effect...
^{“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

December 8, 2016 at 7:07 am

#1916760
RE: Performance issue with nested query

rgp151 (12/7/2016)
I have a large aggregation query on a large dataset that run forever and never return, but if I run the main part of the query by itself 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

December 8, 2016 at 6:35 am

#1916747

Viewing 15 posts - 1,066 through 1,080 (of 10,143 total)