March 12, 2009 at 5:40 am
What other ways that i can use to generate the reports for 5-10 million records.
March 12, 2009 at 5:47 am
Hi Babar,
1. Using temp table is always faster than cursors.
2. U have used dynamic SQL. it slows down the performance.
3. In report, try to use some group by so that u can see the group data only on demand.
4. If possible try to use on demand subreports, which will not fetch 5 million rows at a time.
Better try to redesign the report to achieve efficiency.
March 12, 2009 at 6:05 am
The structure and the query are the driving factors. You've got an open-ended query with no well defined WHERE clause, so it's going to be hard to index it properly, but what you need to do is capture the execution plans for the most frequently used WHERE clauses and ensure that you've got indexes in place to support those, if possible. Again, with the volume of data you're dealing with, even with good indexes, you're likely to get table scans.
Can you post actual execution plans? That will help to drill down on specifics rather than speaking in generalities.
Also, if you use sp_executesql instead of exec you're more likely to get plan reuse, which could help some.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2009 at 12:46 pm
I agree with the suggestions made.
For instance, I had this ridiculous instance where I was using Crystal Report version 8 to generate the TOP 10 most popular items from an Order table which had 50,000 orders and about 500,000 items inn the Order_Item table. Crystal report downloaded the entire contents of both tables and then decided which were the 10 records that deserved to be printed.
This kind of "dump everything to the client" resulted in the user machine stuck for minutes.
Removing the SQL statement from CR and replacing it with a stored procedure cut the time down to seconds.
Yes I know that there are later versions of Crystal Report that allow you pass parameters to select the data but this illustrates the major weakness of having the client crunch a lot of data.
Also think of the load over the network. Download 500,000 records just to print 10 ?
In other cases, I have also found stored procedures to be considerably more efficient than client-side calculations. For instance, generating two sets 100,000 Universal Product code (the bar code) with calculation of the check digit on the client side took 45 minutes to complete. It dropped to 38 seconds having this done in a stored procedure.
Regards
March 12, 2009 at 1:04 pm
Hi Babar
What everybody wants to say is there seems to be no reason to transport 5 million records ever to a client application/report.
I don't think that this 5 million records will be shown to anybody in raw format. I think currently your reporting client uses this data as base for aggregations, calculations and analytics and creates a result data set which will be shown/saved in your report.
You should determine to move the aggregations, calculations and analytics into the database server (or a SSAS but this maybe later). You can to any of this tasks directly within the database and generate the well formed data which can directly be used within your report.
Greets
Flo
March 12, 2009 at 1:20 pm
Babar Javaid (3/12/2009)
What other ways that i can use to generate the reports for 5-10 million records.
The report may have to sort through 5 million records but just to find the ones of interst or to create summaries (sums, counts, etc. such as
the number of sales in the last 3 months, the toal amount of the sales, etc).
The select top 10 from half a million records is just such an example. If you let the server handle your request instead of just being a dumb provider to the client, only the data actually needed for the report will fly across the network.
We are not saying you cannot get a report based on information dispersed ober 5 million records, we are saying the SQL Server can organize the data considerably faster than a client without choking the network.
Regards
March 17, 2009 at 2:26 am
All the calculations are made on the server side. I fetch the records from SQL Server Db to RAM and then manipulate the records on server side and at the send the aggregate/summarized report to the client.
In this client will only see the summarized reports.
I change the query where clause to specific parameters and remove the with clause, even then the query is not running in a fraction time.
If i use the sql reporting services and send the actual results from SQL server to web server then forward the results to client. Will this improve the performance issues.
March 17, 2009 at 8:17 am
The "server" which calculates the report data should be the SQL Server 😉
Let me try to explain what we are talking/writing about with an example:
Let's assume:
* Your database contains consignees, orders and order-details which refer to your product information.
* Your report shows for for each country the count of consignees, the price of their orders and the weight of the ordered products
... just a example.
Now you can select all the consignees and for these consignees the orders, order-details and the product information. You select 10,000 consignees, 200,000 orders and 2,000,000 order details. The product information may be 1,000 but they don't matter in this case.
You loop through all this data (maybe on another server) to aggregate your report information. This causes a huge network traffic, a hard cost of IO and CPU for the SQL Server and a huge server load for the calculating server.
The question of all these people is:
May we help you to aggregate the data directly within SQL Server, before they will be transported to any other system.
Greets
Flo
March 18, 2009 at 2:01 am
Does this mean that i need to write my business logic in the sql and return only result set to the web server that need to forward to the client.
March 18, 2009 at 3:03 am
Babar Javaid (3/18/2009)
Does this mean that i need to write my business logic in the sql and return only result set to the web server that need to forward to the client.
You got it!! 😀
If you need help for this, feel free to post here ;-). Nobody here can help to make 5 million records fast over network but if you need assistance for SQL tuning this is the right place.
Greets
Flo
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply