Use MSSQL CLR to retrieve data as json

Question

I have a table with more than 1 million records and im using these record to generate some reports using crystal reports, but when selecting large number of records some time occur timeout errors or sometime computer getting stuck,

I already used index and I'm retrieving data using Stored procedures.

There are several tables that joining with main table(that have 1mil records) and also data will group inside the report.

So I'm asking cant we use MSSQL CLR to get these data from data base by compressing or converting to light objects.

Answer 1

There are 2 separate issues in your post that are unlikely to be solved by a CLR solution:

• Timeout

There are no details about where the timeout actually occur (on the rdbms while performing the selection, on the client side while retrieving the data, in the report engine while actually building the report) so I suppose that the timeout occur on the rdbms.
Building a CLR library will not improve the time required to gather the data.
The solution is indexing (as you already did) and query plan analyzing to identify bottlenecks and issues.

• Computer getting stuck

This looks like an issue related to the amount of data that makes the machine struggle and there is very little you can do.
Once again a CLR library on the server will not lower the amount of data handed to che client and imho would only make the situation worse (the client would receive compressed data to uncompress: an additional heavy task for an already overloaded machine).
Your best bet are increase the amount of ram, buy a better cpu (higher speed and/or more cores), run the reports scheduled and not interactively.