ChrisM@Work

Forum Replies Created

Viewing 15 posts - 481 through 495 (of 10,143 total)

RE: Are the posted questions getting worse?

Just spent a business day prototyping a partition-switch mechanism to improve the performance of a weekly data feed of 50M rows into a 12,000,000,000 row table. It takes ten hours. Most...
^{“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

January 3, 2018 at 10:11 am

#1974277
RE: TSQL help

-- Works for cnt <= 10
-- Expand the inline tally for cnt > 10
;WITH Process AS (
SELECT t2.*, t1.cnt,
rn = ROW_NUMBER() OVER(PARTITION BY...
^{“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

January 3, 2018 at 9:41 am

#1974273
RE: Find a string with two dots

drew.allen - Tuesday, January 2, 2018 11:30 AM
ChrisM@Work - Tuesday, January 2, 2018 10:16 AM
^{“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

January 3, 2018 at 7:12 am

#1974218
Reply To: Find a string with two dots
```
-- SQL Server will perform the filter and the conversion in either order
-- Note that Compute Scalar is plonked anywhere convenient in the execution plan
-- and may...
```
- This reply was modified 7 years, 2 months ago by ChrisM@Work.
- This reply was modified 4 weeks ago by Steve Jones - SSC Editor. Reason: formatting
^{“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
January 2, 2018 at 10:16 am

#1974103
RE: INDEX MATCH IN SQL.... IS IT POSSIBLE??

craig.jenkins - Wednesday, December 13, 2017 9:19 AM
here is the sample data with the error message. Many thanks
IF OBJECT_ID('tempdb..#SampleData', N'U')...
^{“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

December 14, 2017 at 1:36 am

#1972078
RE: ASCII ProperCase Function

Here's an alternative which is worth testing:
DECLARE @String
^{“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

December 11, 2017 at 9:57 am

#1971596

RE: Find Differences Between Two Tables

I'd do something like this:

--===== Drop Temp Tables to make runs in SSMS easier.

^{“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

December 6, 2017 at 5:08 am

#1970896

RE: INDEX MATCH IN SQL.... IS IT POSSIBLE??

craig.jenkins - Wednesday, December 6, 2017 2:29 AM

Thank you Chris, appreciate the help. Will try and get me head around this and...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 6, 2017 at 2:35 am

#1970876

RE: INDEX MATCH IN SQL.... IS IT POSSIBLE??

-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
(7425623, 'CSI', 1224199, 7425623),
(7422342, 'CSI', 1236163, 7422342),
(7414996,...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 6, 2017 at 2:18 am

#1970871

RE: Slow query - client or server issue

leehbi - Tuesday, December 5, 2017 3:51 AM

I denormalised some of the underlying tables - this resulted in a much simpler plan. ...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 6, 2017 at 1:45 am

#1970861

RE: Mathematics/Algorithms in SQL

Jeff Moden - Monday, December 4, 2017 5:17 PM

Alan.B - Monday, December 4, 2017 3:07 PM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 5, 2017 at 12:49 am

#1970652

RE: Are the posted questions getting worse?

Lynn Pettis - Friday, December 1, 2017 8:33 AM

Brandie Tarvin - Friday, December 1, 2017 8:28 AM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 1, 2017 at 8:47 am

#1970300

RE: Calendar table to transaction table join performance

ScottPletcher - Friday, December 1, 2017 7:46 AM

It can't hurt to apply the limiting condition to both tables:

WHERE a.TransactionTime >= @startdate
and b.DateKey >=...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 1, 2017 at 7:50 am

#1970279

RE: Calendar table to transaction table join performance

Make that join SARGable:

--However, if I move the date filter to the calendar table like so:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

December 1, 2017 at 6:32 am

#1970251

RE: Converting a varchar to int

Check your data:

SELECT [Status]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

November 29, 2017 at 7:19 am

#1969912

Viewing 15 posts - 481 through 495 (of 10,143 total)