Ban Man Pro 8 and higher offers an optimization feature for high-traffic web sites.  This feature uses temporary SQL tables for recording statistics and querying valid campaigns.  This significantly reduces the number of database queries and enhances performance.  The only drawbacks are your statistics will be delayed by up to 2 minutes and it is possible for a campaign to slightly over serve because of the 2 minute delay.

To use this feature you must have administrator access to the SQL server in order to make sure the SQL Server Agent is running and in order to create a SQL Job. 

Note: SQL 2005 Express does not include the SQL Agent Job Scheduling Service so to use this feature you must be using the Workgroup, Standard, or Enterprise version of SQL 2005.

Here are the steps to utilize this feature:

1. Verify that the SQL Server Agent is running on the SQL Server.  Also verify that the SQL Server Agent is set to start automatically with the operating system so that after a reboot it continues running.
    
2. Create the SQL Server Job that will create the cache tables every 1 minute.  This is done by executing the script below within SQL Query Analyzer.  Before executing, you must replace 'BanManPro' with your database name near the beginning of the script.  Be sure to copy the entire script into Query Analyzer.
    

   DECLARE @DatabaseName varchar(100) ---/////////////////You must set the database name in the line below ////////// Set @DatabaseName='BanManPro' --////////////////// Do not modify below this line //////////////////////////// DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Job_BanManPro_Optimization') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''Job_BanManPro_Optimization'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Job_BanManPro_Optimization' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Job_BanManPro_Optimization', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Transfer Stats from Temp Tables', @command = N'Exec BMP_proc_TransferTempStats', @database_name = @DatabaseName, @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'sp_BMP_CreateTemporaryBannersTable', @command = N'Exec sp_BMP_CreateTemporaryBannersTable', @database_name = @DatabaseName, @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'BanManProOptimization', @enabled = 1, @freq_type = 4, @active_start_date = 20070522, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:

    

3. Start the SQL Server Job.
   
    
4. Now enable the optimization feature in Ban Man Pro by clicking Preferences/Setup and enabling the option.

Monitor the Ban Man Pro stats for a few minutes to make sure new stats are appearing every 1 minute.  This will tell you if the job is executing properly.