ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,336 through 1,350 (of 10,143 total)

RE: Issue with slow find and replace function code from a lookup table can I do this in a better way

Thanks for the generous feedback KR - do pop by if you need further assistance with this.
^{“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 4, 2016 at 7:11 am

#1893762
RE: Are the posted questions getting worse?

BWFC (8/4/2016)
Jack Corbett (8/3/2016)
Lynn Pettis (8/3/2016)
Is the code I wrote here really that sophisticated, or is it that I have been doing this so long that for me...
^{“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 4, 2016 at 5:05 am

#1893716
RE: inefficient query

drew.allen (8/3/2016)
And here is an option changing the last part to a NOT EXISTS. This also assumes that PolicyInsuredLocGK cannot be NULL.
AND NOT EXISTS (
SELECT 1
FROM AIRGBS.vLCExtract AS lc2
WHERE...
^{“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 3, 2016 at 9:18 am

#1893528
RE: inefficient query

Sean's nailed the real problem - the indexing is a proper mess.
You might get a little better mileage with this alternative query:
;WITH FirstQuery AS (
SELECT
lc.DownLoad
, lc.AccountNum
, lc.PolicyNum
--, splitLC.SplitPolicyNumLmt
, splitLC.SplitPolicyNumLmt
,...
^{“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 3, 2016 at 8:16 am

#1893493
RE: Issue with slow find and replace function code from a lookup table can I do this in a better way

Northern Monkey (8/3/2016)
Also I would like to add a parameter to be passed from the case statement to the function to limit the type of product I have a column...
^{“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 3, 2016 at 7:54 am

#1893486
RE: In-Line TVF Split Function JOIN to Base Tables Optimization

regan.wick (8/2/2016)
I have a specific question about JOINing results of an in-line split function to base tables
I am using an in-line TVF and joining to base tables. Sample code below.
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

August 3, 2016 at 3:45 am

#1893426
RE: how to run stored procedure inside select statement

mizan700 (8/3/2016)
Hi Experts,
How can i run my stored procedure inside select statement?
example:
Select Username, exec @myproc from userinfo
Regards,
Mizan
Can't you add UserName to the output of the stored procedure?
^{“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 3, 2016 at 3:16 am

#1893423
RE: Set Date based on day of Month

Sergiy (8/2/2016)
ChrisM@Work (8/2/2016)
Sergiy (8/1/2016)
ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the database
Dates definitively have a format when they are stored in the database.
Dates do not have...
^{“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 3, 2016 at 2:17 am

#1893419
RE: What is the minimum lever of permission I need to use these views

View server state.
^{“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 2, 2016 at 9:44 am

#1893274
RE: Set Date based on day of Month

Sergiy (8/1/2016)
ben.brugman (7/21/2016)
spaghettidba (7/21/2016)
Dates have no format when they are stored in the database
Dates definitively have a format when they are stored in the database.
Dates do not have format stored...
^{“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 2, 2016 at 8:54 am

#1893250
RE: Deadlock - due to Temp Tables?

Adi Cohn-120898 (8/2/2016)
A deadlock can occur only on resources that multiple sessions can work with. Local temporary table can be used by one session only (the one that was...
^{“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 2, 2016 at 6:46 am

#1893209
RE: DATEDIFF format

yujinagata72 (8/1/2016)
Hi,
I use the below code, I can get the duration as hh:mm:ss.0000000.
CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, last_request_end_time, GETDATE()), 0), 114) AS Duration
But how I can get the format like dd:hh:mm:ss?
Thanks,
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

August 2, 2016 at 2:04 am

#1893141
RE: YYYY-MM-ddTHH:mm:ssZ Format Error

TheComedian (8/2/2016)
Eirikur Eiriksson (8/2/2016)
Quick thought, datetime does not have any time zone awareness and only assumes UTC, convert into a time zone aware data type such as datetime2 instead.
😎
SELECT CONVERT(DATETIME2,'2016-08-01T07:04:24+01:00',127)
So...
^{“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 2, 2016 at 1:47 am

#1893138
RE: Select Statement SubQuery in the From Clause

Scubba-Steve (8/1/2016)
Actually I am on 2008. I just asked the question on the first SQL forum that I found. Did I post in the wrong place?
Yes but it's no big...
^{“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 1, 2016 at 9:07 am

#1892976
RE: Select Statement SubQuery in the From Clause

Scubba-Steve (8/1/2016)
Wow. I didnt catch that. Thanks for the help. Works like a charm.
Cool.
Are you really on SQL Server 7 or 2000?
^{“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 1, 2016 at 8:56 am

#1892972

Viewing 15 posts - 1,336 through 1,350 (of 10,143 total)