ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,326 through 2,340 (of 10,143 total)

RE: Extra join is killing query

DavidDroog (7/22/2015)
Hi Guys
thanks for the replies.
...
@chrism-2 - Yes, it does look complicated and there may be a couple of unecessary joins, but it works fine without the addition of 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

July 23, 2015 at 1:43 am

#1814288
RE: Extra join is killing query

The original query appears to be "For each client, get all the products and all their colours". The other tables in the query could filter the output by geography but...
^{“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

July 22, 2015 at 2:18 am

#1814024
RE: Are the posted questions getting worse?

Sean Lange (7/16/2015)
SQLRNNR (7/16/2015)
Sean Lange (7/16/2015)
Another thing to do in Seattle...of course the NW is known for having lots of microbreweries but what often goes unmentioned is that many 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

July 17, 2015 at 1:47 am

#1813180
RE: Fastest way to retrieve the result

Or rather this:
-- This table holds account positions data that are appended to multiple times a day
-- what is the efficient way to retrieve the first set of positions...
^{“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

July 15, 2015 at 7:12 am

#1812610
RE: Fastest way to retrieve the result

-- Determine the most recent load_id, use it as a filter.
-- It will be about as fast as it can get without adding
-- additional indexes because load_id is the leading...
^{“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

July 15, 2015 at 6:56 am

#1812601
RE: Syntax Error IS NULL

Instead of updating the temp table, why not calculate the value when you populate it, like this:
SUM(DayCount) OVER(PARTITION BY Loss_State)
^{“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

July 15, 2015 at 1:46 am

#1812547
RE: Are the posted questions getting worse?

TomThomson (7/13/2015)
Lynn Pettis (7/13/2015)
OMG! How does Silver Spoon keep his job? Member of ssc for over 10 years and can't seem to do the simplest tasks without running 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

July 14, 2015 at 7:02 am

#1812294
RE: Please Help - trying to eliminate "used tempdb to spill data"

jghali (7/13/2015)
Thanks for your response...
1. the execution plans of the Row_number for:
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [UPC], [LocGroup] ORDER BY PriceLvl) FROM #Master_TAB
SELECT *, rn =...
^{“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

July 13, 2015 at 7:26 am

#1811956
RE: Please Help - trying to eliminate "used tempdb to spill data"

Firstly, ROW_NUMBER() doesn't necessarily invoke a sort. Compare the execution plan of these two queries:
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY [UPC], [LocGroup] ORDER BY PriceLvl) FROM #Master_TAB
SELECT *, rn...
^{“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

July 13, 2015 at 5:44 am

#1811925
RE: Please Help - trying to eliminate "used tempdb to spill data"

-- The outer query makes little sense, it only adds duplicate rows to a set which you're eliminating rows from
SELECT MT.A3+MT.A4 AS A34, MT.A3
-- ,M.*
FROM Master_TAB M
JOIN...
^{“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

July 10, 2015 at 9:34 am

#1811659
RE: How to shortening this sql code?

HerryMarkowitz (7/10/2015)
ChrisM@Work (7/10/2015)
HerryMarkowitz (7/10/2015)
I want INVENNO 101, 102, 103, 104, 105, 106, 107.
But I dont want to see 102, 103, 104, 105, 106 lines in the sql code.
I want 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

July 10, 2015 at 6:47 am

#1811593
RE: How to shortening this sql code?

HerryMarkowitz (7/10/2015)
I want INVENNO 101, 102, 103, 104, 105, 106, 107.
But I dont want to see 102, 103, 104, 105, 106 lines in the sql code.
I want a loop code...
^{“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

July 10, 2015 at 6:35 am

#1811587
RE: How to shortening this sql code?

HerryMarkowitz (7/10/2015)
I am so sorry but my English is not perfect.
Let me tell you what I understand;
A loop code is possible but not efficient.
Is that what you told?
The code 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

July 10, 2015 at 6:17 am

#1811580
RE: How to shortening this sql code?

HerryMarkowitz (7/10/2015)
Hi again,
Thanks for the codes.
But your codes are not what I want.
Maybe I am not able to explain my needs because I am very new with SQL coding.
Let me...
^{“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

July 10, 2015 at 5:26 am

#1811561
RE: How to shortening this sql code?

HerryMarkowitz (7/10/2015)
Hi kyagi.jo,
Thanks for re-organized that code.
But my main question is about how to shorten that code.
I mean I dont want to enter every line from INVENNO 101 to INVENNO...
^{“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

July 10, 2015 at 4:05 am

#1811537

Viewing 15 posts - 2,326 through 2,340 (of 10,143 total)