Jeff Moden

Forum Replies Created

Viewing 15 posts - 48,091 through 48,105 (of 59,102 total)

RE: MSSQL 2005 performance issue

Do you have any SQL Server Agent jobs that are failing? I've found that, depending on what they do, they can leave open connections that hold on to resources....
--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)

September 27, 2008 at 6:51 pm

#878110
RE: Convert datetime to just time (only hour)

BWAAHAA! No need to do the Polka with Convert, folks! 😛 Read the original post!
I have one [font="Arial Black"]datetime [/font]column named Hours in SQL Server Table1...
Notes4we, I recommend...
--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)

September 27, 2008 at 6:33 pm

#878109
RE: Database Size and Available Space

Just remember that TRUNCATE doesn't work in the presence of foreign keys. I believe they can be disabled rather than dropped, 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)

September 27, 2008 at 5:54 pm

#878107
RE: To give my process high priority in SQL server 2000

prasanna_pathangi (9/25/2008)
Is there a way to tell MS SQL that it should give higher priority to a query?. that is, i want my query to execute even when another process...
--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)

September 27, 2008 at 5:47 pm

#878104
RE: Concat String of More than 30000 Chars

It doesn't matter if you have a max of 8000 characters for a variable, or not. You cannot print more than 8000 characters in SQL Server in a single...
--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)

September 27, 2008 at 5:29 pm

#878101
RE: Size limitation of variable in query analyzer

Actually, there IS a limit on the size of stored procedures and batches... it's (65,536 * Network packet size) for batches and the lesser of batch size or 250 MB...
--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)

September 27, 2008 at 5:11 pm

#878100
RE: SQL studs and studettes!!!Please recommend something on this...Thx

Yes... there's a huge problem... you have a double nested correlated subquery in the SET clause... and THAT will be executed, in it's entirety, once for every row qualified by...
--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)

September 27, 2008 at 5:01 pm

#878099
RE: Date Manipulation

Roger Abram (9/27/2008)
Thanks! I get trying to use the DATEADD function again in the same line.
Roger
... and that will work, also... although it's a bit uglier...
DECLARE @date DATETIME
SELECT @date...
--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)

September 27, 2008 at 4:48 pm

#878098
RE: Query is slow because of TOP and Order By Count(*)

stephane3d (9/27/2008)
Hi,
I have a log table that saves web visits. And I have this query which is pretty slow :
select top 500 count(1) as nbCount, currentPage as item,
dateadd(dd, DATEDIFF(dd,...
--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)

September 27, 2008 at 4:45 pm

#878097
RE: Update with multiple conditions - Help needed

As a side bar, I strongly question the value of assigning the NCHAR datatype to a column, like the amount column, that will obviously hold a numeric value.;) 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)

September 27, 2008 at 1:06 pm

#878090
RE: Update with multiple conditions - Help needed

There is one lightning quick method to do this in a set based fashion that depends on a clustered index. The method is discussed at some great length in...
--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)

September 27, 2008 at 12:59 pm

#878089
RE: Measuring SQL Performance

For starters, you could try adding the "WITH RECOMPILE" option to the sprocs just to test the water. A better way would be to actually assume that parameter sniffing...
--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)

September 27, 2008 at 11:12 am

#878086
RE: Help on Update/Query

Thanks... I hate it when that happens... just splits resources and makes it difficult for others to search for a correct answer.
Since the other post has more activity, I'll continue...
--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)

September 27, 2008 at 7:15 am

#878075
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

peter (9/25/2008)
In my testing of using the shown tally function on the test input the estimated sub-tree cost of my whole query wend from 0.0131634 to 0.0071212 which is an...
--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)

September 27, 2008 at 7:07 am

#878074
RE: That's just not right...

rbarryyoung (9/26/2008)
What the IF NOT EXISTS check was intended to do was merely to prevent the error message in the first place. And they did complete that intention.
You could...
--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)

September 26, 2008 at 9:46 pm

#878042

Viewing 15 posts - 48,091 through 48,105 (of 59,102 total)