ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,921 through 4,935 (of 10,143 total)

RE: CTE - Temp table - CROSS APPLY

Arjun Sivadasan (4/4/2013)
I had some free time at work and was trying out some silly queries when I came across this.
CTE referenced in a subquery does not yield any result....
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 5, 2013 at 1:07 am

#1603818
RE: How can we optimize a query having TABLE VALUED function in SELECT?

TheSQLGuru (4/3/2013)
refactor your TVF to be an INLINE TVF.
I think it is an iTVF
Also push that SELECT ...(SELECT...) out like previous poster did.
I don't think that matters much,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 9:15 am

#1603242
RE: A Hazard of Using the SQL Merge Statement

the sqlist (4/3/2013)
vliet (4/3/2013)
We tried both the CTE approach and the AND clause on the WHEN NOT MATCHED BY SOURCE part to merge rows spanning only a certain period. The...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 9:09 am

#1603236
RE: A Hazard of Using the SQL Merge Statement

Jim Sebastiano (4/3/2013)
If you're excited about the MERGE statement (aren't we all?), you'll probabaly also be excited by row constructor
I think Dwain knows a thing or two about table constructors...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 8:58 am

#1603227
RE: How can we optimize a query having TABLE VALUED function in SELECT?

Can you post the actual execution plan for this please, as a .sqlplan attachment:
SELECT TOP 250000
[z13031322175522447287ca90] = x1.wo_ref,
[z13031322175522447289ca90] = SUM(x.approved_ts_amount)
FROM dbo.wo x1
INNER JOIN dbo.wocc x3
ON x1.wo_id...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 8:46 am

#1603216
RE: Calculate number of days missed per term

Does it meet the requirements for Step 1? The accumulation would be Step 2.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 8:26 am

#1603196
RE: Insert into new table based on conditions

Anytime, thanks for the feedback.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 8:19 am

#1603185
RE: Are the posted questions getting worse?

Lynn Pettis (4/3/2013)
I'm curious, how do most of you like your dihydrogen monoxide, straight or flavored?
I don't drink water, fish frolic in it.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 8:18 am

#1603184
RE: Insert into new table based on conditions

The joins are all messed up. It runs forever because it's generating a partial cartesian product. Try this instead:
SELECT
u.Useraccount_First_Name,
u.Useraccount_Last_Name,
x.*
FROM Application_Useraccounts u -- "driving" table is now correct
CROSS APPLY...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 7:55 am

#1603168
RE: Insert into new table based on conditions

Does the SELECT without the INSERT output the correct result set?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 7:42 am

#1603152
RE: Query Performance

Use MERGE with an output clause. Dwain Camps has an excellent article on the front page today which describes a potential pratfall issue with MERGE. It's perfectly adequate to get...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 7:40 am

#1603150
RE: Join on 2 tables where values are different

Use the LIKE operator:
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.Region LIKE '*'+t1.Region+'*%'
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 6:50 am

#1603129
RE: Join on 2 tables where values are different

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.Region = '*'+t1.Region+'*'
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 6:27 am

#1603120
RE: How will you optimize this Query (having many SELF (LEFT) Joins) ?

T.Ashish (4/2/2013)
Chris,
I have attached sample data for both the tables.
Thanks, it's so much easier when you have sample data to play with.
As Kevin pointed...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 4:37 am

#1603088
RE: Are the posted questions getting worse?

Sean Lange (4/2/2013)
Can somebody help here. http://www.sqlservercentral.com/Forums/Topic1437758-391-1.aspx
I know it isn't too difficult I just can't seem to figure out what they want.
Touched.
It will still be tricky, even with a full...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

April 3, 2013 at 2:02 am

#1603048

Viewing 15 posts - 4,921 through 4,935 (of 10,143 total)