Reports running much slower on SQL Express Edition of Reporting Services...

We have a SQL Server 2005 application that we deploy most of the time on SQL
Express, not full blown SQL. We have a Development box here (2 CPU XEON
with 4GB RAM) that has the Developer Edition and an instance of SQL Express
installed. If I run the same reports, with the same data, but one on the
developer edition and one on the Express Edition, there is a SIGNIFICANT
performance difference. This is a 1-page report - nothing huge, and it
renders in 2-3 seconds on Developer edition, but consistantly takes 15-20
seconds on the Express Edition.
I know SQL Express has a 1GB RAM limit, and a 1-CPU limit, but are there any
settings or anything that I can do to bring this closer to the full blown
SQL Version. This performance killing me onsite...
Any thoughts?
Thanks,
Markhave you identify if the problem is related to RS or SQL?
Does the SQL query is slower on the express edition? or the query takes the
same amount of time?
if the difference is here, then you have to play in the query and the
indexes to optimize it for the Express edition.
"Mark Essex" <messex@.netalytics.com> wrote in message
news:eyZTzG35HHA.980@.TK2MSFTNGP06.phx.gbl...
> We have a SQL Server 2005 application that we deploy most of the time on
> SQL Express, not full blown SQL. We have a Development box here (2 CPU
> XEON with 4GB RAM) that has the Developer Edition and an instance of SQL
> Express installed. If I run the same reports, with the same data, but one
> on the developer edition and one on the Express Edition, there is a
> SIGNIFICANT performance difference. This is a 1-page report - nothing
> huge, and it renders in 2-3 seconds on Developer edition, but consistantly
> takes 15-20 seconds on the Express Edition.
> I know SQL Express has a 1GB RAM limit, and a 1-CPU limit, but are there
> any settings or anything that I can do to bring this closer to the full
> blown SQL Version. This performance killing me onsite...
> Any thoughts?
> Thanks,
> Mark
>|||The query is 'instant' (less than a second) on both SQL Instances, so I
would say that this is an RS issue, not a SQL issue.
"Jeje" <willgart@.hotmail.com> wrote in message
news:2645D59B-223F-4F18-9712-AFF641F24C77@.microsoft.com...
> have you identify if the problem is related to RS or SQL?
> Does the SQL query is slower on the express edition? or the query takes
> the same amount of time?
> if the difference is here, then you have to play in the query and the
> indexes to optimize it for the Express edition.
>
> "Mark Essex" <messex@.netalytics.com> wrote in message
> news:eyZTzG35HHA.980@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL Server 2005 application that we deploy most of the time on
>> SQL Express, not full blown SQL. We have a Development box here (2 CPU
>> XEON with 4GB RAM) that has the Developer Edition and an instance of SQL
>> Express installed. If I run the same reports, with the same data, but
>> one on the developer edition and one on the Express Edition, there is a
>> SIGNIFICANT performance difference. This is a 1-page report - nothing
>> huge, and it renders in 2-3 seconds on Developer edition, but
>> consistantly takes 15-20 seconds on the Express Edition.
>> I know SQL Express has a 1GB RAM limit, and a 1-CPU limit, but are there
>> any settings or anything that I can do to bring this closer to the full
>> blown SQL Version. This performance killing me onsite...
>> Any thoughts?
>> Thanks,
>> Mark
>>|||what is the connection string used by the report?
and does RS and SQL are on the same server?
"Mark Essex" <messex@.netalytics.com> wrote in message
news:eoyjDAJ6HHA.4880@.TK2MSFTNGP03.phx.gbl...
> The query is 'instant' (less than a second) on both SQL Instances, so I
> would say that this is an RS issue, not a SQL issue.
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:2645D59B-223F-4F18-9712-AFF641F24C77@.microsoft.com...
>> have you identify if the problem is related to RS or SQL?
>> Does the SQL query is slower on the express edition? or the query takes
>> the same amount of time?
>> if the difference is here, then you have to play in the query and the
>> indexes to optimize it for the Express edition.
>>
>> "Mark Essex" <messex@.netalytics.com> wrote in message
>> news:eyZTzG35HHA.980@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL Server 2005 application that we deploy most of the time on
>> SQL Express, not full blown SQL. We have a Development box here (2 CPU
>> XEON with 4GB RAM) that has the Developer Edition and an instance of SQL
>> Express installed. If I run the same reports, with the same data, but
>> one on the developer edition and one on the Express Edition, there is a
>> SIGNIFICANT performance difference. This is a 1-page report - nothing
>> huge, and it renders in 2-3 seconds on Developer edition, but
>> consistantly takes 15-20 seconds on the Express Edition.
>> I know SQL Express has a 1GB RAM limit, and a 1-CPU limit, but are there
>> any settings or anything that I can do to bring this closer to the full
>> blown SQL Version. This performance killing me onsite...
>> Any thoughts?
>> Thanks,
>> Mark
>>
>|||COnnection string looks like:
data source=.\installs; initial catalog = Methasoft53
RS and SQL are on the same machine.
Would the '.\installs' cause issues, meaning should I put the MACHINE NAME
in there, instead of using the 'dot' notation? Maybe a 'lookup' time issue?
Thanks,
Mark
"Jeje" <willgart@.hotmail.com> wrote in message
news:6C308FB1-6A82-440F-9746-8BED264C9313@.microsoft.com...
> what is the connection string used by the report?
> and does RS and SQL are on the same server?
> "Mark Essex" <messex@.netalytics.com> wrote in message
> news:eoyjDAJ6HHA.4880@.TK2MSFTNGP03.phx.gbl...
>> The query is 'instant' (less than a second) on both SQL Instances, so I
>> would say that this is an RS issue, not a SQL issue.
>> "Jeje" <willgart@.hotmail.com> wrote in message
>> news:2645D59B-223F-4F18-9712-AFF641F24C77@.microsoft.com...
>> have you identify if the problem is related to RS or SQL?
>> Does the SQL query is slower on the express edition? or the query takes
>> the same amount of time?
>> if the difference is here, then you have to play in the query and the
>> indexes to optimize it for the Express edition.
>>
>> "Mark Essex" <messex@.netalytics.com> wrote in message
>> news:eyZTzG35HHA.980@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL Server 2005 application that we deploy most of the time
>> on SQL Express, not full blown SQL. We have a Development box here (2
>> CPU XEON with 4GB RAM) that has the Developer Edition and an instance
>> of SQL Express installed. If I run the same reports, with the same
>> data, but one on the developer edition and one on the Express Edition,
>> there is a SIGNIFICANT performance difference. This is a 1-page
>> report - nothing huge, and it renders in 2-3 seconds on Developer
>> edition, but consistantly takes 15-20 seconds on the Express Edition.
>> I know SQL Express has a 1GB RAM limit, and a 1-CPU limit, but are
>> there any settings or anything that I can do to bring this closer to
>> the full blown SQL Version. This performance killing me onsite...
>> Any thoughts?
>> Thanks,
>> Mark
>>
>>|||to see if there is a naming resolution issue, open the profiler on your
server, then run the report.
if you see the query 10 seconds after you click "view report", then there is
a name resolution issue. if you see the query in less then 1second, then
there is no naming issue and the problem is somewhere else.
but using the . (dot) should not cause name resolution issue.
but maybe there is an authentication issue.
try to use an SQL Server login instead of the windows authentication
data source=.\installs; initial catalog =Methasoft53;uid=<user>;pwd=<password>
"Mark Essex" <messex@.netalytics.com> wrote in message
news:Oqg98Rp6HHA.4584@.TK2MSFTNGP03.phx.gbl...
> COnnection string looks like:
> data source=.\installs; initial catalog = Methasoft53
> RS and SQL are on the same machine.
> Would the '.\installs' cause issues, meaning should I put the MACHINE NAME
> in there, instead of using the 'dot' notation? Maybe a 'lookup' time
> issue?
> Thanks,
> Mark
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:6C308FB1-6A82-440F-9746-8BED264C9313@.microsoft.com...
>> what is the connection string used by the report?
>> and does RS and SQL are on the same server?
>> "Mark Essex" <messex@.netalytics.com> wrote in message
>> news:eoyjDAJ6HHA.4880@.TK2MSFTNGP03.phx.gbl...
>> The query is 'instant' (less than a second) on both SQL Instances, so I
>> would say that this is an RS issue, not a SQL issue.
>> "Jeje" <willgart@.hotmail.com> wrote in message
>> news:2645D59B-223F-4F18-9712-AFF641F24C77@.microsoft.com...
>> have you identify if the problem is related to RS or SQL?
>> Does the SQL query is slower on the express edition? or the query takes
>> the same amount of time?
>> if the difference is here, then you have to play in the query and the
>> indexes to optimize it for the Express edition.
>>
>> "Mark Essex" <messex@.netalytics.com> wrote in message
>> news:eyZTzG35HHA.980@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL Server 2005 application that we deploy most of the time
>> on SQL Express, not full blown SQL. We have a Development box here (2
>> CPU XEON with 4GB RAM) that has the Developer Edition and an instance
>> of SQL Express installed. If I run the same reports, with the same
>> data, but one on the developer edition and one on the Express Edition,
>> there is a SIGNIFICANT performance difference. This is a 1-page
>> report - nothing huge, and it renders in 2-3 seconds on Developer
>> edition, but consistantly takes 15-20 seconds on the Express Edition.
>> I know SQL Express has a 1GB RAM limit, and a 1-CPU limit, but are
>> there any settings or anything that I can do to bring this closer to
>> the full blown SQL Version. This performance killing me onsite...
>> Any thoughts?
>> Thanks,
>> Mark
>>
>>
>