ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,376 through 6,390 (of 10,143 total)

RE: Get month names ordered using recursion

The recursive CTE is used for generating rows - and it's a massively expensive way to do this. In 2K8 or higher, row constructors would be much cheaper
VALUES((1),(2),...)
In 2k5,...
^{“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 13, 2012 at 3:09 am

#1458346
RE: Count Function

Jeff Moden (2/13/2012)
ChrisM@home (1/18/2012)
jennigirl (1/18/2012)
I was able to get the query results I need, but I am looking to now present the results as the total count of trouble tickets...
^{“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 15, 2012 at 1:51 am

#1447166
RE: Need Better Way of Converting Rows to Columns

Cadavre (12/9/2011)
ChrisM@Work (12/9/2011)
Although it's less elegant than Cadavre's code, it's likely to perform a little faster.
Agreed 100%. Generally I use cross-tab queries to pivot data, I showed the syntax for...
^{“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

December 9, 2011 at 8:49 am

#1419450
RE: Need Better Way of Converting Rows to Columns

The method you are using - correlated subqueries - is similar in layout to a cross-tab, but check out the real thing for performance:
SELECT
ListingID,
Photo01 = MAX(CASE WHEN Priority =...
^{“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

December 9, 2011 at 8:37 am

#1419433
RE: Severe Performance Problem

Divine Flame (12/9/2011)
GilaMonster (12/9/2011)
Divine Flame (12/9/2011)
What is the average row cont in sys.dm_io_pending_io_requests ??
On SQL 2000?
lol :-D, No way . He has not mentioned that he is using SQL...
^{“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

December 9, 2011 at 4:05 am

#1419185
RE: How to display the first and second Doctor to see a patient?

Can you confirm that you are using SQL Server 2000?
Have a quick read of the link "here" in my sig, it will help you frame your question for a better...
^{“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

December 9, 2011 at 4:04 am

#1419183
RE: Are the posted questions getting worse?

SQLRNNR (12/8/2011)
ChrisM@Work (12/8/2011)
SQLRNNR (12/7/2011)
Took the plunge on 70-451
Nice one, Jason!
Thanks
Was the braindump useful?:-D
It was just small enough to fit :hehe:
^{“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

December 9, 2011 at 3:17 am

#1419158
RE: How to perform an update to an old record when inserting new record in same table using merge, when the ON clause matches only some of the key fields.

You could use the OUTPUT clause:
DROP TABLE #MemberEnrolments
CREATE TABLE #MemberEnrolments (MemberID INT NOT NULL, EnrollmentID INT NOT NULL, enrolmentStartDate DATE NOT NULL, primaryflag BIT)
ALTER TABLE #MemberEnrolments --WITH NOCHECK
ADD CONSTRAINT...
^{“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

December 8, 2011 at 10:24 am

#1418816
RE: need sql query to convert a column of a table into meaningful data

mario.balatellii (12/8/2011)
yeah thats what i was thinking as well.. Thanks.
Or may be i will have sql job which will transfer the data into a table in a database. What...
^{“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

December 8, 2011 at 6:24 am

#1418641
RE: need sql query to convert a column of a table into meaningful data

Probably easiest if you run the results into a local temp table...
SELECT ... INTO #Temp FROM ...
^{“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

December 8, 2011 at 5:58 am

#1418627
RE: Update with inner join - Taking more than 13 hours

-- change this
update TestA set TestA.Column5 = TestB.Column5
from TestA inner join TestB
on TestA.Column1 = TestB.Column1
and TestA.column2 = TestB.Column2
and TestA.Column3 = TestB.Column3
-- to this
SELECT <<primary key from table a>>, b.Column5
INTO #Temp
FROM...
^{“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

December 8, 2011 at 4:43 am

#1418598
RE: Are the posted questions getting worse?

SQLRNNR (12/7/2011)
Took the plunge on 70-451
Nice one, Jason!
^{“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

December 8, 2011 at 4:01 am

#1418573
RE: need sql query to convert a column of a table into meaningful data

mario.balatellii (12/7/2011)
thanks for this Chris. I will try this at work tomoro and let you know how it goes. Much appreciated
You're welcome Mario. You will need to change the name...
^{“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

December 7, 2011 at 10:12 am

#1418256
RE: need sql query to convert a column of a table into meaningful data

Crosstab version:
SELECT
[Type]= MAX(CASE WHEN en.[Entity name] = 'Type' THEN Attribute END),
[Bank name]= MAX(CASE WHEN en.[Entity name] = 'Bank name' THEN Attribute END),
[Merchant code] = MAX(CASE WHEN en.[Entity...
^{“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

December 7, 2011 at 10:07 am

#1418249
RE: need sql query to convert a column of a table into meaningful data

mario.balatellii (12/7/2011)
I will be hard coding the headings and I want the respective data for the row under that heading.
No problem. Don't forget to shape your sample data so that...
^{“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

December 7, 2011 at 9:36 am

#1418218

Viewing 15 posts - 6,376 through 6,390 (of 10,143 total)