ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,981 through 4,995 (of 10,143 total)

RE: Assistance with this query

;WITH Employees AS (
SELECT TOP 1
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE
WHERE employee.ENDDATE IS NULL -- still active
ORDER BY ACTIONDATE DESC -- most recent
)
SELECT
e.*,
org.ORGANIZATIONID,
job.JOBTITLEID
FROM Employees e
LEFT...
^{“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 26, 2013 at 2:42 am

#1600652
RE: calculating period of time

astrid 69000 (3/26/2013)
Chris that but I need to add each row manually which I didn’t want to do (this is the one thing I know how to do lol.
Jayanth, I...
^{“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 26, 2013 at 2:22 am

#1600643
RE: Reorder a custom order column

matak (3/26/2013)
Expected output where @startNum < @oldnum (startnum 2 oldnum 4)
changedinitial
11
32
43
24
55
66
77
88
3 rows affected
Expected output where @startNum > @oldnum (startnum 4 oldnum 2)
changedinitial
11
42
33
24
55
66
77
88
Two rows affected
Are these both correct? The second sample...
^{“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 26, 2013 at 2:18 am

#1600638
RE: calculating period of time

Does this help?
;WITH MyData ([day], [Count]) AS (
SELECT '2008-01-01 00:00:00.000', 3 UNION ALL
SELECT '2008-01-01 11:00:00.000', 9 UNION ALL
SELECT '2008-01-01 22:00:00.000', 3 UNION ALL
SELECT '2008-01-02 09:00:00.000',...
^{“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 26, 2013 at 2:04 am

#1600631
RE: Performance Problem In a Procedure

Chandan, you're very welcome, thank you for your generous feedback.
This exercise, resolving a simple query from a more complex query referencing many more objects, often isn't successful because of cardinality...
^{“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 25, 2013 at 7:31 am

#1600301
RE: Deadlock on update command

bugg (3/25/2013)
ChrisM@Work (3/25/2013)
This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.
Right-click on the execution plan tab, 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

March 25, 2013 at 6:20 am

#1600264
RE: Deadlock on update command

OK got it. You need to change two indexes.
1. Include orderhash and status in index IX_SessionID, like this:
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha]
([sessionid] ASC, OrderHash ASC) INCLUDE (Status)
2....
^{“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 25, 2013 at 5:52 am

#1600246
RE: Deadlock on update command

This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.
Right-click on the execution plan tab, select "Save Execution...
^{“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 25, 2013 at 4:51 am

#1600225
RE: Deadlock on update command

bugg (3/25/2013)
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40...
^{“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 25, 2013 at 4:30 am

#1600219
RE: Deadlock on update command

bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:
Node:1
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000...
^{“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 25, 2013 at 3:42 am

#1600208
RE: Problem in 1 Billion Records Table

Si Thu (3/24/2013)
Hi,
I have one huge table which has 1Billion records. I tried to delete unnecessary records but it took a lot of time to delete and the delete was...
^{“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 25, 2013 at 2:19 am

#1600194
RE: Add variable number of rows into a table based on the values in another table (without cursors/while loops)

Lynn Pettis (3/22/2013)
Megha P (3/22/2013)
Hello ,
you can also try below
;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid =...
^{“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 22, 2013 at 10:11 am

#1599857
RE: Performance Problem In a Procedure

It's difficult to tell with all of those funky joins and no sample data, but you might get a win with this too;
SELECT CAST(ID AS NUMERIC(9))
INTO #Accounts
FROM dbo.SplitIDs(@vcAccountId, ',')
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

March 22, 2013 at 8:33 am

#1599798
RE: Performance Problem In a Procedure

Most folks put in this situation will look for a quick win to buy the time necessary for the complete overhaul which Grant correctly recommends.
I think you have some...
^{“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 22, 2013 at 7:57 am

#1599782
RE: Performance Problem In a Procedure

The single biggest cost is a function which references a view which references a function which unnecessarily uses a slow cursor to concatenate some columns. Or something similar. A half...
^{“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 22, 2013 at 6:44 am

#1599747

Viewing 15 posts - 4,981 through 4,995 (of 10,143 total)