ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,136 through 6,150 (of 10,143 total)

RE: Contentious SP

Just a hunch Les, but have you tried replacing the delete & update with MERGE?
-------------------------------------------------------------------------------------
-- Delete any previous record from today
-- Close out the previous record
MERGE CollectorTransitionHistory AS [target]
...
^{“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

July 2, 2012 at 10:11 am

#1507815
RE: Using CASE to create SQL Statement

Sean Lange (7/2/2012)
ChrisM@Work (7/2/2012)
Sean Lange (7/2/2012)
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is...
^{“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

July 2, 2012 at 9:14 am

#1507776
RE: Using CASE to create SQL Statement

Sean Lange (7/2/2012)
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is indeed quite 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

July 2, 2012 at 9:06 am

#1507772
RE: Breaking a String apart

Yep.[/url]
^{“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

July 2, 2012 at 8:53 am

#1507767
RE: Find Member with 12 months consecutive range

SELECT *
FROM #Sample s
CROSS APPLY (
SELECT ClaimCount = COUNT(*)
FROM #Sample
WHERE [Member ID] = s.[Member ID]
AND [Date of Service] >= s.[Date of Service]
AND [Date of Service] <=...
^{“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

July 2, 2012 at 8:03 am

#1507739
RE: APPLY vs JOIN (the differences with a focus on APPLY)

Ted_Kert (7/1/2012)
Is the benefit of OUTER APPLY and CROSS APPLY that you don't need an ON clause? I don't really see why these are different from LEFT OUTER JOIN...
^{“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

July 2, 2012 at 6:34 am

#1507706
RE: Database Design for Hospital Management

Jigneshal (7/2/2012)
MysteryJimbo (7/2/2012)
Jigneshal (7/2/2012)
Hi,
I want to create a database for hospital management system.
but i dont have much idea to design db. Can anyone help creating db for hospital management.
It will...
^{“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

July 2, 2012 at 2:32 am

#1507646
RE: aggregating result set problems

mixalissen (7/2/2012)
This problem has been bugginh me since friday! After a good weekend I managed to find the solution 🙂
ALTER VIEW [dbo].[neoset_dailySalesManReport]
AS
SELECT distinct TOP (100) PERCENT s.contentid,...
^{“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

July 2, 2012 at 2:25 am

#1507641
RE: aggregating result set problems

Those two rows with contentid = 1, which have a different furniturelocation: which other columns are different between the two rows?
Avoid using ORDER BY in views. If you check 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

July 2, 2012 at 2:03 am

#1507635
RE: Ordered group by

_simon_ (7/1/2012)
I want my results to be grouped, your query is totally correct. I was just curious if there is any reason behind the decision, that you used dense_rank() instead...
^{“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

July 2, 2012 at 1:52 am

#1507632
RE: UDF

Using an inline function may help somewhat. Here's the definition of your existing splitter:
--User Defined Function (InLine)
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS...
^{“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

June 29, 2012 at 8:01 am

#1507136
RE: UDF

Phil Parkin (6/29/2012)
ChrisM@Work (6/29/2012)
If you'd said "less than 6" then you would have had alternatives options to a function. With a maximum of about 50 items in the list, then...
^{“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

June 29, 2012 at 7:33 am

#1507129
RE: UDF

If you'd said "less than 6" then you would have had alternatives options to a function. With a maximum of about 50 items in the list, then I reckon you...
^{“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

June 29, 2012 at 7:19 am

#1507124
RE: UPDATE with CASE Statement not updating all Records

Welsh Corgi (6/29/2012)
It is a Simple Query. I just wanted to get the record counts right before I added a bunch of CASE Statements.
As soon as I add the first...
^{“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

June 29, 2012 at 7:15 am

#1507120
RE: UPDATE with CASE Statement not updating all Records

Welsh Corgi (6/29/2012)
If I comment out the LEFT JOIN's I get 39,028 Records.
With the LEFT JOIN's uncommented I get 26,0876 records.
And that's the exact same query that you...
^{“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

June 29, 2012 at 7:06 am

#1507111

Viewing 15 posts - 6,136 through 6,150 (of 10,143 total)