ChrisM@Work

Forum Replies Created

Viewing 15 posts - 46 through 60 (of 10,143 total)

Reply To: dbcc traceon(2453,-1)

MVDBA (Mike Vessey) wrote:
I've been trying to fix some performance issues recently and started by swapping temp tables to table variables , testing performance gains at every step- quick win. until I...
^{“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, 2020 at 12:08 pm

#3731360
Reply To: The COVID-19 Impact
Get the best information you can.
About COVID-19: https://www.who.int/docs/default-source/coronaviruse/who-china-joint-mission-on-covid-19-final-report.pdf

WHO situation report: https://www.who.int/docs/default-source/coronaviruse/situation-reports/20200304-sitrep-44-covid-19.pdf?sfvrsn=783b4c9d_2

WHO Q&A: https://www.who.int/news-room/q-a-detail/q-a-coronaviruses

COVID-19 mortality rate by age (seeking a better source): https://www.sciencealert.com/covid-19-s-death-rate-is-higher-than-thought-but-it-should-drop
- This reply was modified 6 years, 3 months ago by ChrisM@Work.
^{“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, 2020 at 11:45 am

#3731347
Reply To: update query is running very slow

How big is this 256-row table? What's the pattern of usage?
^{“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

February 21, 2020 at 11:44 am

#3727632
Reply To: Extract part of string
Less than four functions, anyone?
```
SELECT string, numbers
FROM ( VALUES
    ('Total # of bytes : 128270200832 (119.46GB)'),
    ('Total # of bytes :...
```
^{“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
February 18, 2020 at 4:30 pm

#3726806
Reply To: A quick search without an index

I remember doing this many years ago, but it didn't get coded up at the time. The same in principle but not quite the same code. Just for S&G I...
^{“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

February 17, 2020 at 1:39 pm

#3726412
Reply To: FUN only, solving a Puzzle.
ben.brugman wrote:
/*
For FUN.
I like puzzles, I like T-SQL coding, I like to show off :-), so why not combine these.
This project was for FUN only, but I did learn new...
- This reply was modified 6 years, 4 months ago by ChrisM@Work.
^{“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
February 14, 2020 at 2:50 pm

#3725929
Reply To: T-SQL not throwing error for invalid column name in a subselect

MVDBA (Mike Vessey) wrote:
I did not know this... are there any ms documents around it?

Not that I've seen. The observed behaviour is necessary to support correlated subqueries, where columns from both inside...
^{“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

February 14, 2020 at 1:17 pm

#3725894
Reply To: How to write query on column with list of delimited categories

MVDBA (Mike Vessey) wrote:
Sorry to jeff, but i'm going to make light of the split functionality, it ends up in a function that goes RBAR - Use a TVP instead
again - sorry...
^{“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

February 14, 2020 at 1:13 pm

#3725891
Reply To: UPDATE statement on CTE with INNER JOIN
michael.leach2015 wrote:
drew.allen wrote:
You're UPDATE statement is incorrect. If you are going to alias something, you need to use that alias throughout.
```
UPDATE o -- use the alias here.
SET...
```
- This reply was modified 6 years, 4 months ago by ChrisM@Work.
^{“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
February 12, 2020 at 11:08 am

#3725074
Reply To: Need to replace this inefficient CASE statement

VoldemarG wrote:
THIS works:
WHERE ((T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X')) AND PS.popup_gid = 2)
OR ((T.record_Key_4 <> '1' AND T.Record_Key_5 IN('I', 'X')) AND PS.popup_gid = 5)
thanks for your help !!!

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

February 10, 2020 at 1:13 pm

#3724327
Reply To: Multi-statement table-valued function is not working

Either SELECT or VALUES
DECLARE @ID int
DECLARE @multiVariable table
(
ID int,
FName varchar(20),
Company varchar(20),
State char(2)
)
INSERT INTO @multiVariable
--VALUES
SELECT ID, fName, address, state
FROM baseTableExample;
^{“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

February 10, 2020 at 12:42 pm

#3724322
Reply To: Need to replace this inefficient CASE statement

WHERE 1 = CASE
WHEN T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN('I', 'X') THEN CASE WHEN PS.popup_gid = 2 THEN 1 eLSE 0 END
ELSE CASE WHEN PS.popup_gid = 5 THEN 1...
^{“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

February 7, 2020 at 4:38 pm

#3723998
Reply To: Need to replace this inefficient CASE statement
```
WHERE 
(PS.popup_gid = 5)
OR
(PS.popup_gid = 2 AND T.record_Key_4 = '1')
OR 
(PS.popup_gid = 2 AND T.Record_Key_5 NOT IN ('I', 'X'))


Add an extra column to your temp table:

MyFilter =...
```
^{“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
February 7, 2020 at 4:04 pm

#3723986
Reply To: Are the posted questions getting worse?
jasona.work wrote:
OK, something I've never done before. Would anyone object if I posted a GoFundMe link here? It's not for me in any way, shape, or form, but it is...
- This reply was modified 6 years, 4 months ago by ChrisM@Work.
^{“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
February 5, 2020 at 8:50 am

#3723191
Reply To: Calculate Moving Averages using T-SQL in SQL Server

This article, along with this one from ten years ago, should be enough to assist in solving most Moving Average problems. Thanks for posting.
^{“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

February 4, 2020 at 4:27 pm

#3723005

Viewing 15 posts - 46 through 60 (of 10,143 total)