ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,621 through 1,635 (of 10,143 total)

RE: Just fetches one record in 10 lakhs

I bet I'm not the only one around here who sits on the next potential iteration of the OP's query waiting for responses before posting. Here's one of those:
SELECT --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

April 26, 2016 at 9:53 am

#1874683
RE: Just fetches one record in 10 lakhs

Have you tried this?
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
...
^{“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

April 26, 2016 at 6:11 am

#1874531
RE: Just fetches one record in 10 lakhs

mbavabohrude (4/26/2016)
I just commented and ran it fetches around 1074733 in 9 mins .When i run the inner query it fetches one row
when i run the inner join seperately...
^{“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

April 26, 2016 at 5:32 am

#1874520
RE: Just fetches one record in 10 lakhs

Comment out the derived table, then selectively remove and restore the filters from the WHERE clause. Observe what happens. How many rows are in table dbo.PolicyAudit with p.Status ...
^{“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

April 26, 2016 at 4:53 am

#1874512
RE: Just fetches one record in 10 lakhs

mbavabohrude (4/26/2016)
Thanks for your info . When i just run the query i am still getting one record alone . Why i am not getting all the policy records in...
^{“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

April 26, 2016 at 4:43 am

#1874509
RE: Just fetches one record in 10 lakhs

Try this alternative to your query. The significant change is removing the right join. Most folks avoid them - a mix of right joins and left joins in the same...
^{“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

April 26, 2016 at 3:59 am

#1874499
RE: Query tuning advice required please

There are numerous RID lookups in the plan. It's rare for a table to play better without a clustered index than with one. There's even an ordinary index called PK_...
^{“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

April 25, 2016 at 7:20 am

#1874222
RE: Are the posted questions getting worse?

Phil Parkin (4/22/2016)
BLOB_EATER (4/22/2016)
He lost me on freezer performance...
The walking freezer is what I want to see.
Manchester. That's where my ex wife lives.
^{“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

April 22, 2016 at 7:49 am

#1873906
RE: Why does moving the ON Clause change the query result

There are a couple of ways to rewrite this code, discussed here. Choose one, document it, and encourage folks to stick with it.
^{“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

April 22, 2016 at 4:44 am

#1873856
RE: Problems with dates

Another way:
WITH SampleData ([Employee No.], [Rag Rating], [Timestamp]) AS (
SELECT 1, 'Green', CONVERT(DATE,'21/04/2016',103) UNION ALL
SELECT 1, 'Yellow', CONVERT(DATE,'20/04/2016',103) UNION ALL
SELECT 1, 'Yellow', CONVERT(DATE,'19/04/2016',103) UNION ALL
SELECT 1, 'Green', CONVERT(DATE,'18/04/2016',103) UNION ALL
SELECT...
^{“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

April 21, 2016 at 8:40 am

#1873661
RE: Performance tuning a query

There's some discussion of it following this post.
^{“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

April 20, 2016 at 8:48 am

#1873364
RE: Performance tuning a query

below86 (4/20/2016)
Not sure it will make a difference, but this is what I would do, I also added comments on a few things that looked questionable to me. try...
^{“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

April 20, 2016 at 8:33 am

#1873357
RE: Performance tuning a query

VSSGeorge (4/19/2016)
I am attaching the actual execution plan available.
The full execution plan is not available as an error message is encountered.
Msg 1105, Level 17, State 2, Line 61
Could not allocate...
^{“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

April 19, 2016 at 6:30 am

#1873024
RE: A deadlock issue that killing me dead

Can you post it as a .sqlplan file attachment please?
^{“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

April 14, 2016 at 8:37 am

#1872005
RE: Re-Write SQL Query to a View

Those two UNIONed queries would probably be more efficient run as one query. also, is that left-joined table actually necessary except for the outer SELECT? This is what I mean:
SELECT
GLItems.GLChartOfAccount,
GLItemValue...
^{“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

April 13, 2016 at 8:49 am

#1871732

Viewing 15 posts - 1,621 through 1,635 (of 10,143 total)