Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,776 through 2,790 (of 59,086 total)

Reply To: query take long time when execute it so how to solve issue ?

The "pre-aggreation" I spoke of earlier is identical to what you're suggesting. I'm just trying to get the OP to provide some extra information.
I'll also tell you there is no...
--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 12, 2022 at 5:26 pm

#4086753
Reply To: query take long time when execute it so how to solve issue ?

In a very real "get real" moment, I have to ask... how many times per day will this be used and how often will the underlying data be modified.
What I'm...
--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 12, 2022 at 1:00 am

#4086446
Reply To: query take long time when execute it so how to solve issue ?

In the ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg table, what does the FeatureValue column contain in reference to the PartId? Also, is FeatureValue a unique value by FeatureID or does it change with each 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)

September 12, 2022 at 12:50 am

#4086445
Reply To: Pivot columns to get 0 /1 flag columns

To get the nice-warm-fuzzies on that, though, start a new session and run the following code... It should run just fine in the new session where you haven't set 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)

September 11, 2022 at 10:58 pm

#4086432
Reply To: Pivot columns to get 0 /1 flag columns

masterelaichi wrote:
Jeff Moden wrote:
Jeffrey Williams wrote:
Phil Parkin wrote:
IF you add the statement
SET DATEFORMAT DMY;
before you attempt the INSERT, the error will go away and the INSERT runs successfully.
Although this is an option -...
--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 11, 2022 at 10:51 pm

#4086431
Reply To: Pivot columns to get 0 /1 flag columns

masterelaichi wrote:
Unless you meant conversion at the datasource ?
As Jeffrey Williams is implying, that would be the best solution.
--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 11, 2022 at 6:35 pm

#4086410
Reply To: Pivot columns to get 0 /1 flag columns

Jeffrey Williams wrote:
Phil Parkin wrote:
IF you add the statement
SET DATEFORMAT DMY;
before you attempt the INSERT, the error will go away and the INSERT runs successfully.
Although this is an option - don't 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)

September 11, 2022 at 6:33 pm

#4086409
Reply To: query take long time when execute it so how to solve issue ?
ahmed_elbarbary.2010 wrote:
```
SELECT f1_AllCount = COUNT(f1.PartId)
,f1_UnqCount = COUNT(DISTINCT f1.PartID)
FROM Technology.Receipe ft
JOIN ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg f1 ON ft.featureid = f1.featureid
WHERE ft.operatorid = 1;
```
result is
```
f1_AllCount         f1_UnqCount
124140 ...
```
--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 11, 2022 at 6:29 pm

#4086400
Reply To: Pivot columns to get 0 /1 flag columns

masterelaichi wrote:
jcelko212's post got me thinking
I tried to change the datatype of the paydate column to date by using the alter command but ended up getting this error "Conversion failed...
--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 11, 2022 at 5:03 am

#4086295
Reply To: query take long time when execute it so how to solve issue ?

ahmed_elbarbary.2010 wrote:
so how to use cross apply as you write below
The optimizer attempts to SORT 65 million rows after a merge join. Yeeeesh. One way to re-write the query would...
--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 11, 2022 at 4:47 am

#4086290
Reply To: Get AD data and load to SQL

I've found that I really don't need all of that stuff for SQL Server. xp_LoginInfo and sp_validatelogins usually provide what I need. If not, then I go to the Windows...
--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 10, 2022 at 9:50 pm

#4086256
Reply To: Pivot columns to get 0 /1 flag columns

jcelko212 32090 wrote:
Please learn what a table is. First, we need to fix your non-table.
CREATE TABLE Expenses (emp_id CHAR(10) NOT NULL, pay_date DATE NOT NULL, -- read ISO-8601 formats expense_type VARCHAR(30)...
--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 10, 2022 at 9:09 pm

#4086249
Reply To: query take long time when execute it so how to solve issue ?
I suspect a couple of accidental CROSS JOINs is the issue. Please run the following code and post the results.
```
 SELECT  f1_AllCount = COUNT(f1.PartId)
  ...
```
--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 10, 2022 at 6:16 pm

#4086227
Reply To: assigning value from sproc inside another sproc

Ah... I see what you mean. Guess I'll have to look up how to do a "StaNine" and see if I can make a function or a GP proc 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)

September 9, 2022 at 9:03 pm

#4086043
Reply To: assigning value from sproc inside another sproc

mjohnson 71479 wrote:
That procedure just calls some individual functions used to get each Stanine.
If you are looking for a formula for determining the bellcurve, we pay for studies to be done...
--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 9, 2022 at 8:26 pm

#4086033

Viewing 15 posts - 2,776 through 2,790 (of 59,086 total)