ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,266 through 2,280 (of 10,143 total)

RE: Are the posted questions getting worse?

Luis Cazares (8/17/2015)
The amazing part is that this same poster was in charge of a 2005 to 2012 migration. :w00t:
It took seven years? Should've gone to Specsavers for a better...
^{“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, 2015 at 1:36 am

#1820632
RE: Are the posted questions getting worse?

Eirikur Eiriksson (8/14/2015)
Jeff Moden (8/14/2015)
Eirikur Eiriksson (8/14/2015)
Ed Wagner (8/14/2015)
Eirikur Eiriksson (8/14/2015)
Alvin Ramard (8/14/2015)
Eirikur Eiriksson (8/14/2015)
SQLRNNR (8/14/2015)
Eirikur Eiriksson (8/14/2015)
Sometimes one wonders what drives the responds when one points out that 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 17, 2015 at 1:35 am

#1820362
RE: Execution Plan "table Spool"

meerack11 (8/14/2015)
Hello friends
I found table spool(Lazy) in execution plan so it is better or not?
let me know if it is not good then how to avoid it?
It depends...
^{“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 14, 2015 at 10:14 am

#1820121
RE: Query performance

Check the definition of index [_dta_index_ EFT_AR_AP_Snapshot_11_13…] on table [EFT_AR_AP_Snapshot] is the same on both servers. This is based on comparing seek predicate and residual predicate between the two...
^{“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 14, 2015 at 9:49 am

#1820114
RE: Query performance

tfeuz (8/14/2015)
All;
Thank you for all your responses. As most of you alluded to, I went down the path of checking out the query plans and the indexes.
After ensuring that...
^{“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 14, 2015 at 8:18 am

#1820068
RE: Metrics Table or Performance Tuning

nick.latocha (8/14/2015)
Hi,
Fairly new to managing large datasets - we have a large table - 16m rows.
We load around 800k rows each month and store data on a monthly basis.
We...
^{“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 14, 2015 at 8:06 am

#1820061
RE: Null values with joins

Run this and you will see what I mean:
SELECT a.ID, b2.EMPID, Grp
FROM #a a
CROSS JOIN (
SELECT empid, Grp = ROW_NUMBER() OVER(ORDER BY MIN(ID)) FROM #b GROUP BY empid
) b1
LEFT 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

August 14, 2015 at 7:25 am

#1820042
RE: Null values with joins

Anamika (8/14/2015)
Hi,
I appreciate your response. I need little more info if I add some more records in B like
INSERT INTO #B VALUES (4,'E2')
INSERT INTO #B VALUES (5,'E2')
then 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 14, 2015 at 7:11 am

#1820035
RE: Null values with joins

SELECT
a.ID,
b2.EMPID
FROM #a a
CROSS JOIN (SELECT DISTINCT id FROM #b) b1
LEFT JOIN (
SELECT ID, EMPID, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EMPID)
FROM #b
) b2 ON...
^{“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 14, 2015 at 5:37 am

#1820008
RE: Where Statement - Case Statement

Informer30 (8/13/2015)
Thanks again Chris....building the knowledge....
Many Thanks
Any time, thanks for the feedback.
^{“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 13, 2015 at 8:43 am

#1819707
RE: Where Statement - Case Statement

Informer30 (8/13/2015)
That brilliant Chris...That has worked....
Many Thanks....
Ps. still intrigued why the case statement did not work....
Can't tell from your code, but probably because you weren't comparing your expression to anything:
(CASE...
^{“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 13, 2015 at 8:25 am

#1819691
RE: Where Statement - Case Statement

Informer30 (8/13/2015)
Thanks Chris - excellent code....really appreciate the quick turnaround....I was looking for a variable free way....sorry...?
Sorry, I didn't realise how new you are to SQL.
SELECT column1, DATENAME(weekday,column1)
FROM #table1
WHERE...
^{“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 13, 2015 at 8:05 am

#1819686
RE: Where Statement - Case Statement

-- Make some data to play with
DROP TABLE #table1
CREATE TABLE #table1 (column1 DATETIME)
INSERT INTO #table1 VALUES (GETDATE()-1),(GETDATE()-2),(GETDATE()-3),(GETDATE()-4),(GETDATE()-5),(GETDATE()-6),(GETDATE()-7),(GETDATE()-8)
/* Take a peek
SELECT column1, DATENAME(weekday,column1)
FROM #table1
*/
GO
-- Pretend it's monday
DECLARE @getdate-2 DATETIME = GETDATE()-3
DECLARE...
^{“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 13, 2015 at 6:12 am

#1819629
RE: Where Statement - Case Statement

Let's get this clear.
If today is Monday, then you want to return data for Thursday, Friday and Saturday.
If today is Tuesday, Wednesday, Thursday or Friday then you want...
^{“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 13, 2015 at 5:29 am

#1819615
RE: Calculating median over 2 columns

This article is worth a read too.
That's a strange median, as JLS points out.
^{“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 13, 2015 at 5:16 am

#1819610

Viewing 15 posts - 2,266 through 2,280 (of 10,143 total)