Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,176 through 2,190 (of 59,067 total)

Reply To: Is there another way to solve this "group in 5 day spans" problem?

frederico_fonseca wrote:
Jeff Moden wrote:
I added 1000 "sets" of 3 groups, each with 13 dates. The Cartesian products persisted in the execution plan, each of them doing reads of 39,078,039 rows.
Too bad...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 7:03 pm

#4132011
Reply To: Split One Column into Multiple that uses / as the delimiter

Luv SQL wrote:
I've been researching this for over 3 hours and none of the syntax I've found works. I have a column with data that's been merged from various other tables...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 6:57 pm

#4132010
Reply To: Is there another way to solve this "group in 5 day spans" problem?

I added 1000 "sets" of 3 groups, each with 13 dates. The Cartesian products persisted in the execution plan, each of them doing reads of 39,078,039 rows.
Too bad because, like...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 5:36 pm

#4131983
Reply To: Is there another way to solve this "group in 5 day spans" problem?

That certainly solves the problem without an explicit WHILE loop or a "Quirky Update". I used the following code the add 5 more sets of 3 groups each to "play"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 5:29 pm

#4131978
Reply To: Is there another way to solve this "group in 5 day spans" problem?

Ken McKelvey wrote:
Ken McKelvey wrote:
In this case I cannot see anyway to predict when missing dates are needed (2017-11-16) or not needed (2017-11-19 & 2017-11-18).
This is a delayed message as I...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 4:41 pm

#4131962
Reply To: Is there another way to solve this "group in 5 day spans" problem?

frederico_fonseca wrote:
my attempt at this.
Do note that the 5 days interval is partially misleading as to the final result because of the time part.
,(7 ,'2017-11-17 09:38:00.000') --Group 2 starts here...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 4:36 pm

#4131961
Reply To: Cama saperated values
Here's the solution to the problem you posted.
```
 SELECT CSV = STRING_AGG(sal,',')
   FROM #emp
;
```
There are more powerful uses for the STRING_AGG() function if you have...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
December 30, 2022 at 4:15 pm

#4131951
Reply To: Query help

karen.ferrara wrote:
Thank you so much J. Drew Allen! That is exactly what I was looking for. I appreciate your help and detailed script.
Do you understand what it does and how...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 3:57 pm

#4131946
Reply To: Are the posted questions getting worse?

Steve Jones - SSC Editor wrote:
Jeff Moden wrote:
Ummm... ok. Why not the best of both worlds instead of "upset everyone"?
If you mean add some relational stuff to the WP document store, I'm tempted. I think...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 3:53 pm

#4131943
Reply To: Best primarykey and index

ScottPletcher wrote:
To be absolutely clear, you want a:
2) unique clustered [index] on (tenant,year,id)
And if you partition the data (which typically wouldn't be necessary here), partition it by (tenant, year) not...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 30, 2022 at 3:39 pm

#4131940
Reply To: Are the posted questions getting worse?

Steve Jones - SSC Editor wrote:
Grant's book: https://www.amazon.com/Server-2022-Query-Performance-Tuning/dp/1484288904/ref=sr_1_3?crid=3I1VV0HSNHD4T&keywords=grant+fritchey&qid=1672333097&sprefix=grant+fritche%2Caps%2C159&sr=8-3&ufe=app_do%3Aamzn1.fos.006c50ae-5d4c-4777-9bc0-4513d670b6bc
Thanks, Steve.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 29, 2022 at 6:16 pm

#4131604
Reply To: Are the posted questions getting worse?

Steve Jones - SSC Editor wrote:
I've logged quite a few bugs lately on things. We've got a test site setup for an upgrade to PHP/WP/Nami, which is delaying work on other bugs. The upgrade...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 29, 2022 at 6:14 pm

#4131603
Reply To: Are the posted questions getting worse?

Bloody forum software did it again... since the post I just posted is the first one on a new page, it's not showing up until I make another post. I...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 29, 2022 at 4:41 pm

#4131566
Reply To: Are the posted questions getting worse?

Grant Fritchey wrote:
I show an example in the query tuning book where defragmentation improves performance because of a scan, fewer pages read. I also explain all the reasons why, this probably...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 29, 2022 at 4:38 pm

#4131565
Reply To: Are the posted questions getting worse?

Thanks, Greg. I have to admit I took it wrong way a bit and so was just making sure. And I love both of your examples.
As a bit of a...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 28, 2022 at 10:28 pm

#4131262

Viewing 15 posts - 2,176 through 2,190 (of 59,067 total)