Viewing 15 posts - 13,591 through 13,605 (of 14,953 total)
This still confuses me as the CPU and Elapsed Time seem to go in different directions ?!?!?!
This often happens when one query plan doesn't convince SQL Server to use parallel...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 1:17 pm
Have you tried turning those row_number sub-queries into aggregates? I've tried both, and sometime the aggregates are faster.
Something like this:
;with
-- Publication Version CTE, used for Pub CTE
PubVersion (DocID,...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 1:12 pm
DBA (5/21/2008)
SELECT len as Length, p.productnumber,p.productID_PK AS ID
FROM Products P
WHERE p.Active = '0'
(p.productname)...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 12:49 pm
Jack: Yeah, I saw your posts, and didn't want to be redundant on that point. I totally agree with you, just wanted to add a couple of possibly pertinent...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 12:45 pm
Another solution to this is don't bother updating the base table, just use Row_Number() in the queries if you want to show that sequence.
If you do it the other way,...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 12:44 pm
When you say that the syntax you've come up with isn't working, what exactly do you mean? Do you get a syntax error on it? Does it not...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 12:40 pm
The quick answer is update the table using RTrim.
update dbo.Table
set Column = rtrim(Column)
Since you're using Varchar data type, that will probably do it.
If it doesn't, the thing to do is...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 12:30 pm
Keep in mind that an inline function (like these) has an overhead to it. With something as simple as the dateadd version for this, you're better off including it...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 11:11 am
Matt Miller (5/21/2008)
GSquared (5/21/2008)
The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 10:33 am
Steve Jones - Editor (5/20/2008)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 9:48 am
pduplessis (5/21/2008)
Syntax:
declare @table table(identifier int identity(1,1), ModuleID tinyint)
insert into @table (ModuleID) values(1)
insert into @table (ModuleID) values(2)
insert into @table (ModuleID) values(3)
declare @counter tinyint, @ModuleID tinyint, @string varchar(max),...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 9:33 am
On the syntax error, I can't duplicate it. I tested the code, and it compiled and ran just fine. Did you copy-and-paste the code from this forum into...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 9:32 am
These two:
AND tbl_dxu_templatedetails.TemplateID = 24
AND tbl_DXU_LookUp.TemplateID = 24
Need to be in the Join criteria, not in Where clause. I'm not sure...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 9:22 am
ddnrg (5/21/2008)
Does the new SQL2008 HeirarchyId pretty much replace the need for doing this?
That one, per what I've read, is fast to query, but slow to update/insert/delete. Adjacency hierarchies...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 7:50 am
I've used hierarchies with as many as 7,000 nodes and as many as 50 levels. CTEs work well for those.
But one thing that should be brought up in any...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 7:43 am
Viewing 15 posts - 13,591 through 13,605 (of 14,953 total)