SQL express 2005 Query and SQL itself Performance Tunning

my query run much slow i called procedure of SQL express 2005 from desktop app (developed using vs 2008 C#).

procedure code:

USE [csoft]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[VolProc] 
  @P_prd varchar(2), -- e.g: 01
  @P_rep varchar(2), -- e.g: 31
  @P_yy varchar(2000), -- e.g: 2010-11|2010-开发者_StackOverflow社区10| ... 2009-09 year-month
  @P_lines varchar(8000), -- e.g: 1|2|3| ... 1025 lines
  @P_HS varchar(1) -- e.g: 1
AS

declare varials...
...
...

 set ...
 ...
 SET @YEARS = REPLACE(@P_YY,'|','')
 SET @PYS = 1
 SET @PYE = 6

-- --------------------------------------------
-- clean the data already exist in both tables
-- --------------------------------------------
 delete from haider_data
  dump tran cosft with no_log
 delete from dyn_data
  dump tran cosft with no_log

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

set @pp_prd = @p_prd
set @pp_rep = @p_rep
set @pp_hs  = @p_hs
set @pp_lines  = @p_lines

WHILE (@I <= LEN(@YEARS)/7) -- loop for all years and months to process
BEGIN
   SET @YY = SUBSTRING(@YEARS,@PYS,4) -- pick year to process
   SET @PYS = @PYS + 7
   SET @MM = SUBSTRING(@YEARS,@PYE,2) -- pick month to process
   SET @PYE = @PYE + 7

 IF CURSOR_STATUS('local', 'db_cursor') > 0
  BEGIN
    CLOSE DB_CURSOR
 DEALLOCATE DB_CURSOR
  END

  -- Report lines working...
 -- cursor to process only mentioned lines in @pp_lines only
  DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
    select view_text, sno FROM  month_scripts2
 where   prd_code = @pp_prd
 and     rep_code = @pp_rep
 and     sno in (select item from fnSplit(@pp_lines,'|'))
 order by sno

 OPEN db_cursor  
 FETCH NEXT FROM db_cursor INTO @VTEXT, @sno 
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
    SET @SQL = @VTEXT 
    SET @SQL = REPLACE(@SQL,'''YYYY''',@Q+@YY+@Q)
    SET @SQL = REPLACE(@SQL,'''MM''',@Q+@MM+@Q)
    Exec (@SQL)  

-- in @sql there is a already made select of view named markets
-- each line of @sql create different view with same name (select different in view)
-- and insert into Haider_data table for all 15 months

  INSERT INTO HAIDER_DATA SELECT mar.*,@sno FROM MARKETS mar order by title
  dump tran cosft with no_log
    FETCH NEXT FROM db_cursor INTO @VTEXT,@sno
 END   
 CLOSE DB_CURSOR
 DEALLOCATE DB_CURSOR

--
-- insert data into dyn_data
-- some code missing

   set @NUMS = @NUMS + 1
   SET @HSV = @Q+right('00'+convert(varchar,@nos),2)+'.'+@HS+@Q
      SET @SQL_STR = 'INSERT INTO DYN_DATA (PROJECT_YY, PROJECT_MM, TITLE, COL_TYPE,     COL_VALUE, SNO, HSNO,YY_MM,YEARS) '
   SET @SQL_STR = @SQL_STR + ' SELECT PROJECT_YY, PROJECT_MM, right('+@Q+'0000'+@Q+'+sno,4) + char(32) + char(32) + TITLE , ' + @HSV + ', sum(isnull(MARKET,0)), sno, ' + convert(varchar,@nums) + ', right(PROJECT_YY,2)+''_''+PROJECT_MM,  SUBSTRING(DATENAME(month, CAST(Project_yy + Project_mm + ''01'' AS datetime)), 1, 3) + ''-'' + SUBSTRING(Project_yy, 3, 10) ' 
   SET @SQL_STR = @SQL_STR + ' FROM HAIDER_DATA  WHERE project_yy = ' + @YY + ' and project_mm = ' + @MM + @COND + @cls_abc_filter + @u_r_filter
   SET @SQL_STR = @SQL_STR + ' group by PROJECT_YY, PROJECT_MM, TITLE,sno order by convert(int,sno),title '
   EXEC (@SQL_STR) 

   SET @I = @I + 1
END
-- end of procedure....

Question

when i press process button it takes min. 2 hours to complete. how can i fast query generation.

i am not a DBA person. my database size 4 GB and log fine grows much faster why i do not know. please tell me to restrict log grow.

can i shrink my log file by automatically once a month -- job scheduled can be in SQL Express 2005 ?

how can i tune my sql express 2005 to optimized performance ?

thanks & regards Haider

---

You read SQL queries that are stored in your views. So, the overall speed depends of what is actually in your view... Tricky design...

To avoid log file grow, you can change the recovery mode of your database to single. That is normally the default under SQL server express 2005. Otherwise, you have to backup your database first, then shrink the log file.

SQL server agent is not available in sql server express. If you want job scheduling, you must write it yourself or use a thirdparty product.