Jeff Moden

Forum Replies Created

Viewing 15 posts - 21,706 through 21,720 (of 59,098 total)

RE: Help needed in Performance Tuning

ScottPletcher (1/27/2015)
Jeff Moden (1/26/2015)
ScottPletcher (1/26/2015)
dwain.c (1/25/2015)
coalesce(a.processStatus, 0) = 0
is not SARGable, so that could be improved on by making the processStatus column NOT NULL.
It's much better to code it...
--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)

January 27, 2015 at 9:22 am

#1773416
RE: Please explain what this Trigger query does

David Burrows (1/26/2015)
Well done Jeff 🙂
Please clarify
You are auditing 'what was' and not 'what is'
'Insert' is the first audit row or the main row if no audit exists
and this needs...
--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)

January 27, 2015 at 6:59 am

#1773374
RE: Always set a Field to NULL - is a trigger the answer

What will it hurt if something IS entered into the column and can it wait? If so, save yourself some pain and just run a job on a scheduled...
--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)

January 26, 2015 at 11:56 pm

#1773303
RE: Grant Fritchey Reviews Minion Reindex

Nice write-up, nice product. I haven't done a deep dive on the code but I have some questions. Maybe Sean can incorporate some of these if they don't...
--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)

January 26, 2015 at 11:46 pm

#1773298
RE: What's the best practices for storing Very Large Tables?

Rahuul (1/26/2015)
Thanks Jeff. I got it.
On another note for the sake curiosity,
What would be your recommendation had it been a Standard OR Enterprise version of SQL Server (not Azure)?...
--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)

January 26, 2015 at 11:01 pm

#1773295
RE: Help needed in Performance Tuning

ScottPletcher (1/26/2015)
dwain.c (1/25/2015)
coalesce(a.processStatus, 0) = 0
is not SARGable, so that could be improved on by making the processStatus column NOT NULL.
It's much better to code it as:
(a.processStatus is null...
--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)

January 26, 2015 at 10:43 pm

#1773293
RE: Repeat contact cursor

Details are in the code...
--===== Conditionally drop the working table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#QU','U') IS NOT NULL
...
--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)

January 26, 2015 at 10:13 pm

#1773291
RE: NEed help for SQLServer Query

I don't do links for two reasons... 1) is that I don't actually know what's there and don't want to take the chance going there and 2) is that 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)

January 26, 2015 at 5:43 pm

#1773273
RE: What's the best practices for storing Very Large Tables?

Rahuul (1/26/2015)
Thanks Jeff.
The article was informative.
Per your advice, we'll go ahead and combine these tables with Partition Views.
Thanks a lot,
Rahul
Not quite what I recommended for Azure, if that's...
--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)

January 26, 2015 at 9:34 am

#1773159
RE: Help needed in Performance Tuning

Something is going on here. Using your test data and your code as you posted it, the code runs in 23 seconds. Is it this test code 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)

January 25, 2015 at 1:06 pm

#1772970
RE: Group By (Grouping with Non Aggregrate)

It's not exactly what you asked for but I have a couple of suggestions on this.
First, this is a "hierarchical" problem but the data you have doesn't exactly support 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)

January 25, 2015 at 11:43 am

#1772965
RE: Help needed in Performance Tuning

First, thank you for taking the time to build the test data. That will really help.
It would be helpful if, instead of trying to figure out what the overall...
--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)

January 25, 2015 at 10:30 am

#1772957
RE: Query result display layout

kd11 (1/25/2015)
Question, I have a query I select the data from it will return the data as such:
ManagerName EmployeeName HoursWork
John...
--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)

January 25, 2015 at 10:13 am

#1772955
RE: Please explain what this Trigger query does

You bet and thank you for the great questions and feedback throughout this thread. Let us know how it all works out when you get the chance.
--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)

January 25, 2015 at 9:39 am

#1772951
RE: Selecting and merging records for a singular complete record

k.allen.emt (1/24/2015)
I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads...
--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)

January 24, 2015 at 9:29 pm

#1772937

Viewing 15 posts - 21,706 through 21,720 (of 59,098 total)