Help! Indexing LARGE table

OK, I imported 680 million records into an unindexed table. That went well.

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Help! Thanks in advance!OK, I imported 680 million records into an unindexed table. That went well.

Well what? Into the night?

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

Yeah, you'd want to do that with almost a billion records (hey let's not split hairs)

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

Pray?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Nice box...do you have to buy it dinner?

Help! Thanks in advance!

Help with what? You defenitley DONT want to kill it...it'll take twice as long to rollback.

Seems you get a lot of these. I would definetly start padding my estimates...

OR, I would think about performance before...

Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

But that's just me

OH

MOO

Good Luck|||I agree with Brett that killing the job will be bad. A clustered index looks to order the rows of the table on the disk itself. If SQL Server decides that these rows are in exactly the revers order of what they should be, then you are going to see an awful lot of data move around. Your transaction log is probably going nuts, too.
As for speeding it up, there isn't a lot you can do while the process is in flight. If this import is a normal thing, then you can consider partitioning the table on some unique key, and cutting it down to seven 100 million row tables. Also, for non-clustered indexes, you can get a boost from the SORT_IN_TEMPDB option. Take a look in BOL for that.|||Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

Yes, horizontal partitioning will be a necessary move. My data isn't primarily organized by date so that wouldn't make an ideal partition criteria. I haven't done this before and am nervous about doing it correctly; mistakes at the planning phase will be hard to correct.

Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.|||ok, it just finished rolling back. The data file was on a 150GB SAN partition and ran out of space. It was previously like 73GB so I'm surprised that happened. any way, at least I have the database back...

thanks guys|||Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.

Well I was trying to get you to laugh..not to make fun of you

Why not post the DDL of the table...|||Got me to laugh, anyway. Does that count as a near miss?

Roger: At this point, maybe you can try the index as a non-clustered index. Let us know what happens.|||Is there any chance the import file is already sorted? If so you can create the clustered index on the empty table and use the ORDERED argument with BCP to prevent SQL from resorting the data.|||Well I was trying to get you to laugh..not to make fun of you

Brett's gone off to Flordia for vacation. We can make fun of him while he is gone. :p

Help with writing sql statement

I need some help writing a query. I have a text file that will be imported on a weekly basis, with 1000+ rows and 5 columns. This i need to import into table tblECR which i have added 2 of my own columns.

The problem is the text file will arrive with current data and new data. The current data may or may not have changed (dates, status etc). How do i go about importing the new data and updating the existing data with the new fields, without deleting the data in the 2 columns i've added. I'm using vs 2005 with a sql 2005 express database.

This is the code i'm using to import the data currently. Clicking the button more than once will obviously just import all the data into the database again.

Private Sub CustomerDataToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerDataToolStripMenuItem.Click

'Clear the dataset

dsimport.Clear()

'Set the file variables

Dim strFileName As String

Dim strFilePath As String

Dim sSlash As Single

'Open the file dialog and select the text file to open

Try

With OpenFileDialog1

'Set the initial dialog options

.Title = "Import Customer data file"

.InitialDirectory = "P:\Ian\"

.FileName = ""

.Filter = "File (*.csv)|*.csv|All files (*.*)|*.*"

If OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then

Else

MessageBox.Show("No file was selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Sub

End If

'Strip the filename into its seperate portions

sSlash = InStrRev(.FileName, "\")

strFilePath = Mid(.FileName, 1, CInt(sSlash))

strFileName = Mid(.FileName, CInt(sSlash + 1), Len(.FileName))

End With

'Set the connection properties to read the text file

Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""

Dim conn As New OleDb.OleDbConnection(strConnectionString)

'Open connection with the database.

conn.Open()

'Create new OleDbCommand to return data from the text file

Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM [" & strFileName & "]", conn)

' Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement

Dim objAdapter1 As New OleDb.OleDbDataAdapter

'Pass the Select command to the adapter

objAdapter1.SelectCommand = objCmdSelect

'Fill the DataSet with the information from the file

objAdapter1.Fill(dsimport, "Import")

objAdapter1.AcceptChangesDuringFill = False

'Clean up objects

conn.Close()

Catch ex As Exception

MsgBox(ex.Message).ToString()

Exit Sub

End Try

'Now import the data into the table

Dim sqlcn As New SqlConnection(ConnString)

Dim sqlcmd_ECR As New SqlCommand

Dim dr As DataRow

Try

sqlcn.Open()

'Setup the sql command to enter data into the ECR table

sqlcmd_ECR.Connection = sqlcn

sqlcmd_ECR.CommandText = "Insert into tblECR_Test(ECR_No,Aims_No,ECR_Type) Values(@.a,@.b,@.c)"

'Setup the sql parameters to enter data into the ECR table

sqlcmd_ECR.Parameters.Add("@.a", SqlDbType.Int)

sqlcmd_ECR.Parameters.Add("@.b", SqlDbType.Int)

sqlcmd_ECR.Parameters.Add("@.c", SqlDbType.VarChar, 255)

Try

For Each dr In dsimport.Tables(0).Rows

sqlcmd_ECR.Parameters("@.a").Value = dr(0)

sqlcmd_ECR.Parameters("@.b").Value = dr(1)

sqlcmd_ECR.Parameters("@.c").Value = dr(2).ToString()

sqlcmd_ECR.ExecuteNonQuery()

Next

Catch ex1 As SqlException

MsgBox(ex1.Message).ToString()

Exit Sub

End Try

MessageBox.Show("The text file was successfully imported.", "Customer data import", MessageBoxButtons.OK, MessageBoxIcon.Information)

Catch ex3 As Exception

MsgBox(ex3.Message).ToString()

End Try

sqlcn.Close()

End Sub

ExecuteNonQuery returns an integer that tells you how many rows were affected by the query, so you could run an update first (trying to update the record assuming it is already there) and then if the rows affected is 0 instead of 1, run the insert.