April 24, 2008 at 11:40 pm
Hi
I am having a procedure which will create view dynamically. I fine tuned the procedure , i reduced 3 seconds out of 13 seconds after all possible ways. from 10 seconds 9 seconds is taking for dynamic view creation.
EXEC('CREATE VIEW ' + @strViewName + @crlf + @strViewComment + '(' + @strViewList1 + @strViewList2 + @strViewList3 + ')'
+ @crlf + 'AS ' + @crlf + @strValList1 + @strValList2)
the above statement only taking 9 seconds for creating 80 views.anybody have an idea to reduce more time from the mentioned dynamic view
April 30, 2008 at 5:29 am
[font="Verdana"]Have you tried to execute it with sp_executesql?
From BOL:
Using sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.
Mahesh
[/font]
MH-09-AM-8694
April 30, 2008 at 6:49 am
Mahesh Bote (4/30/2008)
[font="Verdana"]Have you tried to execute it with sp_executesql?From BOL:
Using sp_executesql is recommended over using the EXECUTE statement to execute a string. Not only does the support for parameter substitution make sp_executesql more versatile than EXECUTE, it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.
Mahesh
[/font]
mahesh,
i cant try with sp_executesql , since i am creating a big view (many return fields and retrieving many fields). in my code i splitted the items in different variable and when i execute i do concodinating the variable.this i can't do with sp_executesql
i tryed
SET @strView = 'ALTER VIEW ' + @strViewName + @crlf + @strViewComment + '(' + @strViewList1 + @strViewList2 + @strViewList3 + ')'
+ @crlf + 'AS ' + @crlf + @strValList1 + @strValList2
EXEC SP_EXECUTESQL @strView
i am getting error
i have one idea, i dont know whether it is working or not.
i can generate .sql file for each view dynamically. and finally execute all .sql file.if this case would work. then tell me how to create .sql file at runtime and how to execute those .sql file
thanks in advance
April 30, 2008 at 7:03 am
It's not possible to help without knowing either the content of the various variables or you printing out the value of the variable you're trying to execute.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2008 at 8:43 am
How about trying out the same query by adding "dbo." to viewname try this out instead of only view name. In your Dynamic SQL Instead of " Create @ViewName try this Create "dbo." + @ViewName
This will skip some processing.
May 1, 2008 at 9:31 pm
Jeff Moden (4/30/2008)
It's not possible to help without knowing either the content of the various variables or you printing out the value of the variable you're trying to execute.
this is my code
following for view list
==================
SET @iRows = @@ROWCOUNT
SET @iRows = @iRows /3
SET @strViewList1 = ''
SELECT @strViewList1 = @strViewList1 + '[' + UserName + '],' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tUrNm WHERE RowNo <= @iRows
SET @strViewList2 = ''
SELECT @strViewList2 = @strViewList2 + '[' + UserName + '],' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tUrNm WHERE (RowNo > @iRows AND RowNo <= (@iRows * 2))
SET @strViewList3 = ''
SELECT @strViewList3 = @strViewList3 + '[' + UserName + '],' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tUrNm WHERE RowNo > (@iRows * 2)
SET @strViewList1 = 'strType,lngHK_ID,' + @strViewList1 -- Include strType & lngHK_ID at the front of the list
IF LEN(@strViewList1) > 7999 OR LEN(@strViewList2) > 7999 OR LEN(@strViewList3) > 7999
BEGIN
RAISERROR ('Total length of the view column list is too great to handle (max is approx 15960 chars).', 10, 1)
RETURN(1)
END
following is for comment
====================
SET @strViewComment =
'
/*
This is one of a set of views, one for each SK VAL table in EMS area ' + @strEmsArea + '. The views are
auto created/updated by running pUpdate_VAL_tbl_Views. This should be re-run for the affected area
whenever SK models are added/deleted or updated. Each view has columns for the superset of strUserNames
associated with all the strAttributeNames for the EMS area. It selects relevant columns from the VAL table
and nulls for all others. This allows any number of such views for the same area to be UNIONed together.
Procedure pGet_Attr_Vals_From_Views does this automatically for a combination of supplied constraints.
*/
'
IF @strViewList2='' AND @strViewList1<>'' AND @strViewList3 = ''
BEGIN
PRINT 'tEST'
IF RIGHT(@strViewList1,1) = ','
SET @strViewList1 = LEFT(@strViewList1, LEN(@strViewList1)-1) -- Remove unwanted comma from end
ELSE SET @strViewList1 = LEFT(@strViewList1, LEN(@strViewList1)-3) -- Remove unwanted crlf & comma from end
END
ELSE IF @strViewList1<>'' AND @strViewList2<>'' AND @strViewList3 =''
BEGIN
IF RIGHT(@strViewList2,1) = ','
SET @strViewList2 = LEFT(@strViewList2, LEN(@strViewList2)-1) -- Remove unwanted comma from end
ELSE SET @strViewList2 = LEFT(@strViewList2, LEN(@strViewList2)-3) -- Remove unwanted crlf & comma from end
END
ELSE IF @strViewList1<>'' AND @strViewList2<>'' AND @strViewList3<>''
BEGIN
IF RIGHT(@strViewList3,1) = ','
SET @strViewList3 = LEFT(@strViewList3, LEN(@strViewList3)-1) -- Remove unwanted comma from end
ELSE SET @strViewList3 = LEFT(@strViewList3, LEN(@strViewList3)-3) -- Remove unwanted crlf & comma from end
END
following is for value list
====================
SET @iRows = @@ROWCOUNT
SET @iRows = @iRows / 2
SET @strValList1 = ''
SELECT @strValList1 = @strValList1 +
CASE
WHEN vColName IS NOT NULL THEN '[' + vColName + ']'
WHEN vColName IS NULL THEN 'CAST(NULL AS ' + ColType + ')'
ELSE 'Error - New Type'
END + ',' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tVlNm WHERE RowNo <= @iRows
SET @strValList2 = ''
SELECT @strValList2 = @strValList2 +
CASE
WHEN vColName IS NOT NULL THEN '[' + vColName + ']'
WHEN vColName IS NULL THEN 'CAST(NULL AS ' + ColType + ')'
ELSE 'Error - New Type'
END + ',' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tVlNm WHERE RowNo > @iRows
/*
PRINT 'LEN(@strValList1): ' + CAST(LEN(@strValList1) AS varchar(20))
PRINT 'LEN(@strValList2): ' + CAST(LEN(@strValList2) AS varchar(20))
*/
IF LEN(@strValList1) > 7920 OR LEN(@strValList2) > 7999 -- May be truncated by overflowing max length
BEGIN
RAISERROR ('Total length of the view value list is too great to handle (max is approx 15920 chars).', 10, 1)
RETURN(1)
END
IF @strValList2 = '' AND @strValList1<>''
BEGIN
PRINT 'tEST1'
IF RIGHT(@strValList1,1) = ','
SET @strValList1 = LEFT(@strValList1, LEN(@strValList1)-1) -- Remove unwanted comma from end
ELSE SET @strValList1 = LEFT(@strValList1, LEN(@strValList1)-3) -- Remove unwanted crlf & comma from end
END
ELSE IF @strValList1<>'' AND @strValList2<>''
BEGIN
IF RIGHT(@strValList2,1) = ','
SET @strValList2 = LEFT(@strValList2, LEN(@strValList2)-1) -- Remove unwanted comma from end
ELSE SET @strValList2 = LEFT(@strValList2, LEN(@strValList2)-3) -- Remove unwanted crlf & comma from end
END
IF @strValList1<>'' SET @strValList1 = '''' + @strVal + ''',lngHK_ID,' + @strValList1
ELSE SET @strValList1 = '''' + @strVal + ''',lngHK_ID' + @strValList1
--SET @strValList1 = '''' + @strVal + ''',lngHK_ID,' + @strValList1 -- Include strType & lngHK_ID at the front of the list
SET @strValList1 = 'SELECT ' + @strValList1
IF @strValList2 = '' SET @strValList1 = @strValList1 + @crlf + 'FROM ' + @strSkValTbl
ELSE SET @strValList2 = @strValList2 + @crlf + 'FROM ' + @strSkValTbl
-- Create or update the view corresponding to this VAL table.
SET @strViewName = @strViewPrefix + @strVal + '_VAL'
and finally creating dynamic view
=========================
IF OBJECT_ID(@strViewName) IS NULL
BEGIN
EXEC('CREATE VIEW ' + @strViewName + @crlf + @strViewComment + '(' + @strViewList1 + @strViewList2 + @strViewList3 + ')'
+ @crlf + 'AS ' + @crlf + @strValList1 + @strValList2)
SET @intErrorCode = @@ERROR
IF @intErrorCode <> 0
BEGIN
PRINT 'TABLE NAME :' + @strSkValTbl
RETURN 11
END
END
ELSE
BEGIN
EXEC('ALTER VIEW ' + @strViewName + @crlf + @strViewComment + '(' + @strViewList1 + @strViewList2 + @strViewList3 + ')'
+ @crlf + 'AS ' + @crlf + @strValList1 + @strValList2)
IF @@ERROR <> 0
BEGIN
PRINT 'TABLE NAME :' + @strSkValTbl
RETURN 11
END
END
May 2, 2008 at 8:09 am
Part of the problem is that you go back to delete the trailing comma... you basically do something like this...
DECLARE @somestring VARCHAR(8000)
SET @somestring = ''
SELECT @somestring = @somestring + someotherstring + ','
FROM sometable
... and that leaves trailing commas which you must update out of @somestring.
A better way is to not include the trailing comma so you don't need the extra code to remove it... like this...
DECLARE @somestring VARCHAR(8000)
SET @somestring = NULL
SELECT @somestring = COALESCE(@somestring+',','') + someotherstring
FROM sometable
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 12:26 am
Jeff Moden (5/2/2008)
Part of the problem is that you go back to delete the trailing comma... you basically do something like this...
DECLARE @somestring VARCHAR(8000)SET @somestring = ''
SELECT @somestring = @somestring + someotherstring + ','
FROM sometable
... and that leaves trailing commas which you must update out of @somestring.
A better way is to not include the trailing comma so you don't need the extra code to remove it... like this...
DECLARE @somestring VARCHAR(8000)SET @somestring = NULL
SELECT @somestring = COALESCE(@somestring+',','') + someotherstring
FROM sometable
Jeff,
how i use COALESEC function for the following code
SET @strValList2 = ''
SELECT @strValList2 = @strValList2 +
CASE
WHEN vColName IS NOT NULL THEN '[' + vColName + ']'
WHEN vColName IS NULL THEN 'CAST(NULL AS ' + ColType + ')'
ELSE 'Error - New Type'
END + ',' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tVlNm WHERE RowNo > row_count
AND table_id = @tablename
May 7, 2008 at 12:48 am
[font="Verdana"]
...SELECT @strValList2 = @strValList2 +
CASE
WHEN vColName IS NOT NULL THEN '[' + vColName + ']'
WHEN vColName IS NULL THEN 'CAST(NULL AS ' + ColType + ')'
ELSE 'Error - New Type'
END + ',' +...
not sure but might be this way ...
... Select @strValList2 = @strValList2 + Coalesce(vColName, ColType, 'Error - New Type') ...
confirm on this.
Mahesh
[/font]
MH-09-AM-8694
May 7, 2008 at 4:37 am
mahesh
what about ','. the technique is to remove tryling commas
May 7, 2008 at 5:37 am
[font="Verdana"]
shamshudheen (5/7/2008)
maheshwhat about ','. the technique is to remove tryling commas
I didn't get you. It is column separator right?
Mahesh[/font]
MH-09-AM-8694
May 7, 2008 at 6:37 am
No it is not column seperator, it is charactre in the string. see my previous post . which making a string seperated by commas
May 7, 2008 at 7:36 am
shamshudheen (5/7/2008)
Jeff,how i use COALESEC function for the following code
SET @strValList2 = ''
SELECT @strValList2 = @strValList2 +
CASE
WHEN vColName IS NOT NULL THEN '[' + vColName + ']'
WHEN vColName IS NULL THEN 'CAST(NULL AS ' + ColType + ')'
ELSE 'Error - New Type'
END + ',' +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tVlNm WHERE RowNo > row_count
AND table_id = @tablename
Nothing to test on to be sure, but I believe this will do...
SET @strValList2 = NULL
SELECT @strValList2 = COALESCE(@strValList2+',','') +
CASE
WHEN vColName IS NOT NULL THEN '[' + vColName + ']'
WHEN vColName IS NULL THEN 'CAST(NULL AS ' + ColType + ')'
ELSE 'Error - New Type'
END +
CASE WHEN RowNo % 4 = 0 THEN @crlf ELSE '' END
FROM #tVlNm WHERE RowNo > row_count
AND table_id = @tablename
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply