Sorry I was away from my office and was not able to reply I used microsoft Just in time way data loading to fill my datagridvie. with the data. Here is the link http://msdn.microsoft.com/en-us/library/ms171624.aspx Can you please let me know what is going on. If you like to see more code, I dont mind sending you the classes for the search. database table contains 20-30 millions of records, partioned, well-idexed. SQL Server takes 1-2 seconds to display results. Thank you query_str = _ "CREATE PROCEDURE data_" & Environment.UserName & " AS Select Top " & rowsPerPage & " " + _ CommaSeparatedListOfColumnNames & " From " & tableName + _ condition & " WHERE " & columnToSortBy & " NOT IN (SELECT TOP " & _ lowerPageBoundary & " " & columnToSortBy & " From " + _ tableName & condition & " ORDER BY " + _ orderByClause.Remove(orderByClause.Length - 2) + _ ", " & columnToSortBy + _ ") Order By " & orderByClause.Remove(orderByClause.Length - 2) + _ ", " & columnToSortBy Dim table As New DataTable() Try 'drop the stored procedure thats if it exist Dim toDrop As String = "If object_id('data_" & Environment.UserName & "') is not null drop procedure data_" & Environment.UserName Dim dropCmd As SqlCommand = New SqlCommand(toDrop, connection) dropCmd.ExecuteNonQuery() 'create the stored procedure Dim sqlCmd As New SqlCommand(query_str, connection) sqlCmd.ExecuteNonQuery() 'EXECUTE THE STORED PROCEDURE command = New SqlCommand("data_" & Environment.UserName, connection) command.CommandType = CommandType.StoredProcedure Dim dr As SqlDataReader = command.ExecuteReader table.Load(dr) // this is taking too long 'command.CommandText = "EXEC data_" & Environment.UserName 'adapter.SelectCommand = command 'table.Locale = System.Globalization.CultureInfo.InvariantCulture 'adapter.Fill(table) // this is taking long 'drop the procedure sqlCmd = New SqlCommand("DROP PROCEDURE data_" & Environment.UserName, connection) sqlCmd.ExecuteNonQuery() Catch ex As Exception 'drop the procedure Dim sqlCmd As New SqlCommand("DROP PROCEDURE data_" & Environment.UserName, connection) sqlCmd.ExecuteNonQuery() MessageBox.Show(ex.Message & ". You might see some records", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) _timeOutOccured = True End Try Return table End Function
> > > > > > -- You received this message because you are subscribed to the Google Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web Services,.NET Remoting" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en or visit the group website at http://megasolutions.net
