October 13, 2008 at 7:59 am
rgillings (10/13/2008)
I wouldn't feel right if I didn't offer a non-cursor alternative.
A while loop is little if at all better than a cursor. It's a cursor in all but name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 8:05 am
I have received major performance/response time gains by replacing a cursor with a while loop in a similar fashion to what I posted.
Additionally, by using a variable table, there is not the potential of leaving the cursor open if errors are not properly handled.
October 13, 2008 at 7:45 pm
rgillings (10/13/2008)
I wouldn't feel right if I didn't offer a non-cursor alternative.
Heh... it's still RBAR on steroids, though. A cursor with Forward Only Read Only will be just as fast and be similar in resource usage. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2008 at 7:57 pm
This is a "simple" :hehe: "running total" problem except instead of a running total that carries the previous balance of each row forward, we'll just carry the previous value of f2 forward...
Using Gail's test setup code...
drop table Products
Create table Products (
tempid int identity(1,1) PRIMARY KEY CLUSTERED, --<<LOOK! Notice the change here!!
F2 varchar(10)
)
INSERT INTO Products (F2) VALUES ('Car1')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES ('Car2')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES ('Car3')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES ('Car4')
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
INSERT INTO Products (F2) VALUES (NULL)
select * from Products ORDER BY TempID
--===== "Smear" the data "downwards" using the "Running Total" algorithm
DECLARE @PrevF2 VARCHAR(10),
@PrevTempID INT
SET @PrevF2 = ''
UPDATE Products
SET @PrevF2 = F2 = CASE WHEN F2 IS NULL THEN @PrevF2 ELSE F2 END,
@PrevTempID = TempID --Just an "anchor"
FROM Products WITH (INDEX(0))
SELECT * FROM Products ORDER BY TempID
This type of update will work on a million rows in about 7 seconds or less. For more information on this "running total" technique, please see the article at the following link...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2008 at 4:46 am
Jeff,
Thank you for posting your solution...I believe I will be able to gain much benefit from your posted running total logic.
October 14, 2008 at 5:21 am
rgillings,
Thanks for the feedback, I appreciate it. If you really want to get a handle on that particular logic, take a gander at the article in the link I posted above. It explains things like why you can't do this with a non-clustered index, and how to do "simple" running totals, grouped running totals, and how to use it to do "ranking" in SQL Server 2000 where there is no such thing.
Using "INDEX(0)" is a later manifestation of the code... don't need to know the name of the clustered index that way and that allows you to easily use this on temp tables where the PK constraint should never be explicity named.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2008 at 5:24 am
Thanks Jeff,
I already have the link bookmarked for future reference. I look forward to having a bit more time to fully read through and possibly implement the techniques.
Regards,
Ryan Gillings
October 28, 2008 at 1:30 pm
Using "INDEX(0)" is a later manifestation of the code... don't need to know the name of the clustered index that way and that allows you to easily use this on temp tables where the PK constraint should never be explicity named.
Jeff, you copmpletely lost me there, Would you please elaborate ?
October 28, 2008 at 8:14 pm
J (10/28/2008)
Using "INDEX(0)" is a later manifestation of the code... don't need to know the name of the clustered index that way and that allows you to easily use this on temp tables where the PK constraint should never be explicity named.Jeff, you copmpletely lost me there, Would you please elaborate ?
Sure... the method I used relies on a Clustered Index and I normally prefer the easy way of making one by creating a PK if it doesn't already exist. With that in mind, let's start with a quote from Books Online...
[font="Arial Black"]Arguments[/font]
INDEX ( index_val [ ,...n ] )
Specifies the name or ID of the indexes to be used by SQL Server when processing the statement. Only one index hint per table can be specified.
If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.
Ok... let's explain the on temp tables where the PK constraint should never be explicity named part. You shouldn't explicitly name a constraint on a Temp Table because (at least in SS2k) constraint names must be unique throughout the database. If you run the following in two different windows, two different copies of the temp table will be created...
CREATE TABLE #Test1
(RowNum INT IDENTITY(1,1), SomeColumn VARCHAR(20))
ALTER TABLE #Test1
ADD CONSTRAINT PK_Test1_RowNum PRIMARY KEY CLUSTERED (RowNum)
... but the code blows up with the following error when it tries to create the constraint on the second instance of #Test1...
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'PK_Test1_RowNum' in the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
Even if you try to name the constrait as part of the CREATE statement, it'll blow up on the second instance for the same reason.
CREATE TABLE #Test1
(RowNum INT IDENTITY(1,1), SomeColumn VARCHAR(20),
CONSTRAINT PK_Test1_RowNum PRIMARY KEY CLUSTERED (RowNum) )
Since indexes CAN be named the same, you could get away with this...
CREATE TABLE #Test1
(RowNum INT IDENTITY(1,1), SomeColumn VARCHAR(20))
CREATE UNIQUE CLUSTERED INDEX Test1_RowNum ON #Test1 (RowNum)
... and still be able to use WITH(INDEX(Test1_RowNum)) without anything blowing up. But, I like tables, even temp tables, to have a Primary Key. So, I'll often write some Temp Table setup/"running" code like this (and kind of explain the rest of my statement)...
CREATE TABLE #Test1
(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeColumn VARCHAR(20))
INSERT INTO #Test1 (SomeColumn)
SELECT SomeColumn
FROM SomeTable
WHERE yada-yada
DECLARE @somevariables somedatatypes
UPDATE #Test1
SET @Somevariable1 = SomeColumn = SomeExpression,
@DummyVariable = RowNum
FROM #Test1 WITH(INDEX(0))
Notice that I allowed SQL Server to name the PK so there's no constraint naming conflict. Because of that, I don't know the clustered index name for the WITH(INDEX()) directive (table hint), but I don't actually need to because WITH(INDEX(0)) says to "Scan the clustered index" without me actually knowing it's name.
Since I only put into a temp table what I want to get out of it, I know I want a clustered index scan on the whole temp table for things like running totals and data smears, so WITH(INDEX(0)) works just fine for me...
Dunno if I explained that all clearly... lemme know if you have any more questions.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2008 at 8:57 pm
There is another way to define constrains:
CREATE TABLE #Test1
(RowNum INT IDENTITY(1,1),
TypeID int,
Number VARCHAR(20),
PRIMARY KEY CLUSTERED (RowNum),
UNIQUE (TypeID, Number)
)
This way you also don't name them explicitly but it allows you include more than 1 column into constraint and gives you more of flexibility.
_____________
Code for TallyGenerator
October 28, 2008 at 9:45 pm
metalray (10/12/2008)
The data is imported froman excel file.
I know this is a SQL forum, but there are times when I, and this is just me, find it easier to do this kind of thing in Excel before I import it. So, I thought I would offer up another solution to the problem in Excel. Plus, we're all friends here right?
metalray, create a new sub in Excel. If you don't know how to do that, it is simple. Open up your spreadsheet, that is the source of your data. Press ALT and F11 keys simultaneously, and the VB editor will open up. Then, in the VB editor, copy and paste the below code, and save it. Please note, the example assumes column 1 is F2, and column 2 is some other data relevant to F2. You will need to change the macro to suit your two columns, but I have attached a file to show you how it works. Again, I am not meaning to impugn anyone's efforts, just offering up an alternative solution. Then, once you have changed the columns, click Tools>macro, and pick DragDownF2, and hit run.
Sub DragDownF2()
'Calculate the last row containing data, and populate
'the word "stop" in the next cell. This assumes that column
'A is F2, and that column B contains some data relevant to
'each car, and that there are values in column 2 past
'the last row that F2 is populated. You will need to change these.
StopRow = [B65535].End(xlUp).Row
Set MyRng = Range("A2:A" & StopRow)
Range("A" & StopRow + 1).Select
ActiveCell.Value = "stop"
'Now, drag down each value of F2 for each row where there
'is no value.
Range("a2").Select
Do Until ActiveCell.Value = "stop"
If ActiveCell.Value <> "" Then
Dim a As String
a = ActiveCell.Address
ActiveCell.Offset(1, 0).Activate
Count = 1
End If
Do Until ActiveCell.Value <> ""
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Activate
Count = Count + 1
End If
Loop
Dim b As String
ActiveCell.Offset(-1, 0).Activate
b = ActiveCell.Address
Range(a & ":" & b).FillDown
ActiveCell.Offset(1, 0).Activate
Loop
'Now, just get rid of the word "stop"
ActiveCell.Value = ""
End Sub
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 28, 2008 at 9:46 pm
Thanks Sergiy! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2008 at 10:10 pm
Greg Snidow (10/28/2008)
I know this is a SQL forum, but there are times when I, and this is just me, find it easier to do this kind of thing in Excel before I import it. So, I thought I would offer up another solution to the problem in Excel.
Couldn't you just do it with a simple formula and a copy in Excel?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2008 at 10:33 pm
Jeff Moden (10/28/2008)
Greg Snidow (10/28/2008)
I know this is a SQL forum, but there are times when I, and this is just me, find it easier to do this kind of thing in Excel before I import it. So, I thought I would offer up another solution to the problem in Excel.Couldn't you just do it with a simple formula and a copy in Excel?
Maybe, but I had that exact problem a while back, and there was no easy formula solution that I could find. The problem, logically, is the same for Excel, in that you have to tell each null cell what to do, even when the darned thing should know what I want it to do. Also, for this file in question, there were several other things I had to correct, so it made sense to do it in a macro stored in my PERSONAL.xls file, with a short cut key assigned to it...just open the file...hit ctrl+z...and BAM! No formulas to add every day. Plus, in case you haven't figured it out, often I have spare time on my hands...lots of spare time...so I like to try to do things just to see if they can be done, like my post today about needing syntax help.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 29, 2008 at 6:51 am
Jeff,
Dunno if I explained that all clearly... lemme know if you have any more questions
Let me get a cup of coffee first. I'll get back to you afterwards. Now, where is that damn coffee cup again...
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply