ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,076 through 3,090 (of 10,143 total)

RE: How this works?

vignesh.ms (7/24/2014)
Hi there ,
I got this code from web, I cant understand how it works
can anybody suggest me with links or explanations about the below used techniques.
.
.
.
An...
^{“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 24, 2014 at 8:30 am

#1732110
RE: How this works?

-- make a table with two rows, two columns
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
SELECT * FROM @t
GO
----------------------------------------------------------------------------------------------------
DECLARE @t TABLE(...
^{“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 24, 2014 at 8:29 am

#1732108
RE: SQL procedure Query Performance issue

mallikachowdhary 98955 (7/24/2014)
Thanks Craig and Chris for the reply,
I have run the query on both the servers will post the execution plan once it runs, normally when running the same...
^{“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 24, 2014 at 3:30 am

#1732043
RE: SQL procedure Query Performance issue

Compare the execution plans. Since the queries take so long to run, begin with the estimated execution plan. They will highlight differences between the two servers - say an index...
^{“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 24, 2014 at 2:15 am

#1732023
RE: Below Update statement is not working! any Idea why

Check how many rows match:
SELECT a.dataid, x.Value
FROM TableA a
CROSS APPLY (
SELECT TOP 1 t.Value
FROM #tmpcopylibrary t
WHERE t.dataid = a.dataid
ORDER BY (SELECT NULL)
) x
^{“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 24, 2014 at 2:11 am

#1732020
RE: Display Duplicates

TomThomson (7/23/2014)
Brandie Tarvin (7/23/2014)
ChrisM@Work (7/23/2014)
At a guess, you have 741 duplicated rows, 73 triplicated, and 1 quadruplicated.
Give the man a cookie for doing the actual math work! 😀
No, he didn't...
^{“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, 2014 at 8:31 am

#1731771
RE: Display Duplicates

At a guess, you have 741 duplicated rows, 73 triplicated, and 1 quadruplicated.
^{“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, 2014 at 6:41 am

#1731737
RE: IN / JOIN / EXISTS giving same performance in a query.

http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/[/url]
^{“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, 2014 at 6:31 am

#1731735
RE: Please help me debug this SQL

-- Some alternative date arithmetic
SELECT
o.Forecasted_Commencement_Renewal_Date__c,
CAST(RTRIM(CAST(MONTH(DATEADD(mm, 1, o.Forecasted_Commencement_Renewal_Date__c)) AS CHAR(2))) + '/01/' + CAST(YEAR(o.Forecasted_Commencement_Renewal_Date__c) AS CHAR(4)) AS DATETIME),
DATEADD(MONTH,DATEDIFF(MONTH,0,Forecasted_Commencement_Renewal_Date__c)+1,0),
CAST((CAST(YEAR(GETDATE()) AS CHAR(4)) + CASE WHEN LEN(CAST(MONTH(GETDATE()) AS CHAR(2))) < 2 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

July 23, 2014 at 5:57 am

#1731716
RE: Insert sequence number

wBob (7/23/2014)
I did trial that and honestly there is not a lot in it, with solution 1 (mine) slightly faster 5-10% on average at scale of 1 million. My...
^{“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, 2014 at 5:37 am

#1731708
RE: Difference in Time for same query

Your first post shows Q1 and Q2 to be identical. If they are not, and you're obfuscating the code, then:
1. Why are you expecting them to execute in the same...
^{“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, 2014 at 5:08 am

#1731694
RE: Insert sequence number

Lynn Pettis (7/22/2014)
ChrisM@home (7/22/2014)
@wBob, you might find a TOP() -limited IBG-style inline tally even more efficient.
Sort of like this:
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY...
^{“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, 2014 at 4:29 am

#1731688
RE: Index grows really fast!

murnilim9 (7/22/2014)
ChrisM@Work (7/22/2014)
-- change the index to support a nested loops join between rs and r
-- with seeks to r on id and residual predicate of RNT and Man:
create...
^{“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, 2014 at 1:54 am

#1731658
RE: Insert sequence number

-- Make some sample data
DROP TABLE #SampleTable
SELECT *
INTO #SampleTable
FROM (
SELECT TOP 17 [Claim number] = 111, [sequence number] = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM SYSCOLUMNS
UNION ALL
SELECT TOP 38 [Claim...
^{“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, 2014 at 8:25 am

#1731440
RE: Taking long time for a single table execution

ekkaldevi.naveen (7/22/2014)
Hi,
I am having a table "Test" with 70 columns. Out of them a composite primary key is defined with 12 columns.
There are around 4 million records in 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

July 22, 2014 at 7:25 am

#1731417

Viewing 15 posts - 3,076 through 3,090 (of 10,143 total)