-1

this is my Problem,

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 erorros or sometime computer getting stuck,

i already used index and im 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 im asking cant we use MSSQL CLR to get these data from data base by compressing or converting to light objects, if anyone have an idea will be appreciate..

thanks

Thilanka Ishara Gunathilake
  • 290
  • 4
  • 18
  • if the amount of data is huge adding a layer (the one to 'translate' to json) will likely slow all the processing. do you have any evidence and/or information that suggest that a conversion to json will improve performances? maybe you could process the data on sql before exporting it in your software? – Paolo Jun 24 '15 at 06:57
  • ok, if json will increase the processing weight, can we encrypt or compress data inside sql so then import to our application? im using stored procedures to generate data but if the amount of data high it will trow time out erorr – Thilanka Ishara Gunathilake Jun 24 '15 at 07:03
  • please add all the details at once in the post. you mention stored procedures, timeout error, encryption and compression as a side note in a comment. what are you looking for exactly? please describe the problem with all the relevant details. please modify the post and don't put details in the comments. – Paolo Jun 24 '15 at 07:06
  • ok done, not its clear – Thilanka Ishara Gunathilake Jun 24 '15 at 08:41

1 Answers1

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 analizing to identify bottlenecks and issues.
should you have provided any sql code it would have been possible to give you some relevant hint.

  • 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.

there are no technical details at all in the post so all the above are more or less wild guesses; should you need some more detailed answer and advice please post another question with technical details about the issues.
https://stackoverflow.com/help/how-to-ask

Community
  • 1
  • 1
Paolo
  • 2,224
  • 1
  • 15
  • 19