Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

SCCS exporting files slow

Created: 02 Nov 2012 | 22 comments

Hello,

 

I have many jobs the query an sql DB and export the files to a network share.  The files are exported as .xls or .csv.  On average, the files are 20-100K.

It seems the files take a few minutes each to export.  Is there some settings I can change in CCS v10.5.1 to speed up the export times?

 

Thank you in advance.

Comments 22 CommentsJump to latest comment

cmccoy2's picture

If you have these running in a task list then the task list by default is running all of the queries concurrently. The task list will wait until all of the queries are completed before it starts the export process.  In the Task List Options, you can change the task list to run "sequentially".  This will run each query one at at time and then export the results as each query is completed.  I am not sure if this solves your problem, but it may help out.   I don't think there is any way to speed up the exporting process.   Another idea may be to export the files to a folder that is local to the RMS server and then copy them to the network share.

Hope this helps

Rick_D's picture

Thanks cmccoy2.

The queries are running quick however it's the exports that are taking a very long time.  If one task list has, let's say 50 queries......and let's say on 5 of those queries have data returned.  Every query is set to export to a network share to a .csv file.  CCS should not export empty files but only those files that have data.

In one example, I have a task list that has 65 queries.  On any given day, 10 will return data and therefore should be exported.  The queries themselves complete fast, however the exports and the entire task list takes over 2 hours to complete.  If I were to manually rerun a query with an export of a file maybe 100k, it completes in seconds.  The issue seems to be when the entire schedule runs that all task lists take a while - Exports being the issue.

Symantec had my add a couple setting to the registry

GlobalLovJobLimit 0x00000064 - not sure

GlobalNormalJobLimit 0x00000064 - not sure

LowJobLimit 0x00000006 - allows for 6 task lists to run at once (this actually helped schedule finish sooner)

NormalJobLimit 0x00000006 - allows for 6 task lists to run at once (this actually helped schedule finish sooner)

 

Basically looking to find out how to optimize my servers to run these schedules faster.  They were fine prior to the rebuild a few months ago.  (Prior to my starting working here).

 

Thanks

cmccoy2's picture

There is a hard limit on the number of concurrent task lists that you can run at any given time which is 10.  The NormalJobLimit reg key actually controls the number of queries that a given user can run concurrently.  6 is the default, however if the box is big enough then you can bump that up to 10, 15, 24, etc.  The GlobalNormalJobLimit will control how many queries can be run concurrently between any user sessions.   If there isn't a lot of other queries/task lists running on the machine, you can safely increase the NormalJobLimit and it will be able to process more queries concurrently.  I am not sure if that will help out with the exporting, but it might.  Another option might be breaking up the queries that run into smaller task lists and seeing if that helps with the efficiency of exporting the files.

 

Rick_D's picture

So does the "LowJobLimit" and "GlobalLowJobLimit" control the number of "task list" that can run concurrent?

Rick_D's picture

With two task list running right now, all queries are complete.  I can view them in the Task Status window.  Most results are empty and it's still taking 3-5 minutes to export a 14K .csv file!!

I would think that since the queries are set to only run the post process command if the "record count >= 1" that CCS would skip those queries and move to the next.  Removing the queries with "Records: 0/0 from the Task Status window.

Very new to CCS, thanks for your patience with all my questions!!

 

 

cmccoy2's picture

You may want to check your event logs to see if anything is strange may be happening when it is exporting.    Are you only exporting the grid as a post-processing process?  to troubleshoot, I would maybe create a new task list and bring over a few queries at a time to see if the same symptoms occur.   If you still get a delay, then you may need to get technical support to help out with enabling verbose logging and try to nail down the issue.

Rick_D's picture

ok, last question.....

Is there a limit to the number of exports that can take place at a time?

Our custom SQL queries finish almost instantly, the exports take 3-5 minutes each!

cmccoy2's picture

I am not sure if there is a limit to the number of exports that can occur concurrently.  I would guess that this is not a setting that is configurable.

Rick_D's picture

I have one task list that runs the max. 64 (sql) queries.  Of those 64 queries, maybe 20 of them will return output that meets the criteria.  Each query finishes in seconds!!  CCS is setup to only run the post process command to export the data to .csv IF the results are => 1.  Since only 15-20 queries return data, I would think that CCS would skip those other 30+ queries and move right to processing those WITH data that needs to run the post process command!!  This task list takes 2.5 + hours!!

Are there any registry settings or other configs to speed up this process?

 

cmccoy2's picture

I am not aware of any registry keys that could adjust the settings.  

Sorry

Rick_D's picture

cmccoy2,

I made some changes to better allow me to monitor the exports.  It appears that on 2-3 exports run at a time.  So every minute, 2-3 exports take place.  Do you know if there's a way to increase the number of exports that take place at one time?

Matt Plourde's picture

Rick - Export is batched at the end of the tasklist (once all queries have completed). I know of no way to configure this.

I did notice some slowdown with this porcess after some PCU in 10.5 or 10.5.1... but that's just based on my own internal "math"... nothing emperical.

If speed is a concern, I'd recommend breaking apart the tasklist into a few smaller ones.

Rick_D's picture

Matt,

Even if there are 6 task list running, only 2-3 exports will happen at one time.

I also can't seem to get a batch file to run at all when called from the post process command!

Matt Plourde's picture

Gotchya. I don't think there is a way to configure the # of exports.

For the post process command, ensure the user on the schedule can run the command. you might have difficulty running the command from the RMS bin folder on a win2k8 system (especially if it's trying to modify files in that location). I'd recommend running a file from somewhere else (like d:\ccscrap)..

(Edit: I'm pretty sure you have to disable UAC for post proc bat's/cmd's/exe's to run too. Make sure the scheduled user is in the local sec policy: "logon as a batch job"... can't hurt.)

Rick_D's picture

I was under the impression that you could only run commands from the BIN folder.  I just tried to set up the command to run from D:\SCCS\..........batchfile.bat!

I get the following error:

 

You don't have permission to run programs from the specified folder.  Please contact your local RMS Console System Administrator to change folder settings for you.  You may still continue with adding/modifying this post process command.

Do you want to continue?

 

CCS/RMS runs with service account that is the local admin on the server!

Matt Plourde's picture

You can run them from anywhere.

I think UAC or something else may be in play for you there...

Rick_D's picture

I figured out the permissions issue and I can now run the post process however, this is the error I get each time now.  Batch file never completes because of this.

 

Application output :
Application finished succesfully! 

Application: "D:\folderA\Folder1\batchfile.bat" "FilePaths.txt", ID 0x2BE4D6D0, Exit Code: 0x2BE4D6D0

The text file passed as a parameter that is referenced by the batch file to MOVE the exported files to their respective folder.  This batch works well when I run it manually, just not when RMS runs it.

Here is the batch file:

 

@ECHO OFF
SETLOCAL

FOR /F "TOKENS=1,2 DELIMS=/ " %%A IN ('DATE /T') DO SET mm=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('DATE /T') DO SET dd=%%B
FOR /F "TOKENS=3* DELIMS=/ " %%A IN ('DATE /T') DO SET yyyy=%%B

set /A dd=%dd%
set Pdate=%mm%-%dd%-%yyyy%
SET _EXEC=C:\WINDOWS\SYSWOW64\CMD.EXE
for /F "tokens=1-2 delims=," %%A in (%1) do (%_EXEC% /C Move /Y %%A %%B) >> "D:\FolderA\Folder1\Logs\%Pdate%-Export2Folder.log"

 

 

Matt Plourde's picture

you need to "path" both the .bat file and the output file in RMS (where u enter the post proc cmd)... like so:

D:\folderA\Folder1\batchfile.bat D:\folderA\Folder1\FilePaths.txt

If D: doesn't work, try a share and UNC.

don't use quotes if you don't have a space in the path.

 

That should do it :)

Rick_D's picture

Still no luck :(

I randomly get this error too.

Matt Plourde's picture

I'm not gonna get this right... been too many years :)

But there is a compatibility or interactive service/process or "thingy" which must be disabled in windows. Sorry, I haven't run into that in so long, my notes are gone. It might also be some sorta "user experience" thingy.

I ran into this once and it was a bitch. It was something "special" on a specific version of winsvr 2k8.

Sorry  :(

Rick_D's picture

I'll continue to try different things.  If I EVER get a resolution here, I'll post it here.  Thanks for your help.