|
|
|
date: Wed, 20 Aug 2008 15:03:22 GMT,
group: microsoft.public.sqlserver.reportingsvcs
back
Difference Between Filters and Parameters
Hello,
Please consider my rather rudimentary question. I am relatively new to
reporting services and sql server so your patience is much appreciated. Ok,
am trying to determine what is the most efficient way of creating my datasets.
My question is what exactly is reporting services doing with the data that
resides on the sql server? For example say I have a table with 5,000,000 rows,
though there are only 100 distinct values in a certain field, say I create a
dataset in reporting services that runs a group by on the field with only 100
distinct values. Does reporting services pull down the entire 5,000,000
records "locally" then filter out the 100 records? Or does the "crunching"
occur on the server level, then it strictly sends the 100 records across the
network to the machine running reporting services? When using stored
procedures are there any key principles to keep in mind to ensure efficiency?
Essentially I am trying to determine what is the most efficient and elegant
means of designing my datasets. To ensure the quickest run times when reports
are run, I certainly want to be sure that the bridge between my data and my
reports is properly set-up.
Thank you for your time!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200808/1
date: Wed, 20 Aug 2008 15:03:22 GMT
author: NeonSky via SQLMonster.com u23580@uwe
Re: Difference Between Filters and Parameters
You are on the right track. Have the data be as little as possible by the
time RS deals with it. This means that unless absolutely necessary stay away
from filters. Filters occur after the data is downloaded. Query parameters
are executed at the server and only that data is returned. So any grouping,
summing, etc. do with your query. You might still need to group in RS but
first do the real work in SQL Server.
For instance,
select somefield, sum(anotherfield) as theSum, max(anotherfield) as theMax
from sometable
where somefield in (@MyParam)
group by somefield
Stored procedures are great just because the server has already figured out
the execution plan and things run faster. I tend to use a mix. If the query
is easy then I just put it in the dataset. If I use temp tables it goes in a
stored procedure.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NeonSky via SQLMonster.com" <u23580@uwe> wrote in message
news:88f501672a49f@uwe...
> Hello,
>
> Please consider my rather rudimentary question. I am relatively new to
> reporting services and sql server so your patience is much appreciated.
> Ok,
> am trying to determine what is the most efficient way of creating my
> datasets.
> My question is what exactly is reporting services doing with the data that
> resides on the sql server? For example say I have a table with 5,000,000
> rows,
> though there are only 100 distinct values in a certain field, say I create
> a
> dataset in reporting services that runs a group by on the field with only
> 100
> distinct values. Does reporting services pull down the entire 5,000,000
> records "locally" then filter out the 100 records? Or does the "crunching"
> occur on the server level, then it strictly sends the 100 records across
> the
> network to the machine running reporting services? When using stored
> procedures are there any key principles to keep in mind to ensure
> efficiency?
>
> Essentially I am trying to determine what is the most efficient and
> elegant
> means of designing my datasets. To ensure the quickest run times when
> reports
> are run, I certainly want to be sure that the bridge between my data and
> my
> reports is properly set-up.
>
> Thank you for your time!
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200808/1
>
date: Wed, 20 Aug 2008 10:59:02 -0500
author: Bruce L-C [MVP]
Re: Difference Between Filters and Parameters
Parameters limit the result set leaving the SQL server, so the size of the
package going to the Dev box, ReportViewer control, or Reporting Services
server is smaller. Filters are applied by the rendinering engine, so the
locale may be on the client in the case of report development, or
ReportViewer control. Filters are great when you want a single dataset
presented different ways, for example, in a chart and a table, and some data
is removed for one reason or another.
Thanks,
Steve MunLeeuw
date: Wed, 20 Aug 2008 10:14:57 -0600
author: Steve MunLeeuw
Re: Difference Between Filters and Parameters
Thank you Steve, very helpful!
Steve MunLeeuw wrote:
>Parameters limit the result set leaving the SQL server, so the size of the
>package going to the Dev box, ReportViewer control, or Reporting Services
>server is smaller. Filters are applied by the rendinering engine, so the
>locale may be on the client in the case of report development, or
>ReportViewer control. Filters are great when you want a single dataset
>presented different ways, for example, in a chart and a table, and some data
>is removed for one reason or another.
>
>Thanks,
>Steve MunLeeuw
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200808/1
date: Fri, 22 Aug 2008 16:36:34 GMT
author: NeonSky via SQLMonster.com u23580@uwe
|
|
