ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,051 through 4,065 (of 10,143 total)

RE: I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping

L' Eomot Inversé (8/30/2013)
The description od what is calculate says number of times a city is visited is calculated; the sample data and expected results are completely different from this;...
^{“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 2, 2013 at 1:36 am

#1646683
RE: Today's Random Word!

jasona.work (8/30/2013)
Daniel Bowlin (8/30/2013)
sing4you (8/29/2013)
Jersey Boy
The Boss
Springsteen
Sprightly
^{“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 30, 2013 at 7:41 am

#1646367
RE: I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping

Post your actual code too, please. With your pseudo code, it might provide enough information for folks to make a few guesses. If you're expecting good, solid, tested code, then...
^{“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 30, 2013 at 6:31 am

#1646330
RE: Query performance

Abu Dina (8/30/2013)
My bad, the latest actual execution plan is for match key type 13, it looks like I generated an actual execution plan for a different match key type...
^{“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 30, 2013 at 6:10 am

#1646318
RE: Query performance

Without the TOP operator, the earlier actual plan shows 234,990 rows returned. With the TOP operator you're returning nearly a million rows ... can you confirm these figures are correct?
^{“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 30, 2013 at 6:07 am

#1646315
RE: Query performance

Also, change the datatype of d.MatchKey to that of a.MatchKeyType08.
^{“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 30, 2013 at 5:39 am

#1646300
RE: Query performance

Abu Dina (8/30/2013)
Thanks for this Chris.
I've run my original code again to get the actual execution plan (attached below). Interestingly my original query ran in 15 seconds to produce 234990...
^{“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 30, 2013 at 4:56 am

#1646290
RE: Query performance

Without seeing the actual plan, I'd guess that the tvf is being applied to many more rows than it needs to be, i.e. before all of the available filters have...
^{“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 30, 2013 at 4:13 am

#1646282
RE: Wrong datatype for SP?

Junglee_George (8/30/2013)
Thanks Gail. But Chris hasn’t written the code for the splitter function there. I need that code.
Sorry about that. It's in this article[/url].
I recommend you read the article, 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

August 30, 2013 at 3:59 am

#1646276
RE: How to query same table with 2 WHERE clause onto 1 row

Here's another way which you might find a little more intuitive:
;WITH
q1 (min_value, min_t_stamp) AS (
SELECT min(value), min(t_stamp)
FROM @t
WHERE value = (SELECT min(value) FROM @t)
),
q2 (min_value, min_t_stamp) 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

August 30, 2013 at 3:45 am

#1646268
RE: Wrong datatype for SP?

Junglee_George (8/30/2013)
Hi
I achieved it through this way. The code below is working.
If any flaws are there in my approach, replies are welcome.
...
1. This expression
WHERE MC.isDeleted=0x0 AND charindex(MC.materialItemContainerCode,@containerCodes)<>0
isn't SARGable....
^{“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 30, 2013 at 3:16 am

#1646257
RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

boriskey (12/16/2012)
Jeff, thanks for the great articles (part 1 and part 2 and look forward to part3).
Is there a way to pick a random value (like a color) from 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 29, 2013 at 4:35 am

#1645908
RE: Wrong datatype for SP?

Junglee_George (8/28/2013)
I need a help to modify my query to give comma-separated values as SP parameter. Any help is appreciated. Thanks in advance.
-- change your procedure
ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport]
@containerCodes nvarchar(MAX)
AS
SELECT
MC.MaterialItemContainerCode,
MC.ReceptionDate,
C.clientName...
^{“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 29, 2013 at 1:52 am

#1645867
RE: Today's Random Word!

SQLRNNR (8/28/2013)
pork belly
Danny DeVito
^{“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 29, 2013 at 1:22 am

#1645856
RE: Difficult Sorting

busraker (8/28/2013)
Your solution would run much faster (my code must test every previous day in order to determine the proper sort order, and you rely on a case statement) 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

August 29, 2013 at 1:21 am

#1645855

Viewing 15 posts - 4,051 through 4,065 (of 10,143 total)