January 5, 2009 at 10:36 am
jacroberts (1/5/2009)
There are no indexes useful to these queries on the WeblogEvents table. All queries do a full table scan and it looks like the UNION ALL one does two.
I find it really interesting that the UNION ALL query is so much quicker on my box than it is on the main DB. But I think I have a feel for what is going on - I think for the smaller amount of rows, my box may be choosing to use statistics for the scans, wheras on your box the sheer cost of the scan (given the table size) causes it to choose otherwise (or perhaps the same statistics don't exist?).
But that's just my gut feel on this one - totally uneducated guess!
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 12:58 pm
I haven't been here in a long, long time. [ The pages look radically different now and I am not certain of all the capabilities ]
I hope this was not discussed in the 17 plus pages as I did not make time to go through all of this.
First, I have tried this and replaced many of our cursors. It is a reasonable approach.
We have one last cursor used for an unusual "counting" routine we must follow. I attempted to replace it with this method, but it is slower than using the cursor.
I have tried this with @TableVariables, but found in this case, #TempTables were better as you cannot Index a @TableVariable - that did speed things up significantly.
I have the two approaches with about 13,000 records for testing. But(!), I do not know how to post them without blowing through a huge page. Any help?
I wasn't born stupid - I had to study.
January 5, 2009 at 1:39 pm
Farrell Keough (1/5/2009)
I have the two approaches with about 13,000 records for testing. But(!), I do not know how to post them without blowing through a huge page. Any help?
Attach them in a ZIP file.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 2:11 pm
RBarryYoung (1/5/2009)Attach them in a ZIP file.
Thanks. Let's see - where would the button be to attach a file?
Got it. "Edit Attachments" Unusual button name as I had not attached anything...
I wasn't born stupid - I had to study.
January 5, 2009 at 2:57 pm
Farrell Keough (1/5/2009)
RBarryYoung (1/5/2009)Attach them in a ZIP file.
Thanks. Let's see - where would the button be to attach a file?
Got it. "Edit Attachments" Unusual button name as I had not attached anything...
You should explain what this script is supposed to do. I looked at it and it is not very clear what the functional effect of the procedural logic is supposed to be.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 3:21 pm
Farrell Keough (1/5/2009)
...as you cannot Index a @TableVariable...
I thought that until Jeff Moden set me straight. You can declare a table variable like this
declare @myTable TABLE (ID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED, SomeValue int, SomeOtherValue varchar(50) ... )
I have found that makes table variables about 80 times more useful for me.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 3:33 pm
Farrell Keough (1/5/2009)
We have one last cursor used for an unusual "counting" routine we must follow. I attempted to replace it with this method, but it is slower than using the cursor.
OK, if I understand what this is trying to do, then the following routine is approximately 10x faster:
declare @dat datetime; set @dat = getdate()
print 'start: '+convert(varchar(23), @dat, 121)
update c
set count = 0
From (Select id, , alpha, beta, gamma, [type], [count]
, ROW_NUMBER() OVER(Partition By [count],
Order By [Type], [Alpha], [Beta], [Gamma], [id])
AS KeyRowCount
From ccount) c
Where KeyRowCount > 1
-- And [COUNT] <> 0 --this MAY make it faster...
print 'done: '+convert(varchar(23), getdate(), 121)
+', ms:' + cast(datediff(ms,getdate(),@dat) as varchar)
It appears to produce the correct results on my DB, but you should check it to be sure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 3:48 pm
Matt Whitfield (1/5/2009)
I thought that until Jeff Moden set me straight. You can declare a table variable like thisdeclare @myTable TABLE (ID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED, SomeValue int, SomeOtherValue varchar(50) ... )
I have found that makes table variables about 80 times more useful for me.
Thank you! I really like @TableVariables and found the inability to Index a real let down.
RBarryYoung (1/5/2009)
OK, if I understand what this is trying to do, then the following routine is approximately 10x faster:
declare @dat datetime; set @dat = getdate()
print 'start: '+convert(varchar(23), @dat, 121)
update c
set count = 0
From (Select id, , alpha, beta, gamma, [type], [count]
, ROW_NUMBER() OVER(Partition By [count],
Order By [Type], [Alpha], [Beta], [Gamma], [id])
AS KeyRowCount
From ccount) c
Where KeyRowCount > 1
-- And [COUNT] <> 0 --this MAY make it faster...
print 'done: '+convert(varchar(23), getdate(), 121)
+', ms:' + cast(datediff(ms,getdate(),@dat) as varchar)
It appears to produce the correct results on my DB, but you should check it to be sure.
Thank you. I will try this tomorrow.
Sorry for the lack of explaination. No excuse!
Obviously it has been too long since I was here last.
I wasn't born stupid - I had to study.
January 5, 2009 at 3:58 pm
here's a ss2000 compatible version, using an indexed table variable for good measure 🙂
DECLARE @countordered TABLE ([RowNum] INT IDENTITY (1,1) primary key clustered,
[id] integer NOT NULL,
[Key] varchar(48) NULL,
[Count] smallint NULL,
[Alpha] varchar(80) NULL,
[Beta] varchar(80) NULL,
[Gamma] varchar(80) NULL,
[Type] varchar(15) NULL )
INSERT INTO @countordered ([Key], [Count], [id], [Alpha], [Beta], [Gamma], [Type])
SELECT [Key], [Count], [id], [Alpha], [Beta], [Gamma], [Type]
FROM #Count
ORDER BY [Count] DESC, [Key], [Type], [Alpha], [Beta], [Gamma], [id]
update c
set [count] =0
from #count c INNER JOIN
(select co2.id
from @countordered co1 LEFT OUTER JOIN
@countordered co2 on co1.rownum = co2.rownum - 1
WHERE co1. = co2.) sameData
on sameData.id = c.id
where [count] != 0
drop TABLE #count
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 4:02 pm
Hmmm..., I will need multiple keys for the Update. Can the @TableVariable be made with more than just the Primary Index? i.e., Cluster multiple key Index?
I wasn't born stupid - I had to study.
January 5, 2009 at 4:08 pm
OK, on my box, I am getting the following results:
Original cursor: 410ms
RowNum (mine): 45ms
Identity (Matt's): 123ms
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 4:09 pm
not afaik but Jeff will correct me if there is a way to do it.
You can create a unique constraint across multiple columns, but not a clustered index over multiples...
One question though - in the provided data, ID was unique - is that not generically the case?
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 4:10 pm
RBarryYoung (1/5/2009)
OK, on my box, I am getting the following results:Original cursor: 410ms
RowNum (mine): 45ms
Identity (Matt's): 123ms
I can imagine that's about what you'd get - i was doing it on ss2000 though so couldn't test yours. Would def. go for your version if running on 2005+...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 4:12 pm
That is correct. The ID may be split and require the other fields to be truly unique.
This dataset "may" not have that. I was just going for the most data possible that I could actually work with to get enough to see a significant difference.
I wasn't born stupid - I had to study.
January 5, 2009 at 4:13 pm
Matt Whitfield (1/5/2009)
Farrell Keough (1/5/2009)
...as you cannot Index a @TableVariable...I thought that until Jeff Moden set me straight. You can declare a table variable like this
declare @myTable TABLE (ID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED, SomeValue int, SomeOtherValue varchar(50) ... )
I have found that makes table variables about 80 times more useful for me.
1) I see people do this regulary just out of habit and NOT use the ID column or use it in such a way that indexing isn't useful. They waste the overhead of creating the clustered index for nothing.
2) regarding a later comment you made about really liking table variables, in many instances they perform SIGNIFICANTLY worse than temp tables. I probably made $25-30K last year identifying and fixing this one issue for my clients. The inability to have statistics causes the optimizer to have HORIBLE plans from a performance perspective. On some few occassions the lack of index capability makes repeated table variable hits extraordinarily expensive too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 166 through 180 (of 272 total)
You must be logged in to reply to this topic. Login to reply