DataTable.Select("Column is null") very slow
-
Hi I came across this problem while working on a loop which selects from a datatable. I am selecting from a datatable which has about 31K records. I am checking to see if a column value is null. If I am not selecting based on COLUMN IS NULL, then datatable.Select method works nice. However, if I look for null column values, the speed decreases substantially(i.e from seconds to about 30 to 40 minutes). I found a work around by replacing all null values with empty strings which solved the problem. However, if datatable column type was something other than string then this solution would not work. I was wondering if someone has an idea about this. Here is the sample of my code: I have replaced the dt datatable col4 values with empty strings for now(for better performance. I want to find a better way of doing select). As a result, the code looks like this. foreach (DataRow dr in dt.Rows)//31K records { col1 = dr["col1"].ToString().Trim(); col2 = dr["col2"].ToString().Trim(); col3 = dr["col3"].ToString().Trim(); col4 = dr ["col4"].ToString().Trim(); if (col4 == "") // if column4 is null then use this { recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 = ''"); //recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 is null"); <--very slow due to col4 is null } else { recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " col3 = " + "'" + col3 + "'" + " And col4 = " + "'" + col4 + "'"); } }
Thanks Needy
-
Hi I came across this problem while working on a loop which selects from a datatable. I am selecting from a datatable which has about 31K records. I am checking to see if a column value is null. If I am not selecting based on COLUMN IS NULL, then datatable.Select method works nice. However, if I look for null column values, the speed decreases substantially(i.e from seconds to about 30 to 40 minutes). I found a work around by replacing all null values with empty strings which solved the problem. However, if datatable column type was something other than string then this solution would not work. I was wondering if someone has an idea about this. Here is the sample of my code: I have replaced the dt datatable col4 values with empty strings for now(for better performance. I want to find a better way of doing select). As a result, the code looks like this. foreach (DataRow dr in dt.Rows)//31K records { col1 = dr["col1"].ToString().Trim(); col2 = dr["col2"].ToString().Trim(); col3 = dr["col3"].ToString().Trim(); col4 = dr ["col4"].ToString().Trim(); if (col4 == "") // if column4 is null then use this { recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 = ''"); //recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 is null"); <--very slow due to col4 is null } else { recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " col3 = " + "'" + col3 + "'" + " And col4 = " + "'" + col4 + "'"); } }
Thanks Needy
did you try
col1 = dr["col1"].ToString().Trim(); col2 = dr["col2"].ToString().Trim(); col3 = dr["col3"].ToString().Trim(); if( dr["col4"] == DBNull.Value ) { // Do Stuff recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 = ''"); } else { col4 = dr ["col4"].ToString().Trim(); recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " col3 = " + "'" + col3 + "'" + " And col4 = " + "'" + col4 + "'"); } -
did you try
col1 = dr["col1"].ToString().Trim(); col2 = dr["col2"].ToString().Trim(); col3 = dr["col3"].ToString().Trim(); if( dr["col4"] == DBNull.Value ) { // Do Stuff recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 = ''"); } else { col4 = dr ["col4"].ToString().Trim(); recordsSelected = dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " col3 = " + "'" + col3 + "'" + " And col4 = " + "'" + col4 + "'"); }I already tried that. If you read my post I have that select line already in my code. I want to find out a work around. I replaced all nulls to the empty strings. However, this approach may not work if a column i am looking for is not string. As a result, if someone knows a better way or other way to select if values are null in datatable.
Thanks Needy
-
I already tried that. If you read my post I have that select line already in my code. I want to find out a work around. I replaced all nulls to the empty strings. However, this approach may not work if a column i am looking for is not string. As a result, if someone knows a better way or other way to select if values are null in datatable.
Thanks Needy
bemahesh wrote:
I already tried that. If you read my post I have that select line already in my code. I want to find out a work around. I replaced all nulls to the empty strings. However, this approach may not work if a column i am looking for is not string. As a result, if someone knows a better way or other way to select if values are null in datatable.
I can't image that a real null value in the column, and comparing that to
DBNull.Valuewould take longer than doing the string comparision you are doing now. What you are doing sounds like bad design work. I think you need to go back, and set all those columns to null, and compare them toDBNull.Value."If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer I get all the news I need from the weather report - Paul Simon (from "The Only Living Boy in New York")
-
bemahesh wrote:
I already tried that. If you read my post I have that select line already in my code. I want to find out a work around. I replaced all nulls to the empty strings. However, this approach may not work if a column i am looking for is not string. As a result, if someone knows a better way or other way to select if values are null in datatable.
I can't image that a real null value in the column, and comparing that to
DBNull.Valuewould take longer than doing the string comparision you are doing now. What you are doing sounds like bad design work. I think you need to go back, and set all those columns to null, and compare them toDBNull.Value."If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer I get all the news I need from the weather report - Paul Simon (from "The Only Living Boy in New York")
If I was not clear enough, the problem is when i am trying to select the data from datatable using datatable.select(). If i have null value in one of the column in datatable and i am selecting from the datatable like: datatable.select("col4 is null"). That is where it takes longer time. Please, look at my original post this line is not creating overhead: if (col4 == "") // if column4 is null then use this But, this line does: dt.Select("col2 = " + "'" + col2 + "'" + " And col1 = " + "'" + col1 + "'" + " And col3 = " + "'" + col3+ "'" + " And col4 is null");
Thanks Needy