[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Speed up report (report services) sql

Posted on 2012-09-03
14
Medium Priority
?
364 Views
Last Modified: 2012-09-04
Hi!

Have a report made in report services.

The dataset on this report is using a view.

The report is very very slow...

Have 9 parameters on this report

3 of the parameters is a int field, and the rest is
varchar fields...

6 fields that are varchar fields, are filling up a listbox
with querys like:

Select Description2 from
(Select '<All cost centers>' As Description2, 1 as orderno
 Union
 Select distinct Description2, 2  from SALG_STATISTIKKNEW2
) x
ORDER BY orderno, Description2 

Open in new window


So it take a long time to report to come up (1 time)

And are very slow, when ALL records (2 million) are selected
The report takes many minutes to execute...

So now i have converted the view to a TABLE, and have
created INDEX on every field that are used as parameter in the report.

See that the report execute faster now, but just improved by 100%

So the table is 2 times faster then using the view.

My question is:

Is it faster to drop the index on the varchar fields ?
0
Comment
Question by:team2005
  • 7
  • 7
14 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 38361192
Hi team2005 \,
First of all, you need to find out, which part is taking most of the time. Is it filling the parameter selection listboxes? Or report generation itself? Try to generate empty report. For example, add "WHERE 1=0" to your report query. How long does it take to generate parameter selection?
0
 
LVL 2

Author Comment

by:team2005
ID: 38361509
Hi!

Filling parameters ... is OK

But when execute the query.. It take about 2 times faster, then using view

If i use 9 parameters, and all of them ar indexed..
Is this the best thing to do, or do i need a primary key to ?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 38361551
It is a good idea to have indexes on search criteria fields. If I understood correctly, you materialized view into table. I don't see how primary key would help here. One thing that bothers me is that you are trying to show all records on first load. I think you should let user to set some parameters first.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:team2005
ID: 38361589
Hi"

User want to selct example ALL customers in a listbox
So i using Distinct on the table on the customername...

The report use many second on a execution...

With a report using view, it takes aboyt 55 seconds
And with report using indexes and a table it use 23 seconds

I think that is slow ?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 38362398
Are you selecting customers from the fact table? The same table that contains report data? Is there a table that contains customer details? In this case you should fill customers list from there.
0
 
LVL 2

Author Comment

by:team2005
ID: 38362550
Hi!

Yes i am filling the listbox with data from the fact data.

But is this slowing things down ? After i select a customer, and click the
report button ?

Can it be that the rendring of the report is slow ?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 38362603
It is not slowing the report itself, it just takes additional time for report page to load. Is there a separate table for customers?
Do you know how long does it take to generate report itself, parameter loading excluded? How many records does it return?
0
 
LVL 2

Author Comment

by:team2005
ID: 38362625
Hi!

I have a seperate table for customer. But i am selecting the customer from the fact table.

It takes about 25 seconds to generate report.
And it return about 33000 records

If i run query in sql, it takes 2 seconds
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 38362655
So, basically, it's displaying the records that is slowing things down. Is there a reason that you want to display 33K records in the report? I don't think it is very useful. if you want to export data, you should look into different solutions, SSIS for example.
0
 
LVL 2

Author Comment

by:team2005
ID: 38363010
Hi!

So its no way to get this report run  faster ?

Do you have good example of SSIS ?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 38363102
Well, SSIS is a wast topic. There are a lot of articles and books on this. If you are interested, just search for them and pick up anything you like:
https://www.google.com/search?q=getting+started+with+SSIS

But just to make sure you are going right direction. What was the purpose of this report? You should disscuss it with you client. Was it to export the data? I doubt there is something to LOOK AT between these 33K records.
0
 
LVL 2

Author Comment

by:team2005
ID: 38363832
Hi!

I Think is to export data to excel
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 38363877
Hi!

I wil talk to customer, and find out what they want.

Thanks for help
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 38363891
In that case this might help:

SSIS: Export data from Sql Server 2005 to Excel
http://cavemansblog.wordpress.com/2009/04/17/ssis-export-data-from-sql-server-2005-to-excel/
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question