Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vk59
Starting Member
38 Posts |
 Posted - 2004-04-23 : 06:43:20
|
| I have Stored Procedure which has text parameter. I pass a SQL Statement to this parameter.DataLength of Parameter is more than 100,000 characters. Its taking 2 minutes to execute the procedure. During Execution of the procedure my server get hangged and its uses 100% of the memory. After Completion of execution all the memory is freed. But If I Directly execute the statement( the one which i pass as an input) its taking only 2 to 3 secsMy Procedure:CREATE PROCEDURE ExecSQLStatement (@Sqlvc TEXT) ASBEGINEXEC(@Sqlvc)ENDI call the stored proc as below:Calling Procedure:EXEC ExecSQLStatement 'UPDATE TABLENAME SET COL1=1 and COL2=''1234567890 1234567890123456789012345678901234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890..........(and so to 100,000 characters)'' WHERE COL3=1 'Executing Directly:UPDATE TABLENAME SET COL1=1 and COL2='1234567890 1234567890123456789012345678901234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890 1234567890..........(and so to 100,000 characters)' WHERE COL3=1 Can You guys please help me, why is it taking lot of time and eating away memory resources when i execute statement through a stored procedure and when executed directly its works fineThanks in advanceVK59` |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-23 : 09:57:10
|
| OK, why use dynamic at all?And what type of data needs to be updated with 100,000 bytes?Is it a text document?Plus, don't you need to worry about the size of the block?Why aren't you using UPDATETEXT/WRITETEXT, ect?Brett8-) |
 |
|
|
|
|
|
TEXT Parameter in a stored procedure