ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,746 through 2,760 (of 10,143 total)

RE: Find Appropriate Event After An Incident

Thanks.
Query 1 (insert into #temp_violation etc.) has an odd filter:
and
(
cast(fh.startdate as date)<= cast(tv.startdate as date)
and
cast(fh.startdate as time)<= cast(tv.startdate as time)
)
This will capture datetimes from fh where they are...
^{“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

January 2, 2015 at 2:07 am

#1767348
RE: Find Appropriate Event After An Incident

Can you post the execution plan?
Have you considered using EXISTS
WHERE EXISTS (SELECT 1 FROM #temp_violation t WHERE t.orderno = so.eai_orderno_int)
rather than IN
where so.eai_orderno_int in (select orderno from...
^{“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

December 31, 2014 at 5:17 am

#1767161
RE: how to show tow query result

Please provide a sample data script. Not only will this give folks a better idea of how your data looks, it will also encourage them to participate. A dozen rows...
^{“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

December 31, 2014 at 4:32 am

#1767158
RE: Display Order due to NonClustered Index

Shafat Husain (12/30/2014)
GilaMonster (12/30/2014)
No ORDER BY, no guarantee of order. End of Story
If you need a particular order, put an ORDER BY on your query.
Why so serious...!! :crying:
You get 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

December 30, 2014 at 7:41 am

#1767002
RE: Help me find the best index for a specific query

_simon_ (12/30/2014)
@ScottPletcher - I tried it now and it seems that both clustered and nonclustered indexes are running for about the same time.
I found the same, with the test data...
^{“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

December 30, 2014 at 6:53 am

#1766987
RE: Team Scdule with Round Robin

SELECT
b.*,
a.*
FROM #tbl_Team a
INNER JOIN #tbl_Team b ON b.id < a.id
ORDER BY b.id, a.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

December 30, 2014 at 2:47 am

#1766949
RE: this query runs to long how can I speed it up????

The outer select in the OP's original query isn't aggregated:
SELECT
w.uompScheduleNumber,
COLOR = CASE w.uompScheduleColor
WHEN 'NEON' then 'ORANGERED'
WHEN 'PINK' THEN 'FUCHSIA'
ELSE w.uompScheduleColor END,
x.Printed,
x.Audited,
x.Shortage_Prt,
x.Red,
x.Neon,
x.QC_Hold
...
^{“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

December 16, 2014 at 2:14 am

#1764877
RE: Cte for String in random order

You may get some ideas from this:
;WITH
E1 AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E1 a,...
^{“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

December 11, 2014 at 6:13 am

#1764016
RE: NO COUNT

Arthur Olcot (12/11/2014)
BWFC (12/11/2014)
Carlo Romagnano (12/11/2014)
Extremely easy!
The answer is in the title!
😀
The answer might be in the title, but as a newbie I went through my usual process of reading...
^{“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

December 11, 2014 at 4:57 am

#1764005
RE: how to identiyfy memory requirement to run the query

Can you post up the estimated plan for the query?
^{“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

December 10, 2014 at 6:25 am

#1763756
RE: remove non-numeric values from a nvarch column

Easy enough to do as others have shown - but why turn a valid address into an invalid one?
^{“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

December 10, 2014 at 6:24 am

#1763754
RE: Joining Three Tables with multiple record

Child2 table has nothing to indicate which row should be chosen when say row2 is requested - you cannot rely on the order in which you think the rows sit...
^{“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

December 9, 2014 at 6:45 am

#1763502
RE: CTE query - Long Load Time

Couple of questions:
Is ID the PK of either of the source tables?
Can you provide a few rows of sample data please?
Thanks.
^{“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

December 8, 2014 at 7:18 am

#1763268
RE: Consolidating Two or More Identical Rows into One

ssandeepksh (12/5/2014)
Hi,
Can you please help to solve this?
I have a table with many identical rows which has different numerical value only in once column.
I want these two or more identical...
^{“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

December 5, 2014 at 9:09 am

#1763002
RE: SUM() using ROW_NUMBER() without resorting to multiple CTEs

Can you post up a sample data script please Pete? I can't see why you should have to aggregate twice. Cheers.
^{“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

December 5, 2014 at 6:14 am

#1762941

Viewing 15 posts - 2,746 through 2,760 (of 10,143 total)