ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,611 through 2,625 (of 10,143 total)

RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Velveeta22 (2/18/2015)
ChrisM@Work (2/18/2015)
In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly...
^{“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

February 18, 2015 at 8:23 am

#1778365
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly out of...
^{“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

February 18, 2015 at 6:18 am

#1778320
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Velveeta22 (2/17/2015)
Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope 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

February 18, 2015 at 2:30 am

#1778287
RE: Help in query

Here's another way:
SELECT
a.[Pax ID],
a.[Flight Date],
a.[Flight Number],
[Last Mod Date] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Last Mod Date] ELSE x.[Last Mod Date] END,
[Status] = CASE WHEN x.[Pax ID]...
^{“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

February 18, 2015 at 2:25 am

#1778286
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Velveeta22 (2/17/2015)
This filter AND O.CompanyClientMemberID IS NOT NULL won't work in the join, if the filter must be active then it must be in the WHERE clause. I'd mark 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

February 17, 2015 at 8:58 am

#1778090
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Can you post the *actual* 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

February 17, 2015 at 8:49 am

#1778086
RE: Are the posted questions getting worse?

rodjkidd (2/17/2015)
ChrisM@Work (2/17/2015)
dwain.c (2/16/2015)
ChrisM@Work (2/16/2015)
rodjkidd (2/16/2015)
Hey Chris,
How likely is the chance now of you turning up Thursday at SQL Bits?
Rodders...
I've booked the day off, mate - need 5 minutes 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

February 17, 2015 at 6:25 am

#1778043
RE: Are the posted questions getting worse?

dwain.c (2/16/2015)
ChrisM@Work (2/16/2015)
rodjkidd (2/16/2015)
Hey Chris,
How likely is the chance now of you turning up Thursday at SQL Bits?
Rodders...
I've booked the day off, mate - need 5 minutes to book 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

February 17, 2015 at 3:13 am

#1778000
RE: Are the posted questions getting worse?

rodjkidd (2/16/2015)
ChrisM@Work (2/16/2015)
rodjkidd (2/16/2015)
ChrisM@Work (2/16/2015)
rodjkidd (2/16/2015)
Hey Chris,
How likely is the chance now of you turning up Thursday at SQL Bits?
Rodders...
I've booked the day off, mate - need 5 minutes 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

February 17, 2015 at 3:12 am

#1777998
RE: Are the posted questions getting worse?

TomThomson (2/16/2015)
ChrisM@Work (2/16/2015)
TomThomson (2/16/2015)
...
Stop digging, Tom! For goodness' sake, you're coming across as a cranky old fella with a short fuse. This is not what we are used to seeing...
^{“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

February 17, 2015 at 3:11 am

#1777997
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

Velveeta22 (2/16/2015)
This produces the same result set in both the test environment and production. Does anyone see any logic fail on the re-write?
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p1...
^{“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

February 17, 2015 at 1:50 am

#1777978
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

sgmunson (2/16/2015)
The following part of the LEFT JOIN isn't needed:
AND O.CompanyClientMemberID IS NOT NULL
That field is already part of the rest of the JOIN condition (the specific line follows) based...
^{“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

February 17, 2015 at 1:33 am

#1777971
RE: Query Causing Blocking, Locks Table for 1000 Seconds, Help Needed!

First off, here's a modified ddl / dml script. It's a little more compressed than the original, and possibly easier to adjust for data changes:
CREATE DATABASE OutBox;
GO
USE [OutBox]
GO
---------------------------------------------------------------------------------------------------
-- [dbo].[Outbox]
---------------------------------------------------------------------------------------------------
SET ANSI_NULLS...
^{“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

February 17, 2015 at 1:24 am

#1777965
RE: Are the posted questions getting worse?

rodjkidd (2/16/2015)
ChrisM@Work (2/16/2015)
rodjkidd (2/16/2015)
Hey Chris,
How likely is the chance now of you turning up Thursday at SQL Bits?
Rodders...
I've booked the day off, mate - need 5 minutes to book 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

February 16, 2015 at 7:56 am

#1777838
RE: Are the posted questions getting worse?

rodjkidd (2/16/2015)
Hey Chris,
How likely is the chance now of you turning up Thursday at SQL Bits?
Rodders...
I've booked the day off, mate - need 5 minutes to book the thursday at...
^{“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

February 16, 2015 at 7:33 am

#1777831

Viewing 15 posts - 2,611 through 2,625 (of 10,143 total)