Hi
I am currently working on very large table. The idea is to split IP address nvarchar column to 4 bytes tiny-integer.
I want to run SQL script to update all the rows.
This is my code
public override void Up()
{
AddColumn("dbo.IDConnectionPulses", "ip1", c => c.Byte(nullable: false));
AddColumn("dbo.IDConnectionPulses", "ip2", c => c.Byte(nullable: false));
AddColumn("dbo.IDConnectionPulses", "ip3", c => c.Byte(nullable: false));
AddColumn("dbo.IDConnectionPulses", "ip4", c => c.Byte(nullable: false));
Sql(@"
DECLARE @ip NVARCHAR(128)
DECLARE @id INT
DECLARE MY_CURSOR CURSOR
FOR
SELECT Id,PulseIPAddress FROM IDConnectionPulses
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @id,@ip
WHILE @@FETCH_STATUS = 0
BEGIN
-- split ipaddress by char_index and store each byte
DECLARE @idx1 INT, @idx2 INT, @idx3 INT, @idx4 INT
DECLARE @ip1 TINYINT, @ip2 TINYINT, @ip3 TINYINT, @ip4 TINYINT
SET @idx1 = CHARINDEX('.', @ip)
SET @idx2 = CHARINDEX('.', @ip, @idx1+1)
SET @idx3 = CHARINDEX('.', @ip, @idx2+1)
SET @idx4 = CHARINDEX('.', @ip, @idx3+1)
SET @ip1 = CONVERT(TINYINT,SUBSTRING(@ip, 0, @idx1))
set @ip2 = CONVERT(TINYINT,SUBSTRING(@ip, @idx1 + 1, @idx2 - @idx1-1))
set @ip3 = CONVERT(TINYINT,SUBSTRING(@ip, @idx2 + 1, @idx3 - @idx2 - 1))
set @ip4 = CONVERT(TINYINT,SUBSTRING(@ip, @idx3 + 1, LEN(@ip) - @idx3))
UPDATE IDConnectionPulses SET ip1 = @ip1, ip2=@ip2, ip3=@ip3, ip4=@ip4 WHERE Id = @id
FETCH NEXT FROM MY_CURSOR INTO @id,@ip
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR");
DropPrimaryKey("dbo.IDConnectionPulses");
DropColumn("dbo.IDConnectionPulses", "Id");
DropIndex("dbo.IDConnectionPulses", "IX_ConnectionPulse_QuickLookup");
AlterColumn("dbo.IDConnectionPulses", "Timestamp", c => c.DateTime(nullable: false, precision: 7, storeType: "datetime2"));
CreateIndex("dbo.IDConnectionPulses", new[] { "IntendedConnectionId", "Timestamp" }, clustered: true, name: "IX_ConnectionPulse_QuickLookup");
DropColumn("dbo.IDConnectionPulses", "PulseIPAddress");
AddPrimaryKey("dbo.IDConnectionPulses", new[] { "IntendedConnectionId", "Timestamp" });
}
I am getting timeouts using the Entity Framework (EF) when using this funtion.