ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,626 through 2,640 (of 10,143 total)

RE: Are the posted questions getting worse?

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 from 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

February 16, 2015 at 7:18 am

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

Matt Miller (#4) (2/12/2015)
Jeff Moden (2/12/2015)
I'm thinking that the following from the original code could be the real problem here. Dunno for sure because I don't know the data...
^{“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 13, 2015 at 2:13 am

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

Jeff Moden (2/12/2015)
I'm thinking that the following from the original code could be the real problem here. Dunno for sure because I don't know the data but it would...
^{“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 13, 2015 at 2:07 am

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

sgmunson (2/12/2015)
ChrisM@home (2/12/2015)
You're absolutely right Steve, it's an OUTER APPLY coded using CROSS APPLY! I'd love to see some ddl and dml for the tables referenced within it. If 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 13, 2015 at 2:03 am

#1777434
RE: SQL Server Execution Plans - Grant Fritchey

bpw (2/12/2015)
But there aren't two referenced columns, the modified query is only referencing the primary-key column - hence my question.
Point taken, sorry.
Ok try this: what two columns does the ordinary...
^{“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 12, 2015 at 7:31 am

#1777242
RE: SQL Server Execution Plans - Grant Fritchey

bpw (2/12/2015)
I understand clustered and non-clustered indexes.
The Name column is not being referenced in the query, so why would SQL Server reference the non-clustered index on the Name column?
Imagine 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 12, 2015 at 7:14 am

#1777229
RE: Need help in SP Its urgent :-( please help me

In this particular case, which of the following statements is the more accurate:
"Write it properly"
"Tune it"
Sometimes it's a fine line, but in this case I think it's clear.
It seems...
^{“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 12, 2015 at 6:47 am

#1777219
RE: Need help in SP Its urgent :-( please help me

Zohaib Anwar (2/12/2015)
ChrisM@Work (2/12/2015)
The stored procedure is written in a procedural Row-By-Agonizing_Row way indicating an inexperienced programmer. The upside is that you have plenty of scope for improvement by 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

February 12, 2015 at 6:40 am

#1777216
RE: How To Insert 2 Values in 2 columns and rest Hello values in rest columns without using Create Table Statement

maria.pithia (2/12/2015)
Please see my Question as i have clearly stated that i dont want to use create table statement or dont want to set defualt value.
i want to do 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

February 12, 2015 at 5:59 am

#1777201
RE: Need help in SP Its urgent :-( please help me

The stored procedure is written in a procedural Row-By-Agonizing_Row way indicating an inexperienced programmer. The upside is that you have plenty of scope for improvement by a professional. So why...
^{“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 12, 2015 at 5:47 am

#1777198
RE: Need help in SP Its urgent :-( please help me

Zohaib Anwar (2/12/2015)
Please let me know if you need any further detail. I Can also provide Trace file if required. please help me its urgent development team is asking ETA...
^{“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 12, 2015 at 5:29 am

#1777192
RE: Query Assistance - if no data result is in table_1 based on two variables a and b, get the data from table_2 based on one variable a

erwin.oosterhoorn (2/12/2015)
Don't worry, I will always test before I implement. This was more a 'gut feeling' and wanted to make sure that other users where not going to take 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

February 12, 2015 at 4:12 am

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

-- t11 partial rewrite. Can't go any further than this without ddl/dml for ClientMember / ClientRole / MemberOther
CROSS APPLY ( -- t11
SELECT TOP (1) [t10].[test]
FROM (SELECT NULL AS [EMPTY]) 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

February 12, 2015 at 3:00 am

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

Dave Ballantyne (2/12/2015)...I have to agree with the others here, there is nothing wrong with this statement per se...
I have to disagree with you Dave:
-------------------------------------------------------
-- t4 rewrite
-------------------------------------------------------
-- [t3].[EndEffectiveDate] =...
^{“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 12, 2015 at 2:28 am

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

Here's that same query with a little extra formatting:
SELECT
[t4].[Destination],
[t4].[Message],
[t4].[OutboxAN],
[t4].[OutboxStatusAN],
[t4].[OutboxTypeAN],
[t4].[Subject],
[t4].[Fax],
[t4].[FileName],
[t4].[OutBoxData] AS [OutBoxData1],
[t4].[OutBoxDataAN],
[t4].[Type],
[t4].[ClientReferenceID],
[t4].[value] AS [CompanyClientMemberID],
[t4].[value2] AS [ContactClientMemberID],
[t4].[value3] AS [ReportType],
[t4].[value4] AS [CompanyClientRoleID]
FROM ( -- t4
SELECT
[t0].[Destination],
[t0].[Message],
[t0].[OutboxAN],
[t0].[OutboxStatusAN],
[t0].[OutboxTypeAN],
[t0].[Subject],
[t1].[Fax],
[t1].[FileName],
[t1].[OutBoxData],
[t1].[OutBoxDataAN],
[t1].[Type],
[t0].[ClientReferenceID],
[value] = ISNULL([t0].[CompanyClientMemberID],@p1),
[value2] = ISNULL([t0].[ContactClientMemberID],@p2),
[value3] = ISNULL([t0].[ReportType],@p3),
[value4]...
^{“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 12, 2015 at 2:00 am

#1777158

Viewing 15 posts - 2,626 through 2,640 (of 10,143 total)