Jeff Moden

Forum Replies Created

Viewing 15 posts - 27,031 through 27,045 (of 59,101 total)

RE: Slow running query

TheSQLGuru (4/13/2013)
Good points Jeff. I would definitely look at pre-populating the concatenated objects as temp tables. I had that in my notes I took while looking at alternatives...
--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)

April 13, 2013 at 9:23 pm

#1606281
RE: Question about query hint nolock

mister.magoo (4/13/2013)
Any ideas?
Yes, but they're probably not viable for your situation. My recommedations would all start with "Get rid of..." and "Replace the..." and end with " and kill...
--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)

April 13, 2013 at 9:12 pm

#1606280
RE: SQL Server Jobs, status, duration running, step

opc.three (4/10/2013)
This might help get you started:
$Instances = (Get-Content 'C:\Instances.txt') # Make a text file containing your instances, one on each line
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
foreach ($instance in $Instances)
{
...
--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)

April 13, 2013 at 1:23 pm

#1606260
RE: this query is taking time for execution how to trouble shoot

The biggest problem with the current query is that it's not possible for an INDEX SEEK (and subsequent range scan) to occur on the r_mod_time column because of the formulas...
--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)

April 13, 2013 at 12:57 pm

#1606259
RE: Slow running query

Here's an example of what I'm talking about. Of course, I don't have access to the data so the code is completely untested but this will calculate the delimited...
--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)

April 13, 2013 at 12:02 pm

#1606258
RE: Slow running query

eseosaoregie (4/13/2013)
I am currently working on project where we are extracting data from some AX tables. The data from these extracts is transformed in vary ways to be consumed 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)

April 13, 2013 at 10:53 am

#1606254
RE: Question about query hint nolock

GilaMonster (4/13/2013)
ReadPast = skip locked rows. Also rather bad for accurate results. Find for stuff like dashboards or areas where close to accurate is good enough
If you're having problems with...
--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)

April 13, 2013 at 9:48 am

#1606252
RE: Recursive Factorial Function ( n ! )

davidrudd (4/12/2013)
A better solution with no limits.
Thanks to:
http://2smart4school.com/tsql-stored-procedure-to-get-factorial-of-a-given-number/
CREATE PROCEDURE Factorial (@num INT) AS
BEGIN
DECLARE @fact int, @query varchar(255)
SET @fact = 1
IF(@num = 0)
BEGIN
SET @fact = 1
END
ELSE
BEGIN
WHILE(@num >0)
BEGIN
SET @fact =...
--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)

April 12, 2013 at 10:04 pm

#1606230
RE: Job market for SQL DBA

ryan_xh (4/12/2013)
Hello all,
How is the current job market for SQL DBA in US right now? I have around 6 years of SQL DBA experience and I am trying to find...
--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)

April 12, 2013 at 8:49 pm

#1606228
RE: Question about query hint nolock

If you're really trying to prevent blocking, then try the READPAST hint instead of the troublesome NOLOCK hint.
--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)

April 12, 2013 at 8:47 pm

#1606227
RE: Help comparing a data set within a table.

You have some nice data there but it's useless to the people that are trying to help you because it's not "readily consumable". You'll get better help quicker if...
--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)

April 12, 2013 at 8:31 pm

#1606226
RE: something like PIVOT?

SQLDCH (4/12/2013)
I have a table that stores a tablename, the last time the table was accessed and when that data was put into the table:
CREATE TABLE [dbo].[LastSelectCheck](
...
--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)

April 12, 2013 at 7:26 pm

#1606219
RE: CTE Vs temp table

Bhuvnesh (1/24/2013)
CTE is basically used to handle recursive operation not a performance boost alternative to temp table.
Not quite right. Although a CTE can certainly be used in 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)

April 12, 2013 at 7:18 pm

#1606216
RE: CTE Vs temp table

padhu.mukku (1/24/2013)
There will be no diff instead when ever u want to use this CTE to read data from it, u have to define its sturcture again and again....
Try 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)

April 12, 2013 at 7:11 pm

#1606214
RE: Using T-SQL to Create Cross-Tab Data for a CRM Report

j-1064772 (4/12/2013)
Thank you so much for your help Jeff.
And yes, I was already aware of the Tally table. I must be one of your greatest fans.
Regards
Thank YOU! :blush: 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)

April 12, 2013 at 5:37 pm

#1606201

Viewing 15 posts - 27,031 through 27,045 (of 59,101 total)