Jeff Moden

Forum Replies Created

Viewing 15 posts - 40,036 through 40,050 (of 59,072 total)

RE: TempDB into Memory

sam rebus (1/28/2010)
i have no influence over queries that developers write (and already written) and queries that reporting software uses. i have to try speeding things up using what is...
--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 29, 2010 at 10:52 pm

#1111123
RE: File System Task issue

Heh... On most systems, especially those dedicated to ETL and the like, I avoid all of those problems with SSIS (and DTS)... I don't use them. 😛 I do...
--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 29, 2010 at 10:38 pm

#1111121
RE: PK Questions

I normally do what Elliot suggested.... I use an IDENTITY column as a PK even if a natural key is available. It just gives more choices and in this...
--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 29, 2010 at 10:32 pm

#1111120
RE: Stored Proc with table variables running very slow?

Lynn Pettis (1/29/2010)
Actually used it to capture data changes while developing a data modification process when I needed to do destructive testing in a test environment and didn't want 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)

January 29, 2010 at 10:12 pm

#1111117
RE: Exceptional DBA Voting

What would really be cool is to see the email or letter from the person(s) that nominated these folks. Let's see what they have to say about their exceptional...
--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 29, 2010 at 10:09 pm

#1111115
RE: Add standard text to all stored procedures

MANU-J. (1/29/2010)
Try the below mentioned code on test server.
DECLARE @tbl TABLE
(
RowText varchar(max)
)
INSERT INTO @tbl
exec sp_helptext 'dbo.test1'
DECLARE @sp-2 varchar(max)
SELECT @sp-2 = ISNULL(@sp, '') + RowText
FROM @tbl
SET @sp-2 = REPLACE(@sp, 'AS', '/****************************
SQL2008...
--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 29, 2010 at 9:50 pm

#1111112
RE: Stored Proc with table variables running very slow?

I forgot about that and that's very true. Have you ever seen code that does that, though?
--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 29, 2010 at 9:42 pm

#1111110
RE: Index Where Name = NULL

Unless a table is nothing more than a temp table, I put clustered indexes on all tables as a matter of rote. That way, I don't have to worry...
--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 29, 2010 at 9:40 pm

#1111109
RE: Stored Proc with table variables running very slow?

san43 (1/29/2010)
Also is it possible that data is more fragmented in production which is causing the slowness, if so how can I find out that?
My apologies... I missed that part....
--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 29, 2010 at 9:34 pm

#1111106
RE: Stored Proc with table variables running very slow?

As some have already stated, the optimizer thinks all Table Variables only have one row and, therefor, may not pick the best execution plan when the data finally reaches what...
--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 29, 2010 at 9:29 pm

#1111105
RE: hourly average

rpatil22 (1/29/2010)
I have a data in table
Heh... but we don't. We have text on a page and would have to spend a bit of time turning it into data...
--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 29, 2010 at 9:19 pm

#1111103
RE: hourly average

Another way to skin the same cat except this looks a little cleaner...
SELECT ID, StartTime, StartTime + '00:59:59' AS EndTime, AVG(Produced) AS HourlyAverage
FROM (
...
--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 29, 2010 at 9:05 pm

#1111102
RE: hourly average

You can cheat the hell out of this one, BaldingLoopMan... 🙂
Using your good test data setup...
SELECT ID,
DATEADD(hh,DATEDIFF(hh,0,UtcTd),0) AS StartTime,
...
--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 29, 2010 at 8:57 pm

#1111101
RE: Compare values from 2 tables

Hopefully, you ended up with something like this...
SELECT m.MachineID,
m.Description,
CASE
...
--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 29, 2010 at 8:37 pm

#1111098
RE: Patern Matching

UT- (1/29/2010)
Hi,
I have a text column that contains notes about a customer, in this column there are mutiple reference numbers of a customer in the format of xxxxxx-xxxxx-xx (e.g 123456-12345-12).
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)

January 29, 2010 at 8:18 pm

#1111093

Viewing 15 posts - 40,036 through 40,050 (of 59,072 total)