Generation of report takes a long time (performance issue)

Hi,

My company is using MS SQL Server 2000 Developer Edition and Report Services.
We have some tables which are have now around 4000000 rows. When we genarating report
SELECT sessionNum, moteID, dbo.MacAddrConv(macAddr) AS macAddr, chID, dbo.TimestampConv(timestamp) AS timestamp, value
FROM NotificationData
ORDER BY timestamp
it takes 15 minute. Our table is located in the Server whic has these parametres:

1- Currently there are about 4.0 million entry in the database that uses 1638.44 MB of memory

2- The new SQL server has 3.39 GHz Intel Xeon processor, 3.00 GB of RAM and 192 GB of hard drive.

Why it takes so long time to generate the reports? Could you help us to improve the performance for our Database?

Thanks a lot,

Alice

The query selects 4 million rows. If you just show all the rows in the report with about 40 rows per page, you would really want to generate a report with 100.000 pages?

If however, you just show aggregated values in the report (but not the detail rows), you should rather perform the aggregations inside the query - because the aggregations will be performed much more efficiently inside the database.

-- Robert

|||i'm not impressed with reporting services' scalability

i have reports that are much smaller than yours that take forever to run

figure out a way to make it smaller, it isn't going to handle it nicely|||1st question, does ur report must show so many record at 1 time?i believe that even you can show up but u still having the printing problem.
2nd question, how you layout ur report?

my suggestion would be try to put more parameter or fixed the output record no for each page, and try to do any math calculation in query instead of report. hope can help|||

Generating ridiculously huge reports is a problem with every reporting system I've seen. However, I think there are a couple of options here.

First, use the new query execution plan tools to make sure the query isnt too complicated.|||Changing the report source to a SQL Server stored procedure also can have dramatic performance boosts. This is mainly due to procs being pre-compiled.sql

Generation of report takes a long time (performance issue)

Hi,

My company is using MS SQL Server 2000 Developer Edition and Report Services.
We have some tables which are have now around 4000000 rows. When we genarating report
SELECT sessionNum, moteID, dbo.MacAddrConv(macAddr) AS macAddr, chID, dbo.TimestampConv(timestamp) AS timestamp, value
FROM NotificationData
ORDER BY timestamp
it takes 15 minute. Our table is located in the Server whic has these parametres:

1- Currently there are about 4.0 million entry in the database that uses 1638.44 MB of memory

2- The new SQL server has 3.39 GHz Intel Xeon processor, 3.00 GB of RAM and 192 GB of hard drive.

Why it takes so long time to generate the reports? Could you help us to improve the performance for our Database?

Thanks a lot,

Alice

The query selects 4 million rows. If you just show all the rows in the report with about 40 rows per page, you would really want to generate a report with 100.000 pages?

If however, you just show aggregated values in the report (but not the detail rows), you should rather perform the aggregations inside the query - because the aggregations will be performed much more efficiently inside the database.

-- Robert

|||i'm not impressed with reporting services' scalability

i have reports that are much smaller than yours that take forever to run

figure out a way to make it smaller, it isn't going to handle it nicely|||1st question, does ur report must show so many record at 1 time?i believe that even you can show up but u still having the printing problem.
2nd question, how you layout ur report?

my suggestion would be try to put more parameter or fixed the output record no for each page, and try to do any math calculation in query instead of report. hope can help|||

Generating ridiculously huge reports is a problem with every reporting system I've seen. However, I think there are a couple of options here.

First, use the new query execution plan tools to make sure the query isnt too complicated.|||Changing the report source to a SQL Server stored procedure also can have dramatic performance boosts. This is mainly due to procs being pre-compiled.

Generation of report takes a long time (performance issue)

Hi,

My company is using MS SQL Server 2000 Developer Edition and Report Services.
We have some tables which are have now around 4000000 rows. When we genarating report
SELECT sessionNum, moteID, dbo.MacAddrConv(macAddr) AS macAddr, chID, dbo.TimestampConv(timestamp) AS timestamp, value
FROM NotificationData
ORDER BY timestamp
it takes 15 minute. Our table is located in the Server whic has these parametres:

1- Currently there are about 4.0 million entry in the database that uses 1638.44 MB of memory

2- The new SQL server has 3.39 GHz Intel Xeon processor, 3.00 GB of RAM and 192 GB of hard drive.

Why it takes so long time to generate the reports? Could you help us to improve the performance for our Database?

Thanks a lot,

Alice

The query selects 4 million rows. If you just show all the rows in the report with about 40 rows per page, you would really want to generate a report with 100.000 pages?

If however, you just show aggregated values in the report (but not the detail rows), you should rather perform the aggregations inside the query - because the aggregations will be performed much more efficiently inside the database.

-- Robert

|||i'm not impressed with reporting services' scalability

i have reports that are much smaller than yours that take forever to run

figure out a way to make it smaller, it isn't going to handle it nicely|||1st question, does ur report must show so many record at 1 time?i believe that even you can show up but u still having the printing problem.
2nd question, how you layout ur report?

my suggestion would be try to put more parameter or fixed the output record no for each page, and try to do any math calculation in query instead of report. hope can help|||

Generating ridiculously huge reports is a problem with every reporting system I've seen. However, I think there are a couple of options here.

First, use the new query execution plan tools to make sure the query isnt too complicated.|||Changing the report source to a SQL Server stored procedure also can have dramatic performance boosts. This is mainly due to procs being pre-compiled.

Generate SQL scripts with data

Hi,
I am trying to move tables from my local SQL server to the sever on my
hosting company. I tried All Taks->Generate SQL Script. That only gave me
the script to create tables, is there a way I can script the data also? Of
course I can type in Insert statements myself, but that's tedious.
TIAsee
http://www.nigelrivett.net/SQLTsql/...LoadScript.html
http://www.nigelrivett.net/SQLTsql/...Script_Rem.html
also consider using bcp
http://www.nigelrivett.net/SQLTsql/BCPAllTables.html
"Danny Ni" wrote:

> Hi,
> I am trying to move tables from my local SQL server to the sever on my
> hosting company. I tried All Taks->Generate SQL Script. That only gave me
> the script to create tables, is there a way I can script the data also? O
f
> course I can type in Insert statements myself, but that's tedious.
>
> TIA
>
>|||in QA you can create insert scripts for all your table objects, use the
right mouse button.|||Try www.sqlscripter.com to generate data scripts. All types are supported
(Insert, Update, Delete).
It's free.
Thomas
"Danny Ni" wrote:

> Hi,
> I am trying to move tables from my local SQL server to the sever on my
> hosting company. I tried All Taks->Generate SQL Script. That only gave me
> the script to create tables, is there a way I can script the data also? O
f
> course I can type in Insert statements myself, but that's tedious.
>
> TIA
>
>