ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,696 through 1,710 (of 10,143 total)

RE: optimizing query

If all of the objects referenced in the query are remote and on the same server, then run the query on the remote server. The two obvious ways to do...
^{“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

March 11, 2016 at 2:08 am

#1864504
RE: Optimizing Queries

Frank Cazabon (3/10/2016)
Thanks, it's giving me an error when I include the
OPTION (MAXRECURSION 0)
I have left it out, I hope it's not important 🙂
Correct syntax is to use 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

March 10, 2016 at 7:49 am

#1864246
RE: Optimizing Queries

Frank Cazabon (3/10/2016)
Thanks Chris, that CTE is incredibly faster!
Is it possible to use CTE in a view definition? All this code relies on this view so it would be...
^{“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

March 10, 2016 at 7:30 am

#1864238
RE: Optimizing Queries

Those plans suggest that the number of rows returned is only a small fraction of the total, in which case Paul White's super-quick rCTE-based DISTINCT equivalent might be worth 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

March 10, 2016 at 6:55 am

#1864228
RE: Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

Junglee_George (3/10/2016)
I wiil use the NVARCHAR 4K version of [DelimitedSplitN4K] for splitting the input parameter string.
If you can help me in rewriting the stoed proc usp_StatusHistory , it will be...
^{“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

March 10, 2016 at 6:23 am

#1864206
RE: Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

Junglee_George (3/10/2016)
I am posting the required code.
ALTER FUNCTION [dbo].[eufn_e5_eLIMS_SE_Parse_Delimited_List](
@listString nvarchar(4000),
@delimiter nvarchar(1)
)
RETURNS @listTable Table( listItem nvarchar(50) )
AS
BEGIN
DECLARE @pos int
DECLARE @itemString nvarchar(50)
SET @listString = LTRIM(RTRIM(@listString))+ @delimiter-- add trailing comma
SET @Pos...
^{“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

March 10, 2016 at 3:47 am

#1864193
RE: Getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded"

Junglee_George (3/10/2016)
I have posted the complete code for the stored proc here.
I need a alternate way that code which cause the error can be avoided.
I have not developed this code...
^{“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

March 10, 2016 at 2:05 am

#1864184
RE: Select records with CASE statement

souLTower (3/9/2016)
Please try the following. I haven't tried it but I've used a similar approach in other queries.
Select Items, Qty From Table1
Where
QTY >
-- This can be...
^{“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

March 9, 2016 at 9:38 am

#1864049
RE: Alternative to CASE Statement for the below mentioned code

Phil Parkin (3/9/2016)
ganapathy.arvindan (3/9/2016)
Thanks !!! Same Case statement(same code) used in 4 select statements in single SP so i thought instead of writing 4 times,whether there is anyway i can...
^{“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

March 9, 2016 at 6:24 am

#1863976
RE: Can you create an index that has an expression ?

mw112009 (3/8/2016)
Select top 100 CONVERT( CHAR(8), LAB_SCR_DT, 112 ) , * FROM LAB_IN
This will not work..( below )
Create INDEX IDX_334455 on LAB_IN ( CONVERT(...
^{“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

March 9, 2016 at 2:22 am

#1863934
RE: Alternative to CASE Statement for the below mentioned code

ganapathy.arvindan (3/9/2016)
case when AR=1 then ' Calling'
when AR=2 then 'Closed'
when AR=3 then 'Completed'
when AR=5 then 'Resend to A'
when AR=6 then 'Resend to C'
when AR=7 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

March 9, 2016 at 2:14 am

#1863932
RE: Self referencing indexed view created. Using it gives both results and error.

DennisPost (3/8/2016)
Annoyingly enough, this resolved itself after a couple of hours. :alien:
I went back to it to try adding synonyms into the mix, but it was no longer needed.
There might...
^{“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

March 8, 2016 at 9:05 am

#1863740
RE: In a heap..

terry999 (3/8/2016)
Maybe I should bite the bullet and make the PK (varchar(12)) a clustered index.
Inserts happen in random order with respect to PK values. So it would become fragmented quickly....
^{“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

March 8, 2016 at 8:30 am

#1863725
RE: Last Accessed Datetime for table - Most easiest way

dlchase (3/7/2016)
PaymentID
Try this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
TableName = SCHEMA_NAME(t.[schema_id]) +'.'+t.Name,
IndexName = i.name,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
s.last_user_update
FROM sys.tables t
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.[object_id] = t.[object_id]
AND s.database_id = DB_ID()
LEFT 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

March 8, 2016 at 5:44 am

#1863644
RE: Why is a Table Scanned and Read So Much More than Others

Is the information table indexed to support your queries?
Can you provide an actual execution plan?
^{“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

March 3, 2016 at 7:59 am

#1862765

Viewing 15 posts - 1,696 through 1,710 (of 10,143 total)