Using PrimaryKey slows me down,why?

[ralphtrent]

Hi
I am saving data from an inputfile in 2 different datatables. I need to search these datatables to see if the row I am going to add already exists. To improve performance, I created primary keys on these datatables. One primarykey is 4 columns, the other is 3. When I add the primarykey to the datatable, the code runs in 8 minutes, when I take it out, it runs in 17 seconds. Of course I lose the contraint requirments i need but any idea why this would happen?

Thanks,
RalphTrent

 

[craigber]

How exactly did you create the primary key? Have you set that key as a clustered index?

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com/blog

 

[ralphtrent]

Thanks for the reply.

DataTable.PrimaryKey = new DataColumn[3] { DataTable.Columns["Col1"], ldtTransaDataTablections.Columns["Col2"], ldtTransactDataTableions.Columns["Col3"] };

How do I set that key as a clustered index? I did not know you can do that on a datatable. I thought only in a database table.

 

[ralphtrent]

I did some debugging and the problem looks to be realted to updating a row in one of my tables. I do not have the exact code infront of me but here is an idea of what i am doing

dtTable2column1Value = "NAME";
dtTable2column2Value = 1024;
dtTable2column3Value = 53;
[green]
//dtTable1 store StoredProcedure name, total running time, total rows returned...
[/green]
DataRow drFindRow1 = dtTable1.Rows.Find(new object[4]{PrimayKeyColumn1Value, PrimayKeyColumn2Value, PrimayKeyColumn3Value, PrimayKeyColumn4Value})
[green]
//dtTable2 stores StoreProcedure Name, UniqueParameters (I MD5 the parameters to make it easier to store), Databse SPID and Databse PID
[/green]
DataRow drFindRow2 = dtTable2.Rows.Find(new object[4]{PrimayKeyColumn1Value, PrimayKeyColumn2Value, PrimayKeyColumn3Value, PrimayKeyColumn4Value})


if (drFindRow == null)
{
[green]// ADD_NEW_ROW[/green]
}
else
{
[green]// I need to increment this column's value by 1[/green]
drFindRow["ColumnToUpdate1"] = Convert.ToInt32(drFindRow["ColumnToUpdate1"]) + 1;

[green]//I need to add additional time to this column, which is a timestamp
[/green]
drFindRow["ColumnToUpdate2"] = ((TimeStamp)drFindRow["ColumnToUpdate2"]).Add(TimeStampValue);
[green]
//I need to add additional time to this column, which is a timestamp
[/green]
drFindRow["ColumnToUpdate3"] = ((TimeStamp)drFindRow["ColumnToUpdate3"]).Add(TimeStampValue);
[green]
//// THIS IS WHERE THE SLOW DOWN IS

//Now I need to get a total count of how many records are in my other table for the same SP Name, SPID and PID
[/green]
drFindRow["ColumnToUpdate4"] = dtTable2.Select("COLUMN1 = '" + dtTable2column1Value + "' and COLUMN2 = " + dtTable2column2Value +" and COLUMN3 = " + dtTable2Column3Value).Length
}

Any idea's why this is slowing me down.  I must do a select since I need to get a total number of rows by ProcedureName,spid and pid only.  Not by Parameters.

Thanks,
RalphTrent