3G ram for SQL server: single

I have a table report based on a single 50,000 row table. The report
doesn't do anything unusual, but there are 5 grouping levels. The
ExecutionLog table shows the report took < 1 second to pull the data,
but 22 seconds to process the report.
Given that my high end server can perform millions of calcs per
second, does anyone know why my report would take so long to process?
The error logs show nothing unusual.
Thanks,
BurtWhat format are you using? From the execution log, where is most of the time
spent, processing, rendering?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> I have a table report based on a single 50,000 row table. The report
> doesn't do anything unusual, but there are 5 grouping levels. The
> ExecutionLog table shows the report took < 1 second to pull the data,
> but 22 seconds to process the report.
> Given that my high end server can perform millions of calcs per
> second, does anyone know why my report would take so long to process?
> The error logs show nothing unusual.
> Thanks,
> Burt|||Hi Tudor,
The format is HTML. Both rendering and the data pull from SQL Server
take less than a second. The report processing takes 22 seconds.
If I use an AS OLAP cube as the data source, the data pull takes 10
seconds, but the report still takes ~22 seconds to process.
Any thoughts?
Thanks,
Burt
"Tudor Trufinescu $MSFT$" <tudortr@.ms.com> wrote in message news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> What format are you using? From the execution log, where is most of the time
> spent, processing, rendering?
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > I have a table report based on a single 50,000 row table. The report
> > doesn't do anything unusual, but there are 5 grouping levels. The
> > ExecutionLog table shows the report took < 1 second to pull the data,
> > but 22 seconds to process the report.
> >
> > Given that my high end server can perform millions of calcs per
> > second, does anyone know why my report would take so long to process?
> > The error logs show nothing unusual.
> >
> > Thanks,
> >
> > Burt|||Just to make sure, are you using filters in your report? Filters bring over
all the data (in your case 50,000 rows) prior to doing anything with the
data. Then it works on the data (doing the filtering, grouping etc).
Bruce L-C
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> Hi Tudor,
> The format is HTML. Both rendering and the data pull from SQL Server
> take less than a second. The report processing takes 22 seconds.
> If I use an AS OLAP cube as the data source, the data pull takes 10
> seconds, but the report still takes ~22 seconds to process.
> Any thoughts?
> Thanks,
> Burt
>
> "Tudor Trufinescu $MSFT$" <tudortr@.ms.com> wrote in message
news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > What format are you using? From the execution log, where is most of the
time
> > spent, processing, rendering?
> >
> > --
> > Tudor Trufinescu
> > Dev Lead
> > Sql Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Burt" <burt_5920@.yahoo.com> wrote in message
> > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > I have a table report based on a single 50,000 row table. The report
> > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > ExecutionLog table shows the report took < 1 second to pull the data,
> > > but 22 seconds to process the report.
> > >
> > > Given that my high end server can perform millions of calcs per
> > > second, does anyone know why my report would take so long to process?
> > > The error logs show nothing unusual.
> > >
> > > Thanks,
> > >
> > > Burt|||Thanks, Bruce, but no, no filters. Some additional info:
-There are actually only 18K rows in the table, not 50K, and 6 groups,
not 5.
-A couple of guys on the RS dev team from MS were kind enough to take
a look at the rdl, but didn't spot anything unusual.
-I created a similar 6 group report in MS Access, which took only a
couple of seconds to create.
-I have a 600K zip file with the RDL, table DDL, and actual table data
if anyone wants to play with it.
Thanks,
Burt
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> Just to make sure, are you using filters in your report? Filters bring over
> all the data (in your case 50,000 rows) prior to doing anything with the
> data. Then it works on the data (doing the filtering, grouping etc).
> Bruce L-C
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > Hi Tudor,
> >
> > The format is HTML. Both rendering and the data pull from SQL Server
> > take less than a second. The report processing takes 22 seconds.
> >
> > If I use an AS OLAP cube as the data source, the data pull takes 10
> > seconds, but the report still takes ~22 seconds to process.
> >
> > Any thoughts?
> >
> > Thanks,
> >
> > Burt
> >
> >
> > "Tudor Trufinescu $MSFT$" <tudortr@.ms.com> wrote in message
> news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > What format are you using? From the execution log, where is most of the
> time
> > > spent, processing, rendering?
> > >
> > > --
> > > Tudor Trufinescu
> > > Dev Lead
> > > Sql Server Reporting Services
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > I have a table report based on a single 50,000 row table. The report
> > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > ExecutionLog table shows the report took < 1 second to pull the data,
> > > > but 22 seconds to process the report.
> > > >
> > > > Given that my high end server can perform millions of calcs per
> > > > second, does anyone know why my report would take so long to process?
> > > > The error logs show nothing unusual.
> > > >
> > > > Thanks,
> > > >
> > > > Burt|||I don't think this would make a difference but how hard would it be to start
anew with the report. I have had issues where I have added a group, removed
it etc and everything did not remove from the rdl. Just grasping at straws
here. I would think MS would definitely be interested in what you have here.
Bruce L-C
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408250907.43140876@.posting.google.com...
> Thanks, Bruce, but no, no filters. Some additional info:
> -There are actually only 18K rows in the table, not 50K, and 6 groups,
> not 5.
> -A couple of guys on the RS dev team from MS were kind enough to take
> a look at the rdl, but didn't spot anything unusual.
> -I created a similar 6 group report in MS Access, which took only a
> couple of seconds to create.
> -I have a 600K zip file with the RDL, table DDL, and actual table data
> if anyone wants to play with it.
> Thanks,
> Burt
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> > Just to make sure, are you using filters in your report? Filters bring
over
> > all the data (in your case 50,000 rows) prior to doing anything with the
> > data. Then it works on the data (doing the filtering, grouping etc).
> >
> > Bruce L-C
> >
> > "Burt" <burt_5920@.yahoo.com> wrote in message
> > news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > > Hi Tudor,
> > >
> > > The format is HTML. Both rendering and the data pull from SQL Server
> > > take less than a second. The report processing takes 22 seconds.
> > >
> > > If I use an AS OLAP cube as the data source, the data pull takes 10
> > > seconds, but the report still takes ~22 seconds to process.
> > >
> > > Any thoughts?
> > >
> > > Thanks,
> > >
> > > Burt
> > >
> > >
> > > "Tudor Trufinescu $MSFT$" <tudortr@.ms.com> wrote in message
> > news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > > What format are you using? From the execution log, where is most of
the
> > time
> > > > spent, processing, rendering?
> > > >
> > > > --
> > > > Tudor Trufinescu
> > > > Dev Lead
> > > > Sql Server Reporting Services
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > > I have a table report based on a single 50,000 row table. The
report
> > > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > > ExecutionLog table shows the report took < 1 second to pull the
data,
> > > > > but 22 seconds to process the report.
> > > > >
> > > > > Given that my high end server can perform millions of calcs per
> > > > > second, does anyone know why my report would take so long to
process?
> > > > > The error logs show nothing unusual.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Burt|||Thanks, Bruce. I've actually created a few versions of the report with
similar results.
Per MS, RS is taking a (session) snapshot of the report and saving it
as they process the report, this is the most time consuming part of
this particular report.
Something tells me as their engine matures these reports will get
faster...
Burt
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<OjEnXBtiEHA.3972@.tk2msftngp13.phx.gbl>...
> I don't think this would make a difference but how hard would it be to start
> anew with the report. I have had issues where I have added a group, removed
> it etc and everything did not remove from the rdl. Just grasping at straws
> here. I would think MS would definitely be interested in what you have here.
> Bruce L-C
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408250907.43140876@.posting.google.com...
> > Thanks, Bruce, but no, no filters. Some additional info:
> >
> > -There are actually only 18K rows in the table, not 50K, and 6 groups,
> > not 5.
> >
> > -A couple of guys on the RS dev team from MS were kind enough to take
> > a look at the rdl, but didn't spot anything unusual.
> >
> > -I created a similar 6 group report in MS Access, which took only a
> > couple of seconds to create.
> >
> > -I have a 600K zip file with the RDL, table DDL, and actual table data
> > if anyone wants to play with it.
> >
> > Thanks,
> >
> > Burt
> >
> >
> >
> >
> > "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> > > Just to make sure, are you using filters in your report? Filters bring
> over
> > > all the data (in your case 50,000 rows) prior to doing anything with the
> > > data. Then it works on the data (doing the filtering, grouping etc).
> > >
> > > Bruce L-C
> > >
> > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > > > Hi Tudor,
> > > >
> > > > The format is HTML. Both rendering and the data pull from SQL Server
> > > > take less than a second. The report processing takes 22 seconds.
> > > >
> > > > If I use an AS OLAP cube as the data source, the data pull takes 10
> > > > seconds, but the report still takes ~22 seconds to process.
> > > >
> > > > Any thoughts?
> > > >
> > > > Thanks,
> > > >
> > > > Burt
> > > >
> > > >
> > > > "Tudor Trufinescu $MSFT$" <tudortr@.ms.com> wrote in message
> news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > > > What format are you using? From the execution log, where is most of
> the
> time
> > > > > spent, processing, rendering?
> > > > >
> > > > > --
> > > > > Tudor Trufinescu
> > > > > Dev Lead
> > > > > Sql Server Reporting Services
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > > >
> > > > >
> > > > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > > > I have a table report based on a single 50,000 row table. The
> report
> > > > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > > > ExecutionLog table shows the report took < 1 second to pull the
> data,
> > > > > > but 22 seconds to process the report.
> > > > > >
> > > > > > Given that my high end server can perform millions of calcs per
> > > > > > second, does anyone know why my report would take so long to
> process?
> > > > > > The error logs show nothing unusual.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Burt

I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table.

My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory.

Any ideas/suggestions would be appreciated.

You only need to pull in the 4 columns from the table that you will be looking up against. Assuming they are normal integers (i.e. 4 bytes) you would need 4 * 4 * 1000000 =16MB per lookup. That's not really all that much. My advice would be to give it a go and if you're havig problems - see where the bottlenecks are.

More on memory per package: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx

-Jamie

Thursday, March 29, 2012

A few BLOBs per page

Tuesday, March 27, 2012

A few BLOBs per page

A few BLOBs per page

Saturday, February 11, 2012

4 processors, 8 Gigs RAM, 22 Seconds to process report?

4 lookups against single large table

Thursday, February 9, 2012

3rd party software

3G ram for SQL server

Blog Archive

About Me