Welcome Guest, Not a member yet? Register   Sign In
PHP MS SQL large results.
#1

Hello CodeIgniter Developers,


I just want to ask for suggestion about the large result set in MS SQL SERVER. I use CodeIgniter and MS SQL as the database.

Now I am working with 26Million records. 

This is a sales report. So basically you would select a customer then select the year range (example: 2015 - 2016)

And then I will output a table containing the result set. But as I click search, the browser takes to long and then suddenly freezes.


Do you have any idea how can I make it more usable? Users would be frustrated because of this.


If you have worked in the same problem before please let me know how you solved this. Thanks in advance! Smile
Reply
#2

(This post was last modified: 12-13-2017, 01:56 PM by ivantcholakov. Edit Reason: a typo )

I can give you some hints only:

You need pagination, I prefer a JavaScript-based one: https://datatables.net You would need its Server Side Processing feature (SSP) https://datatables.net/examples/server_side/simple.html

Second: You have a system that sells things, i.e. it is a transactional oriented system, it serves business processes. The database structure is designed with this in mind, probably there are many tables with star-shaped relations. And using these tables directly for reporting most probably is not suitable.

So, you need to create views that aggregate data from different tables and producing wide "sheets" that contain what the end user is to see. If there are columns that are filled by nomenclatures, replace the value-codes with their correspondent meaningful texts.

Ok, you have the proper view for reporting, and the pagination feature asks the simple question, how many the rows are? SELECT COUNT(*) FROM my_report_view; Probably this operation for your 26 million record view that aggregates data from several tables would be extremely slow. And if there are calculated values - slower, slower. The server would be forced to traverse all the records.

How to solve this: In PostgreSQL there is a feature called MATERIALIZED VIEWS: https://www.postgresql.org/docs/9.6/stat...views.html Basically, your view creates a real table that represents it. Thus browsing data becomes faster - hundred times or more. But the real table should be refreshed automatically (a cronjob at night) and/or manually. You need to research whether your MS SQL Server provides a similar feature. Or indexed views, I don't know. Terminology may differ.

There is another option, if the things are quite heavy: Search for specialized solutions for reporting. You might search for and invite a firm that will make a separate database, designed for reporting and scripts that on regular basis will transfer data from the transactional database to the reporting database. And they are to provide a web-application for browsing the reports, printing charts, etc.

-----------------------

From distance your task smells to be a challenging one, think for a while whether you want to take it.
Reply
#3

@danangeloalcanar

ivantcholakov has given you some good advice. I also want to point out that you might want to make sure that your tables are indexed properly and that your queries are efficient. This makes sure that your database request are quick.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB