ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,051 through 1,065 (of 10,143 total)

RE: Data update

Ed Wagner (12/13/2016)
John Mitchell-245523 (12/13/2016)
spectra (12/13/2016)
I don't want to change datetime to any other datatype e.g date .......
Why not? You're not changing any data if you cast 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

December 13, 2016 at 8:15 am

#1917618
RE: trying to get rid of clustered index scan

tcronin 95651 (12/13/2016)
same plan and results
Okay so we try the brute force method and relax it from there:
SELECT ao.TherapyAdmin_ID, ao.VisitObservation_ID, 1 as RowVersionNumber
FROM (
SELECT t.TherapyAdmin_ID
FROM hcs.PatientOrder po
INNER 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

December 13, 2016 at 7:27 am

#1917595
RE: trying to get rid of clustered index scan

tcronin 95651 (12/13/2016)
just uploaded
Thanks. Can you run this please - and post up the plan?
SELECT TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber
FROM hcs.AdminObservation ao
WHERE EXISTS (
SELECT 1
FROM hcs.PatientOrder po
INNER 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

December 13, 2016 at 7:14 am

#1917592
RE: trying to get rid of clustered index scan

tcronin 95651 (12/13/2016)
tried yours and I had tried something similar will not give up the scan
People are guessing. There are at least five requests for you to post up 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

December 13, 2016 at 6:57 am

#1917584
RE: Data update

spectra (12/13/2016)
I would like to upgrade this query a bit ...
CREATE TABLE #John (j datetime)
INSERT INTO #John VALUES ('2016-11-10 12:12:42.100')
SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
If...
^{“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 13, 2016 at 6:48 am

#1917583
RE: Data update

spectra (12/13/2016)
John Mitchell-245523 (12/13/2016)
CREATE TABLE #John (j datetime) INSERT INTO #John VALUES ('2016-11-10 12:12:42.100') SELECT j FROM #John
WHERE j BETWEEN '20161201' and '20161213'
[font="Courier New"](0 row(s) affected)[/font]
John
Tested .... but its not...
^{“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 13, 2016 at 6:19 am

#1917570
RE: GROUP BY in SQL Server 2014

zouzou (12/13/2016)
Hello all,
The same query with a group by clause I try to run on SQL Server 2005 and on SQl Server 2014 but it seems that every time i...
^{“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 13, 2016 at 6:00 am

#1917566
RE: Converting date and/or time from character string.

-- Note1: CONVERT style 102 = yyyy.mm.dd
-- Note2: Use ISDATE with a character expression to determine if the expression can be converted to date.
-- isdate(convert(datetime won't work because the conversion...
^{“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 13, 2016 at 4:25 am

#1917548
RE: Long running Insert stored proc

Matt.Altman (12/12/2016)
Thank you Chris. 19 seconds to run that time.
Okay, that's more reasonable. Now, although your db is miniscule, this may be occurring - which is why I asked...
^{“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 12, 2016 at 9:21 am

#1917415
RE: Long running Insert stored proc

Matt.Altman (12/12/2016)
@thom-2. Thanks for cleaning up the code and making it readable. I changed the LEFT JOIN and the query is still running at 8 minutes. Should...
^{“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 12, 2016 at 9:11 am

#1917408
RE: Long running Insert stored proc

Matt.Altman (12/12/2016)
...
I delete everything from Metrics each time the stored procedure is run.
...
Then you shouldn't need this:
WHERE NOT EXISTS (SELECT 1 FROM Metrics WHERE week != DATEPART(wk, n.Timestamp) OR week...
^{“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 12, 2016 at 9:10 am

#1917406
RE: Long running Insert stored proc

Does the Metrics table have a clustered index? How are you removing rows, DELETE or TRUNCATE?
^{“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 12, 2016 at 9:04 am

#1917403
RE: Stored Procedure help

Or using CASE:
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(80)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserId INT, @EmailConfirmed BIT; -- is it a BIT...
^{“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 12, 2016 at 5:05 am

#1917340
RE: Need a help in writing a query

John Mitchell-245523 (12/12/2016)
WHERE LEN(MyCol) > 10 AND (MyCol LIKE '_____[1-6]1%' OR MyCol LIKE '_____[7-9]2%'
OR MyCol LIKE '_____[0]2%')
The digit 6 appears in both your "1" and "2" requirements, so I guessed where 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

December 12, 2016 at 2:25 am

#1917318
RE: Are the posted questions getting worse?

Eirikur Eiriksson (12/9/2016)
ChrisM@Work (12/9/2016)
Brandie Tarvin (12/9/2016)
rodjkidd (12/9/2016)
ChrisM@Work (12/8/2016)
rodjkidd (12/7/2016)
Eirikur,
I'm about a 20 minute walk to the High Holborn Caffe 1882. Let me know if you still want to meet up...
^{“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, 2016 at 9:58 am

#1917120

Viewing 15 posts - 1,051 through 1,065 (of 10,143 total)