ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,556 through 3,570 (of 10,143 total)

RE: Are the posted questions getting worse?

Are you working in Maidenhead, Rod?
^{“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

March 6, 2014 at 4:44 am

#1694625
RE: Windowed Function in Where Clause

dwain.c (3/6/2014)
OK thanks. My head was spinning like Linda Blair's in The Exorcist trying to work out the possibilities. 😛
My head was spinning like Dwain Camps's in a sweetshop...
^{“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

March 6, 2014 at 4:34 am

#1694614
RE: Calculate and return the previous Date at 18:00 Hours

dwain.c (3/4/2014)
ChrisM@Work (3/4/2014)
SELECT DATEDIFF(dd,0,GETDATE())-1 SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)
SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))
Use q3. q1 and q2 help explain the algorithm.
I can do it with just two date function calls:
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,'18:00')
I can also name that tune 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

March 6, 2014 at 2:11 am

#1694571
RE: Parallel Inserts

This is an estimated parallel plan - estimating over 600 million rows output.
Are your stats up to date?
Any chance of an actual plan please? - just the SELECT. Ensure your...
^{“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

March 5, 2014 at 9:27 am

#1694374
RE: Parallel Inserts

I don't see the full plan when I load it here, it gets chopped at this point:
INNER JOIN Database_107.dbo.tblDatabase_107ExchangeRate AS XR WITH (NOLOCK)
ON O.Column_39 = XR.XRateCurrID
AND XR.XRateTypeID = 1
AND XR.XrateColumn_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

March 5, 2014 at 9:14 am

#1694365
RE: Parallel Inserts

IIRC SQL Sentry Plan Explorer has an "obfuscation" option designed with you in mind, Pete. It would help us a ton if you can do 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

March 5, 2014 at 6:56 am

#1694279
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column...
^{“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

March 5, 2014 at 6:54 am

#1694278
RE: Parallel Inserts

Can you post the estimated execution plan as a .sqlplan attachment please? And when you get the opportunity, the actual plan too.
^{“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

March 5, 2014 at 6:35 am

#1694268
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still shows...
^{“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

March 5, 2014 at 6:23 am

#1694259
RE: Is cascade delete Slow

SSISDB incorporates cascaded deletes and MS release it without including indexes for the fk's in the child tables. As a result, heaps of people (we don't have clustered indexes) saw...
^{“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

March 5, 2014 at 5:37 am

#1694239
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
GilaMonster (3/5/2014)
A column cannot be present in an index twice.
Yes I know but...
Not 'cannot be specified twice', actual 'cannot be present twice', so precluding SQL putting 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

March 5, 2014 at 5:32 am

#1694236
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
- The size stats of the two ordinary indexes is virtually the same.
Exactly.
A column cannot be present in an index twice.
Yes I know but...
If a column is...
^{“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

March 5, 2014 at 4:16 am

#1694222
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/5/2014)
CREATE TABLE ReallyStupidTable (
ID CHAR(100),
OtherID INT
)
GO
CREATE UNIQUE CLUSTERED INDEX idx_Silly ON ReallyStupidTable (ID)
GO
INSERT INTO ReallyStupidTable (ID, OtherID)
SELECT ANumber, ANumber FROM (
SELECT TOP (5000000) ROW_NUMBER() OVER (ORDER...
^{“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

March 5, 2014 at 3:59 am

#1694217
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/5/2014)
ChrisM@Work (3/5/2014)
If a multi-column clustered index includes a GUID, a nonclustered index which incorporates that same column either as a key or include column is going to be...
^{“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

March 5, 2014 at 3:28 am

#1694208
RE: Query of 100 mil rows with multiple parameters

GilaMonster (3/4/2014)
ChrisM@home (3/4/2014)
Less rows per page because of data duplication doesn't come without cost.
What data duplication?
There's always some overhead and someone always has to do the sums, calculate the cost-benefit.
Not...
^{“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

March 5, 2014 at 1:43 am

#1694181

Viewing 15 posts - 3,556 through 3,570 (of 10,143 total)