ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,766 through 6,780 (of 10,143 total)

RE: Can I move sorting and avoid Order By with Clustered Index on an empty table?

drew.georgopulos (8/12/2011)
Holy Good Night...that sure is a lot more readable!
I am not sure if the distinct is necessary or not.
Thank you for your work
drew
Should be quite a bit faster too,...
^{“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

August 15, 2011 at 1:56 am

#1368469
RE: Can I move sorting and avoid Order By with Clustered Index on an empty table?

A couple of suggestions.
/* PQI 09 Measure */
-- second query of two in this section.
-- Existing code
update member_aggregate set PQI09N = discharges
from member_aggregate i
inner join (
select member_id, count(distinct...
^{“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

August 12, 2011 at 4:05 am

#1367791
RE: Can I move sorting and avoid Order By with Clustered Index on an empty table?

drew.georgopulos (8/10/2011)
...
the whole thing is dog slow...drew
The whole thing is dog slow because it's coded naively. Here's a suggestion for you. Post each stored procedure separately. This will encourage folks...
^{“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

August 11, 2011 at 4:48 am

#1367155
RE: Combining two sets from same table into one set and being able to pull from new set.

thompson 44810 (8/10/2011)
...What we would like is to create a temp variable from the process that we can pull from...
What are you really trying to do? How about that representative...
^{“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

August 10, 2011 at 10:12 am

#1366711
RE: Nested LEFT JOIN with WHERE?

Put the filter into the JOIN otherwise you will turn it into an INNER JOIN as Ninja shows.
SELECT
c.option_Desc,
c.option_ID,
s.row_ID,
s.User_ID
FROM tbl_codes c
LEFT JOIN tbl_Codes_Select s
ON...
^{“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

August 10, 2011 at 9:58 am

#1366696
RE: speed up and lean up my SQL Statement that will be come a view

twdavis-893252 (8/10/2011)
Will you look at my SQL Statement and give me ideas on how to speed it up and make it leaner. It will be made into a view to...
^{“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

August 10, 2011 at 9:50 am

#1366685
RE: Combining two sets from same table into one set and being able to pull from new set.

thompson 44810 (8/10/2011)
Paul I understand this is really confusing on my first post.
What I want to do is pull and combine from two different columns in the same database...
^{“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

August 10, 2011 at 9:37 am

#1366670
RE: Combining two sets from same table into one set and being able to pull from new set.

Phil Parkin (8/10/2011)
ChrisM@Work (8/10/2011)
Try adding COUNT(*) OVER(PARTITION BY Residence_Address) to your first query.
From this, it sounds like you have managed to understand the initial post. I salute you sir 🙂
Heh...
^{“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

August 10, 2011 at 9:03 am

#1366639
RE: Combining two sets from same table into one set and being able to pull from new set.

Try adding COUNT(*) OVER(PARTITION BY Residence_Address) to your first 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

August 10, 2011 at 8:54 am

#1366629
RE: Help with a sql query

skcadavre (8/10/2011)
...
Beaten to the punch! Teach me for leaving a window open while doing work, then returning to it without refreshing! 😉
Makes a change from lightning-fast Remi 😉
^{“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

August 10, 2011 at 8:12 am

#1366580
RE: Help with a sql query

DROP TABLE #Sample
CREATE TABLE #Sample (Name VARCHAR(10), A INT, B INT, C INT, d INT, E INT)
INSERT INTO #Sample (Name, A, B, C, d, E)
SELECT 'John', 1, 0, 1, 1,...
^{“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

August 10, 2011 at 7:25 am

#1366540
RE: Hidden RBAR: Counting with Recursive CTE's

Jeff Moden (8/9/2011)
ChrisM@Work (8/9/2011)
Crackin' good read as always Jeff.
Thanks, Chris. Always good to hear from you.
I never noticed it before. Your signature line says you're a defender of...
^{“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

August 10, 2011 at 2:06 am

#1366416
RE: Hidden RBAR: Counting with Recursive CTE's

Crackin' good read as always Jeff.
^{“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

August 9, 2011 at 9:50 am

#1366039
RE: help with select

Mad-Dog (8/9/2011)
...
it's not give me the correct results.
LOG_NUMBER '002' needs to be
00205/08/2011
00206/08/2011
00207/08/2011
Apologies. Actually it should appear three times:
SELECT LOG_NUMBER,
MAX_EVENT_TIME =...
^{“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

August 9, 2011 at 6:39 am

#1365835
RE: help with select

Chooes the column you want, min or max (or average, if you wish):
DROP TABLE #Sample
CREATE TABLE #Sample (LOG_NUMBER CHAR(3), EVENT_TIME DATETIME)
INSERT INTO #Sample (LOG_NUMBER, EVENT_TIME)
SELECT '002','2011-08-07 20:29:05.787' UNION ALL
SELECT '002','2011-08-07...
^{“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

August 9, 2011 at 6:19 am

#1365825

Viewing 15 posts - 6,766 through 6,780 (of 10,143 total)