Speed up report (report services) sql

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 ?
LVL 2
team2005Asked:
Who is Participating?
 
RimvisConnect With a Mentor Commented:
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
 
RimvisCommented:
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
 
team2005Author Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
RimvisCommented:
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
 
team2005Author Commented:
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
 
RimvisCommented:
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
 
team2005Author Commented:
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
 
RimvisCommented:
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
 
team2005Author Commented:
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
 
team2005Author Commented:
Hi!

So its no way to get this report run  faster ?

Do you have good example of SSIS ?
0
 
RimvisCommented:
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
 
team2005Author Commented:
Hi!

I Think is to export data to excel
0
 
team2005Author Commented:
Hi!

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

Thanks for help
0
 
RimvisCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.