The following is cut from SQL profiler, database is SQL 2000 SP4. Query 1 takes almost zero time and 26 reads. Query 2 takes 16 millsecs and 2862 reads and the only difference is that Query2 has parameters. I have run the query's multiple times and in different order and the results are the same. My reading of the documentation says that Query 2 should be faster due to not having to recreate the execution plan. If the execution plan is a bad one and is cached how do I remove it. Is there anyway to force a recompile or am I know in stored procedure territory.
Can anyone give me an explanation as it looks like we should be changing our code to use literal's where ever possible rather than parameter substitution.
TIA
SQL:BatchCompleted exec sp_executesql N'select *
from PA_REC_RECEIPT_ALLOCATIONS
where RECEIPT_PREFIX = ''LMH''
and RECEIPT_SUFFIX = 10652
' Microsoft SQL Server Management Studio - Query 0 26 0 0 3256 51 2006-09-20 13:15:32.843
-- Query 2
SQL:BatchCompleted exec sp_executesql N'select *
from PA_REC_RECEIPT_ALLOCATIONS
where RECEIPT_PREFIX = @P1
and RECEIPT_SUFFIX = @P2
', N'@P1 varchar(3),@P2 int', 'LMH', 10652
Microsoft SQL Server Management Studio - Query 16 2862 0 13 3256 51 2006-09-20 13:15:35.830
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
I need to do some dynamic execs based on SQL strings as variables taken from a table.
I need the best performance. In testing, I'm finding that the string I am executing is taking 20 times as long to run from within an Exec or sp_executesql vs running the same string from directly within query analyzer. Of course, there's some overehead with Exec, and we're talking milliseconds (30ms in query analyzer vs 600 in an Exec), but this still seems like a lot of overhead. I assume both statements still need to be compiled and an execution plan created. Is this difference in time normal?
I ask because I'm debating about whether to execute this process (which may need to run 9000 times a second) in a stored procedure or using an executable running separately on the server. I assume an executable's performance will approximate running the same query from query analyzer.
Here's the query:
Select GroupNodeNumber, SB.* From (Select Distinct GroupNodeNumber from #QASData) RD Cross Join (SELECT * From SQL_QAS_ServiceFailure WHERE QASEventID=99077223) SB Where (GroupNodeNumber=10 and (((VerbalPOD=-1) or (Canceled=-1) or ((DeliveryRequirement) = -1) or (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) or (InvoiceType<>0)))) or (GroupNodeNumber=20 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>240) and (VerbalPOD=0) and (InvoiceType=0))) or (GroupNodeNumber=30 and ((Canceled=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)>120) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=240) and (VerbalPOD=0) and (InvoiceType=0))) or (GroupNodeNumber=40 and ((Canceled=0) and (InvoiceType=0) and (VerbalPOD=0) and (NOT (DeliveryRequirement) = -1) and (DateDiff(n,GetDate(),DeliveryRequirement)-(DestTimeAdjustment)<=120))) or (GroupNodeNumber=50 and ((ShipmentStatus='POD') and (VerbalPOD=0) and (InvoiceType=0)))
I have the following queries that run on a view called EntrySummary:
1)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0 AND [EntrySummary].[CreatedBy] = @CreatedBy1',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
2) exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[CreatedBy] = @CreatedBy1 AND [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
(The only difference between the two queries being the order of the where clauses)
Both return the correct answer (4144), but Query 2 takes 10-15 seconds whereas Query 1 takes < second. If the same query is resubmitted several times, this doesn't affect the times.
(The vast majority of the records have a status of 4, but only about 3% will be created by the person).
Replacing Count(1) with count(*) makes both queries return quickly.
Is sp_executesql creating poor execution plans ? Can anyone explain this behaviour?
Okay, so I came across an odd performance issue that I'm wondering if some guru can help me out with.
I have a query that uses a paging algorithm that uses a paging algorithm and a table variable, then gets a page of data based on a join to that table variable. Here's a simplified query using the algoritm:
--declare table variable... not shown for brevity
--make sure we only store the least amount of records possible SET ROWCOUNT ( @pageNumber + 1 ) * @pageSize
--insert into table variable INSERT INTO @TableVariable( Key ) SELECT key FROM table WHERE whatever = @p1
--we only want one page of data SET ROWCOUNT @pageSize
--now get the page of data from the table SELECT key FROM table WHERE whatever = @p1 AND [TableVar Identity Column] > @pageNumber * @pageSize
The algorithm works great for our needs, BUT, I noticed something a little odd about its behavior during performance testing.
In particular, when I run the query using Sql Server Management Studio, where I manually DECLARE all the variables it ends up needing only 156 reads to complete the job. When I call it from the app using ADO.NET, however, I noticed it needs 310 reads! Huh?
I looked for differences, and the only one I could determine was that ADO.NET passes the query and uses sp_executesql and passes the parameters vs. declaring and setting them statically before executing the query. I confirmed that this was the issue by manually running sp_execute SQL and seeing that it took roughly the same number of reads (274) to process the query.
Naturally, I don't want the time it takes to perfrom my query to double, but and frankly I don't understand why there would be a difference in performance. Can anyone help me track down what is going on and suggest to me how to fix the problem.
I assume that SQL Server Management Studio optimizes the execution path somehow, but I'm not sure how to gain the same benefit for my passed query. Can I enable something with hints? Is there something else going on that I should know about?
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL, N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT', @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.
Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.
I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.
Snippet that is not working:
declare @db_name varchar(80)
declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.
Hi I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using EXEC sp_execute sqlstmt. Here is piece of code.
DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' OPEN c1 FETCH NEXT FROM C1 INTO @v_TableName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @v_SQL= 'DROP TABLE ' + @v_TableName --EXEC @v_SQL PRINT @v_SQL IF @v_Error<>0 BEGIN SELECT @ErrorCount=@ErrorCount+1 PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName --GOTO ErrorHandler END FETCH NEXT FROM C1 INTO @v_TableName END CLOSE c1 DEALLOCATE C1
hi everybody How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server. ie a string can contain insert into a table statement,delete a row from a table statement, update etc. Thanks all of You
Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.
I'm using sp_ExecuteSQL and have hit the 4000 character wall
Hi all,Can sp_executesql used inside a user defined function, itried but it has compiled well, but when i call the functio it showsOnly functions and extended stored procedures can be executed fromwithin a function.What i have went wrongThanks in advancethomson
I have a full sql statement which was generated dynamicly, and need toexecute that string and then take the output and generate aspreadsheet document based on the output. I'm new to sql and the bookI have doesn't really explain much. Anyone with an example of theirwork would be appreaciated.thank you.
(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @callingQueue + ' INTO @msgTable WHERE conversation_group_id = '
+ CAST(@conversationGroup AS char) + '), TIMEOUT 2000'
EXEC sp_executesql @SQL, N'@msgTable TABLE output', @msgTable out
I get the following message:
Msg 137, Level 15, State 2, Procedure CENTRAL_Queue_Processor, Line 92
Must declare the scalar variable "@msgTable".
I have decalred the variable but it is a table variable, this leadds me to believe sp_execute sql only supports scalar varibles not table variables, BOL does not say yes or no in this respect.
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
Hi all, I just wanted to know why this doesn't work: if @1's values is computer---------------------------------------------------------------------------------------------------------- BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ' + '''%@x1%''' + ' OR notes like ' + '''%@x1%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%@x1%' OR notes like '%@x1%'-----------------------------------------------------------------------------------------------------------But this works: BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ''%''+ @x1 + ''%'' OR notes like ''%'' + @x1 + ''%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%'+ @x1 + '%' OR notes like '%' + @x1 + '%'---------------------------------------------------------------------------------------------------------------I just don't get it ?? Doesn't sp_executesql just replaces the @x1 with @1?
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
Hi, I want to use the output of the sp_executesql to update a coulmn in the table. example -first i run the below to get output execute sp_executesql @Query, @returnedCount output
-then I want to use that output to update another coulmn in the table update tableName set coulmn=@returnedCount
I am new to this and cannot figure out how. Can someone please guide me? thank you!!
Hi all i have Function and in the context of this function i need to build a Dynamic Query String according to input parameters and execute it with sp_executesql. BUT until now i didn't know that SQL doesn't allow to have Exec command within a function,am i right? Apparently this is true because for example create the following Function..
Create Function Test(@Input int) Returns int AS Begin Exec sp_who -- only for Test purpose Return @Input End
Now Execute this --> Select dbo.test(12)..... Sql Server will return the following Error
Server: Msg 557, Level 16, State 2, Procedure Test, Line 6 Only functions and extended stored procedures can be executed from within a function.
Could Any one help me? i need function with dynamic Sql execution because i can only use function in SELECT statements !!!
Any help greatly would be appreciated. Kind Regards.
IF OBJECT_ID(N'aquery') is not null DROP FUNCTION aquery GO
CREATE FUNCTION aquery() Returns nvarchar(500) as Begin Declare @var nvarchar(500); Set @var = 'Select Distinct Description from dbo.tblScanners'; Return @var end GO
exec sp_executesql aquery;
I created that code to prove if it works. But the result doesn´t appear, and the message is:
I have not used this sp. We have a dynamic SQL statement generated by a sp.For performance reasons I would like to use it to reduce the number ofexplain plans created. I would like to understand its usage and pitfalls(if any) to its use. Any comments from the user community?
I'm trying to build a stored procedure with parameters and sp_executesql. I can't seem to get the types correct. I have two parameters I want to pass: @ADDIVNumber which will be a bigint and @Where which can be varchar(500). I can't seem to figure out how to get the varible types right.
ALTER PROCEDURE [dbo].[AMTRANHDRPaidTranHistAP]
@CharVariable varchar(500),
@IntVariable bigint
as
Declare
@SQLHolder nvarchar(4000)
set @SQLHolder = 'SELECT T1.SMBNKNumber, T1.AMACTNumber, T1.AMALTNumber,
I'm not very familiar with Dynamic SQL, so you may find this question dumb. Sorry if this is the case ;-) I've been reading Raul Garcia's blog about SQL injection and I would like to be able to do something like this:
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
I am attempting to create dynamic SQL with sp_executesql.
However, it seems that parameters can't be created arbitrarily in the query string. In particular, the name of the table apparently can't be parametrized, or I am doing something wrong.
I use SQL Server 2000 with the latest service pack.
The table task has a few columns of which TaskID is Primary Key and INT.
This works fine:
DECLARE @pot nvarchar(200) SET @pot = 1 DECLARE @pot2 nvarchar(200) SET @pot2 = 'task' EXEC sp_executesql N'SELECT * FROM task WHERE TaskID = @pot', N'@pot varchar(128), @pot2 varchar(128)', @pot, @pot2
However, this does not work:
DECLARE @pot nvarchar(200) SET @pot = 1 DECLARE @pot2 nvarchar(200) SET @pot2 = 'task' EXEC sp_executesql N'SELECT * FROM @pot2 WHERE TaskID = @pot', N'@pot varchar(128), @pot2 varchar(128)', @pot, @pot2
Note that the only change in the second case here is to pass the same table name as a parameter.
The error message is: Must declare the variable '@pot2'. However, @pot2 is clearly declared already!
1. Is there any way to parametrize the table name as above? 2. Is there any general guideline on what in the query can be parametrized?
Note: I know that I could parametrize the table name by concatenating the first parameter of sp_executesql but that would assume my question (1) was answered 'no' already.
I am using sp_executesql to get some data but it is not working. Is there a way to actually see the actual statement where subsituted variables are replaced with the actual values.
+ Case @MatchAmount When 1 Then N' and Amount = @BillingAmount ' Else N'' End
+ Case @MatchTicket When 1 Then N' and LTrim(TicketNumber) = STUFF(STUFF(@TicketNumber,Len(@TicketNumber)-@RemoveRight+1,@RemoveRight,''''),1,@RemoveLeft,'''') ' Else N'' End
+ Case @DaysDiff When 0 Then N'' Else N' and DATEDIFF(d,@BillingDate , InvoiceDate) <= @DaysDiff ' End
+ Case @MatchName When 1 Then N' and Left(Name,@CharsToMatch) = Left(@PassengerName, @CharsToMatch) ' Else N'' End ;
