ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,486 through 7,500 (of 10,143 total)

RE: Is there any solution for reducing execution time of stored procedure

Looks to me like all the crazy looping is date arithmetic. Try this as a replacement for the entire stored procedure - don't forget to put appropriate values into 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

August 20, 2010 at 7:48 am

#1209661
RE: Feeding different parameters to a variable

drew.allen (8/19/2010)
Chris Morris-439714 (8/18/2010)
WHERE PartitionSize IN (1, Seq*2, Seq*2-1) -- < changed 3rd param here

Cheers
ChrisM
This makes it seem like 1 is a special case when it's actually an exemplar...
^{“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, 2010 at 3:13 am

#1209537
RE: Feeding different parameters to a variable

Sure. If you remove the filter and instead mark the selected rows in the SELECT, you can see exactly what's happening...
DROP TABLE #VW_AE_SUS
CREATE TABLE #VW_AE_SUS (
PurchCode VARCHAR(5),
Provcode VARCHAR(5),...
^{“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 19, 2010 at 5:58 am

#1209026
RE: Today's Random Word!

Steve Jones - Editor (8/18/2010)
I shall decline to post my word for the day
:w00t:
Similar to French Connection UK?
Sorry to hear about PASS, Steve.
^{“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 19, 2010 at 2:24 am

#1208932
RE: Feeding different parameters to a variable

Hi Richard
Try changing the innermost select
SELECT PurchCode = LEFT(PurchCode, 3),
...
^{“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 18, 2010 at 10:23 am

#1208609
RE: Feeding different parameters to a variable

Hi Richard
Your sample output values don't appear to exist in the sample data, however something like this should do the trick:
SELECT PurchCode, Provcode, Duration, Seq, PartitionSize
FROM ( -- final
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 18, 2010 at 8:27 am

#1208522
RE: SQL Execution Order with multiple Joins

You're welcome.
Jack's post and invaluable explanation has already found a use here 😎
^{“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 18, 2010 at 7:06 am

#1208456
RE: Using GO in EXEC (@command) sometimes causes error

Run this, look at the columnname in the top resultset 🙂
DECLARE @command VARCHAR(MAX)
SET @command = 'SELECT GETDATE()' + CHAR(10) + 'go' + char(10) + 'SELECT GETDATE()'
PRINT @command
exec (@command)
Use ; 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 18, 2010 at 5:47 am

#1208406
RE: SQL Execution Order with multiple Joins

Here's the link to Jack's post:
http://www.sqlservercentral.com/Forums/FindPost964392.aspx
The post points out that it's the order of the ON clauses which changes the effect of the query, rather than the order in which...
^{“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 18, 2010 at 2:16 am

#1208350
RE: SQL Execution Order with multiple Joins

Jack Corbett posted up a blog link recently which covered this, and the fact that the join sequence and the position of the ON clauses can have a profound 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

August 17, 2010 at 10:23 am

#1208022
RE: Today's Random Word!

mbricker-600840 (8/17/2010)
sing4you (8/17/2010)
Chris Morris-439714 (8/17/2010)
Ticky tack
"Little houses on the hillside..."
"And they all look just the same"
There really is a song?
^{“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 17, 2010 at 9:50 am

#1207992
RE: Feeding different parameters to a variable

Richard
You're welcome, come back if you have any questions.
Cheers
ChrisM
^{“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 17, 2010 at 9:41 am

#1207979
RE: replacing long running cursor

Jeff Moden (8/17/2010)
Oh, I see it coming...
Heh Jeff got that porkchop launcher loaded up for the first triangular join post? 😀
^{“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 17, 2010 at 9:16 am

#1207954
RE: Today's Random Word!

Ticky tack
^{“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 17, 2010 at 8:31 am

#1207914
RE: Feeding different parameters to a variable

-- sample data
DROP TABLE #Sample
CREATE TABLE #Sample (PurchCode VARCHAR(3), Provcode VARCHAR(5), ArrivalDate DATE, ArrivalTime VARCHAR(5), DepartureTime VARCHAR(8))
INSERT INTO #Sample
(PurchCode, Provcode, ArrivalDate, ArrivalTime, DepartureTime)
SELECT '5A3', 'RD130', '01/02/2010', '18:13', '20:30:00' UNION ALL
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 17, 2010 at 8:07 am

#1207892

Viewing 15 posts - 7,486 through 7,500 (of 10,143 total)