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.
精彩评论