data export to CSV

I have a report that produces over 3 million rows, this report is
export to CSV and is scheduled to run weekly.
Sometimes the report is needed to run manually. Initially this report
timed out, now the report runs but takes a very long time to complete.
If the stored procedure is run directly it takes about 5 mins to
execute. Running in reporting services takes upward of an hour.
Does anyone have suggestions on how to speed up this report.
my initial thoughts would be to use SSRS to populate the parameters
and maybe hook into the reporting engine and execute the stored
procedure directly with the parameters defined
thanks in advance
IanHi, how run this report? http? web service? how?
--
----
Microsoft M.V.P en SQLServer
SQLTotal Consulting - Servicios en SQLServer
Email: maxi.da@.gmail.com.nospam
----
<emery.ian@.googlemail.com> escribió en el mensaje
news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>I have a report that produces over 3 million rows, this report is
> export to CSV and is scheduled to run weekly.
> Sometimes the report is needed to run manually. Initially this report
> timed out, now the report runs but takes a very long time to complete.
> If the stored procedure is run directly it takes about 5 mins to
> execute. Running in reporting services takes upward of an hour.
> Does anyone have suggestions on how to speed up this report.
> my initial thoughts would be to use SSRS to populate the parameters
> and maybe hook into the reporting engine and execute the stored
> procedure directly with the parameters defined
>
> thanks in advance
> Ian|||Do not waste your time trying to run the report different ways, it will not
affect the time.
Your time is coming in two places. One, does it really only take 5 minutes
to extract 5 million records? I'm not sure how it works when executing a
stored procedure from Query Analyzer that returns 5 million records, whether
it really retrieves all the records or not. Anyway, I think that is one
area, the number of records being physically returned takes time.
Second, RS 2000 and RS 2005 does all rendering in RAM. It does not page
anything out to disk or utilize the disk at all when rendering. I am pretty
sure this is changing substantially in RS 2008. Adding more RAM to the
server will help.
The other option is to use SSIS instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<emery.ian@.googlemail.com> wrote in message
news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>I have a report that produces over 3 million rows, this report is
> export to CSV and is scheduled to run weekly.
> Sometimes the report is needed to run manually. Initially this report
> timed out, now the report runs but takes a very long time to complete.
> If the stored procedure is run directly it takes about 5 mins to
> execute. Running in reporting services takes upward of an hour.
> Does anyone have suggestions on how to speed up this report.
> my initial thoughts would be to use SSRS to populate the parameters
> and maybe hook into the reporting engine and execute the stored
> procedure directly with the parameters defined
>
> thanks in advance
> Ian|||An additional point, BCP is the tool to use (not SSIS and not RS) if
performance is your goal. Give a DBA your query used by the report and they
could have BCP written and scripted for you in no time. It's been over a
decade since I used BCP and I had forgotten about it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eES$LTZPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Do not waste your time trying to run the report different ways, it will
> not affect the time.
> Your time is coming in two places. One, does it really only take 5 minutes
> to extract 5 million records? I'm not sure how it works when executing a
> stored procedure from Query Analyzer that returns 5 million records,
> whether it really retrieves all the records or not. Anyway, I think that
> is one area, the number of records being physically returned takes time.
> Second, RS 2000 and RS 2005 does all rendering in RAM. It does not page
> anything out to disk or utilize the disk at all when rendering. I am
> pretty sure this is changing substantially in RS 2008. Adding more RAM to
> the server will help.
> The other option is to use SSIS instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <emery.ian@.googlemail.com> wrote in message
> news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>>I have a report that produces over 3 million rows, this report is
>> export to CSV and is scheduled to run weekly.
>> Sometimes the report is needed to run manually. Initially this report
>> timed out, now the report runs but takes a very long time to complete.
>> If the stored procedure is run directly it takes about 5 mins to
>> execute. Running in reporting services takes upward of an hour.
>> Does anyone have suggestions on how to speed up this report.
>> my initial thoughts would be to use SSRS to populate the parameters
>> and maybe hook into the reporting engine and execute the stored
>> procedure directly with the parameters defined
>>
>> thanks in advance
>> Ian
>

Data checking?

Hey all,
prolly a simple solution, but why isn't the following string working in
my execute sql step within DTS? It produces results, just not the ones
I want... What am I doing wrong?

select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'

It's displaying all the records? It should only be displaying those
records that do *not* contain letters or numbers.
Thanks in advance!
-RoyOn 6 Jan 2005 06:13:08 -0800, roy.anderson@.gmail.com wrote:

> Hey all,
> prolly a simple solution, but why isn't the following string working in
> my execute sql step within DTS? It produces results, just not the ones
> I want... What am I doing wrong?
>
> select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'
> It's displaying all the records? It should only be displaying those
> records that do *not* contain letters or numbers.
> Thanks in advance!
> -Roy

Your clause is selecting rows where the x column contains at least one
character that is not a digit and also contain at least one character that
is not a letter. If you had a row where x was all letters, all digits, or
maybe all letters plus punctuation but no digits, etc., then it would not
be included.

The clause you want is probably

WHERE NOT (x LIKE '%[0-9a-z]%')

(parenthesis optional)|||Thanks much Ross, after some toying around, the end product that works
is:

WHERE (x LIKE '%[^0-9a-z]%')

I'm unsure why having the "NOT" specified beforehand produces no
results, but it doesn't. I'm assuming it's because sqlserver perceives
the NOT as referring to the wildcards too, ergo, it's only looking for
blank fields.

Thanks much for the help!!!|||On 6 Jan 2005 09:02:41 -0800, Roy wrote:

> Thanks much Ross, after some toying around, the end product that works
> is:
> WHERE (x LIKE '%[^0-9a-z]%')
> I'm unsure why having the "NOT" specified beforehand produces no
> results, but it doesn't. I'm assuming it's because sqlserver perceives
> the NOT as referring to the wildcards too, ergo, it's only looking for
> blank fields.
> Thanks much for the help!!!

It looks to me like your query is requesting those rows that contain at
least one non-letter, non-digit character. I thought you wanted rows that
contained no letters and contained no digits... maybe I'm still confused
... but if you've got what you want, great.