ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,381 through 1,395 (of 10,143 total)

RE: Find "Consecutive" records based on available dates in another table

jtrask (7/13/2016)
Of course. It all seems obvious once someone spells it out for me.
I like the idea of doing it on the fly. There's no point in having...
^{“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 14, 2016 at 2:48 am

#1890018
RE: Are the posted questions getting worse?

SQLRNNR (7/13/2016)
ChrisM@Work (6/29/2016)
tindog (6/29/2016)
ChrisM@Work (6/29/2016)
tindog (6/29/2016)
ChrisM@Work (6/29/2016)
tindog (6/29/2016)
Now Wales are after independence from England to 'safeguard' their future...despite the fact that they also voted in favour of Brexit :w00t:
The Welsh...
^{“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 14, 2016 at 2:10 am

#1890015
RE: A problem of concurrency on a small table

Is the UPDATE always followed by a SELECT of the new values?
UPDATE [cpwarn] set [autonum] = [autonum]+@1
WHERE [tablecode]=@2
SELECT autonum, tablecode FROM cpwarn WITH (NOLOCK)
WHERE tablecode='prog\DONUMREG\''001''\''2016 '''...
^{“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 13, 2016 at 5:35 am

#1889814
RE: A problem of concurrency on a small table

paolobianchi (7/13/2016)
voilà 🙂
thank you
Paolo
Thanks - but these are estimated plans. If possible can you post up actual plans? An actual plan of the SELECT is straightforward, use the query from...
^{“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 13, 2016 at 5:12 am

#1889803
RE: aggregate function in subquery

Pro100 (7/13/2016)
Yea, i know what SQL tells me but I post it here cause i thought someone will help me rewrite it.
Post the whole query.
^{“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 13, 2016 at 4:14 am

#1889793
RE: A problem of concurrency on a small table

Capture actual execution plans for each operation against the table, and post them as .sqlplan file attachments.
^{“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 13, 2016 at 4:10 am

#1889792
RE: Subquery Question

SQLPain (7/12/2016)
I took out the distinct.
If somebody else could also look into it that would be great
This should be close:
SELECT --DISTINCT
x.[Facilitator],
[Funded Apps] = SUM(CASE WHEN y.[Status] = 'Funded'...
^{“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 13, 2016 at 3:00 am

#1889783
RE: Subquery Question

SQLPain (7/12/2016)
I took out the distinct.
If somebody else could also look into it that would be great
Sure. There's a ton of redundant joins and stuff in it but something...
^{“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 13, 2016 at 2:29 am

#1889776
RE: Select and select into calulation produces Different result ??? HELP

rmurray 17656 (7/13/2016)
I have a Calculation in a query that is producing two different results. when used in a query statement the result is 160.442873, However when I use...
^{“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 13, 2016 at 1:35 am

#1889767
RE: Date Range overlaps

J Livingston SQL (7/11/2016)
ChrisM@Work (7/11/2016)
Assuming you're in a playpen, CHECKPOINT then dbcc dropcleanbuffers. You might free up a few kb 😀
I'll tell you what I'll do Chris......as its only 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

July 11, 2016 at 10:11 am

#1889461
RE: Date Range overlaps

Assuming you're in a playpen, CHECKPOINT then dbcc dropcleanbuffers. You might free up a few kb 😀
^{“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 11, 2016 at 10:02 am

#1889459
RE: Date Range overlaps

Eliminated that pesky spill:
== JLS1 ==========================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
Table 'testtable'. Scan count 34, logical reads...
^{“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 11, 2016 at 9:55 am

#1889458
RE: Date Range overlaps

J Livingston SQL (7/11/2016)
== CJM2 ==========================================================================
Table 'testtable'. Scan count 9, logical reads 24440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads...
^{“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 11, 2016 at 9:20 am

#1889446
RE: Date Range overlaps

J Livingston SQL (7/11/2016)
guess I am !!
only 12 GB here
Try with this index G, I'm getting about 10% improvement.
-- CREATE UNIQUE NONCLUSTERED INDEX [idx_Code_Dates] ON [dbo].[testtable] ([Code] ASC,[enddate]...
^{“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 11, 2016 at 9:00 am

#1889437
RE: Date Range overlaps

J Livingston SQL (7/11/2016)
It's quick G but not quite as quick as this, which uses no Black Magic at all:
I dont get the same results Chris....any thoughts ??
PRINT '==...
^{“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 11, 2016 at 7:31 am

#1889406

Viewing 15 posts - 1,381 through 1,395 (of 10,143 total)