Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,351 through 1,365 (of 59,067 total)

Reply To: Calculating Prime Numbers With One Query

@ Jonathan,
Sorry... My previous post above was referring to the 10 million row run prior to your additional (@Start*@Start) optimization. I have to try your new optimization. I didn't look...
--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)

June 22, 2023 at 3:26 pm

#4216864
Reply To: Calculating Prime Numbers With One Query

p.s. I just tested the results of Jonathan's code against a 3rd party generator and it passed.
https://numbergenerator.org/numberlist/prime-numbers/1-1000000#!low=1&high=1000000&csv=nl&oddeven=&step=1&addfilters=prime_numbers-val-
Jonathan's code takes about 39 seconds for a max value of 10 million without...
--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)

June 22, 2023 at 12:13 am

#4216382
Reply To: Calculating Prime Numbers With One Query

Jonathan AC Roberts wrote:
Jeff Moden wrote:
That's pretty amazing. Now someone just needs to verify that it's actually producing the correct results using a brute force method.
It's basically a primes sieve, but instead...
--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)

June 21, 2023 at 11:42 pm

#4216380
Reply To: delete as head blocker

Remember that deletes affect all indexes (except maybe filtered indexes) and causes a check for every FK pointing at the table. There's some silly wide tables at one of the...
--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)

June 21, 2023 at 2:08 pm

#4216093
Reply To: Calculating Prime Numbers With One Query

That's pretty amazing. Now someone just needs to verify that it's actually producing the correct results using a brute force method.
Also, I've not looked into it, is there a link...
--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)

June 21, 2023 at 2:02 pm

#4216092
Reply To: How to Avoid N+1 Queries: Comprehensive Guide and Python Code Examples

A far better way, IMHO, is to write a good SELECT in a stored procedure. 😉
What's the old saying? "Just because you can do it in Python, doesn't mean you...
--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)

June 21, 2023 at 12:40 am

#4215662
Reply To: Calculating Prime Numbers With One Query

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
If they are implementing things like shift-left and shift-right maybe they should also implement unsigned integers.
It's coming... According to the way they handled things like GENERATE_SERIES(),...
--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)

June 20, 2023 at 7:33 pm

#4215493
Reply To: Calculating Prime Numbers With One Query

@steve-2 Collins,
Sorry... I tried your latest code on a max value of "just" 1 million. I stopped it at 2 hours and 8 minutes.
--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)

June 20, 2023 at 7:30 pm

#4215492
Reply To: Calculating Prime Numbers With One Query

Jonathan AC Roberts wrote:
If they are implementing things like shift-left and shift-right maybe they should also implement unsigned integers.
It's coming... According to the way they handled things like GENERATE_SERIES(), etc, it should...
--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)

June 20, 2023 at 5:08 pm

#4215435
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
Jeff Moden wrote:
Ok... I'm a little gobsmacked... I ran your code with zero changes and it's riddled with syntax errors and,yet, it ran without error.
What is the following supposed to...
--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)

June 20, 2023 at 3:57 pm

#4215370
Reply To: Calculating Prime Numbers With One Query

~~I also have to admit that I have no clue what something like this is supposed to be doing in T-SQL. In another language, maybe but not in t-SQL.~~
(prime <<...
--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)

June 20, 2023 at 3:26 pm

#4215326
Reply To: Calculating Prime Numbers With One Query

~~Ok... I'm a little gobsmacked... I ran your code with zero changes and it's riddled with syntax errors and,yet, it ran without error.~~
~~What is the following supposed to do for...~~
--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)

June 20, 2023 at 3:21 pm

#4215325
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
Jeff Moden wrote:
The doubled-up relational symbology isn't floating my boat on an SQL Server forum, either.
There are functions also as I'm sure you're already aware
So why are you using...
--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)

June 20, 2023 at 3:17 pm

#4215323
Reply To: Calculating Prime Numbers With One Query

Steve Collins wrote:
I don't know. Setting STATISTICS ON doesn't work on Fiddle
Simplified without the 4th CTE
The doubled-up relational symbology isn't floating my boat on an SQL Server forum, either.
--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)

June 20, 2023 at 2:35 pm

#4215319
Reply To: Devil's Advocate

Sounds good. You could still be affected if you copy a DATETIME2(7) into any datatype that has a lower resolution.
--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)

June 20, 2023 at 2:25 pm

#4215304

Viewing 15 posts - 1,351 through 1,365 (of 59,067 total)