Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,941 through 5,955 (of 59,089 total)

Reply To: Creating indexes on a very large table takes over 5 hours

@pamkagel ,
Ok... you said that the entire table is generated nightly. That makes me want to assume that you're not actually doing any updates or additional inserts to the table. ...
--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 11, 2020 at 7:31 am

#3819249
Reply To: Extract data with variable column names and order

aaron.reese wrote:
not wanting to get off-topic and there are dozens of threads on EAV on this site, why would you prefer EAV for this problem. I can turn it into...
--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 11, 2020 at 3:15 am

#3819175
Reply To: comma issues in sql statement

John_2 wrote:
I'm clueless after trying everything and searching for others with this experience still getting the same errors again and again.
Please help me out with this error
Like I said earlier...
--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 11, 2020 at 2:57 am

#3819170
Reply To: comma issues in sql statement

ScottPletcher wrote:
Convoluted method, don't do it.
Only to those that think so.
--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 11, 2020 at 2:53 am

#3819169
Reply To: Inventory Running Total & calculations
Just as a bit of a drive by shooting, the following code...
```
select

DATEPART (d, datetime) AS Day,
CASE DATEPART(DW, datetime)
    WHEN 1 THEN 'Sun'
  ...
```
--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 10, 2020 at 4:28 pm

#3819023
Reply To: Extract data with variable column names and order

aaron.reese wrote:
(at least it's not EAV!)
Actually, too bad it's 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 10, 2020 at 4:17 pm

#3819021
Reply To: Inflow / outflow report per day

Again, can't use SUM() OVER for the running total on this one. The OP said he's actually using 2005.

--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 9, 2020 at 6:54 pm

#3818711
Reply To: Creating indexes on a very large table takes over 5 hours

pamkagel wrote:
Tried creating the view. It was non performant .
Then you're doing it wrong. Please answer the questions I posted or I don't know enough how to help 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)

December 9, 2020 at 6:51 pm

#3818710
Reply To: how to capture login 'sa' begin used ot not

My recommendation would be to do what the industry best practice normally recommends and disable it. If the phone rings, you'll have your answer and, yes, you should answer 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)

December 9, 2020 at 5:07 am

#3818494
Reply To: Same execution plan but different run time

paphilly wrote:
Hi, I am running into similar situation, except that my procedure consistently runs slow compared to other environment, when in comparison both environments have similar execution plan and statistics,...
--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 9, 2020 at 4:56 am

#3818485
Reply To: Subtract running amount

Steve... the OP has posted in a 2005 forum which is before SUM() OVER had the ability to produce a running sum. I'm thinking we're going to need another "Quirky...
--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 9, 2020 at 4:17 am

#3818480
Reply To: Creating indexes on a very large table takes over 5 hours

pamkagel wrote:
I have a table that is 125GB. It only has 5 Columns but has almost a billing rows. This is a fact table that gets regenerated nightly . 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 9, 2020 at 4:12 am

#3818479
Reply To: Boost performance of your SQL Server tables with a TURBO button

trimjib wrote:
A heap can have a non-clustered index and leaves won't be ordered on that index.
If I'm reading that correctly, I'm really sure that's not true. All non-clustered indexes are...
--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 9, 2020 at 12:15 am

#3818427
Reply To: Boost performance of your SQL Server tables with a TURBO button

To the best of my knowledge (and I could be wrong), inserts into heaps follow no order. They're almost always added to the last logical page of the heap. That...
--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 8, 2020 at 11:16 pm

#3818417
Reply To: Order By Slowness

Jackie Lowery wrote:
I may not have had to rebuild them, but doing a full sample on the statistics definitely fixed the slowness.
Should I have to do a full sample on 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)

December 8, 2020 at 10:25 pm

#3818394

Viewing 15 posts - 5,941 through 5,955 (of 59,089 total)