Program sometime stuck when select records from INFORMATION_SCHEMA.COL

Question

Hi,

I am encountering issue when

selecting records from INFORMATION_SCHEMA.COLUMNS

sometimes program gets stucks.

Also no error in log and re-run no issue.

Please advise for a better alternative/approach are welcome.

Regards

Shehzad

Tuhin Paul · Answer

Asynchronous methods can improve the responsiveness of your application by allowing it to perform non-blocking I/O operations. However, it's crucial to use them judiciously and understand their implications, such as potential thread pool exhaustion or increased complexity in error handling. In addition to using asynchronous methods for I/O-bound operations, consider parallelizing CPU-bound operations using techniques such as parallel LINQ (PLINQ) or the Task Parallel Library (TPL). This can help utilize multi-core processors and improve overall performance.

Tuhin Paul · Answer

Before optimizing queries and using asynchronous methods, it's essential to identify the specific bottlenecks causing the program to get stuck. This may involve profiling the application to pinpoint areas of slow performance or using monitoring tools to analyze database queries and their execution times. Once you've identified the bottleneck queries, focus on optimizing them by adding appropriate indexes, rewriting complex queries, or using query hints to improve query execution plans. Database tuning tools and query analyzers can assist in this process.

Shehzad Khan · Answer

Hi Jayraj, Thanks for replying, Let me check, In where filter we have table_name and column_name, Howvever this is a system view where we are unable to create index Regards Shehzad

Jayraj Chhaya · Answer

When your program gets stuck without any errors, it could be due to various reasons such as blocking operations, deadlocks, or long-running queries. To troubleshoot this issue, you can start by checking the database performance, query execution time, and any locks on the tables being accessed. One approach is to optimize your query by adding appropriate indexes to the columns being used in the SELECT statement. Additionally, consider using asynchronous methods to prevent blocking the main thread. Here's an example of using asynchronous methods in C# to execute a query: using System; using System.Data.SqlClient; using System.Threading.Tasks; public async Task GetDataAsync() { string connectionString = "YourConnectionString"; string query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"; using (SqlConnection connection = new SqlConnection(connectionString)) { await connection.OpenAsync(); using (SqlCommand command = new SqlCommand(query, connection)) { SqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { // Process data here } } } } By optimizing your queries and using asynchronous methods, you can improve the performance and prevent your program from getting stuck during database operations.

Shehzad Khan · Answer

Hi Tuhin, Thanks for sharing, Can u share some example of parallel LINQ (PLINQ) or the Task Parallel Library (TPL). Regards Shehzad