Dynamic SQL Question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic SQL Question

I have a query that handles searching through an activity log table. The users can select any of about 20 parameters to use in the search (multiple parameters allowed). They can also specify how many rows they want retrieved — there are over 750K rows in the table. My idea, in short, is to build the query dynamically in a stored procedure based on parameters passed in from the client. The dynamic sql would be run using sp_executesql. We are running SQL 2000. Questions: 1. Is there a performance difference between using SET ROWCOUNT vs. doing a SELECT TOP? 2. What is there a "best performing" way to pass the parameters to the stored proc? I’m considering two approaches: (a) set up 20 arguments in the CREATE PROCEDURE statement. The client would only pass those arguments that the user has selected as parameters. All others would be defaulted to null. (b) Setup a single varchar argument to hold a parameter string. Only selected parameters would be passed. Format would be compatible with that required by sp_executesql. Personally, I’m leaning towards method a. It eliminates the possibiity of passing the wrong parameter name to the server which would foul the query. Also, I won’t have to rely on the client-side programmer formatting the parameter string properly for sp_executesql. [8] Cheers, Buddy
SELECT TOP should be faster than SET ROWCOUNT, the main reason being that sql server can optimise a SELECT TOP statement because it’s part of the query. I’ve actually read that SELECT TOP should never be slower than SET ROWCOUNT, I’ve never seen it proven, but I’d back that. For your second question I’d also go with a), it’s alot more structured and would be easier to maintain. I’m not sure about the performance benefits of it though. Cheers
Shaun World Domination Through Superior Software
I would agree that a is the wisest approach, even if it is not the best performing. It leaves query maintenance in your hands, and not the developers. Also, passing raw sql from the client to the stored proc is not good practice from a security point of view, granted.. most times internally used software is not that much of an issue. I think ROWCOUNT and TOP perform similarly; neither will process the full resultset before stopping.
However, TOP can be placed inline, whereas ROWCOUNT must be set, queried, and then set back to 0, as it would otherwise affect subsequent queries also.
As far as my understanding goes, I agree with shaun that TOP is faster than SET ROWCOUNT. Chappy has also made a great point about TOP being inline. I would recommend going for approach a as the code becomes more managable and also the chances of error are less. Gaurav
My only question is why exactly you need to use dynamic SQL. You may have a valid reason to do this, however many times you can write your queries differently to avoid it. If, for instance, you wanted to have a dynamic WHERE clause as you have described, and a dynamic row count, you should be able to do this: CREATE PROCEDURE dbo.myProc
(
@rowcountINT = 0,
@var1CHAR(5) = NULL,
@var2INT = NULL,
@var3VARCHAR(10) = NULL
)
AS SET NOCOUNT ON SET ROWCOUNT @rowcount SELECT *
FROM myTable
WHERE field1 = COALESCE(@var1, field1),
AND field2 = COALESCE(@var2, field2),
AND field3 = COALESCE(@var3, field3) SET ROWCOUNT 0
GO Then in your application just pass in the parameters you need. If you need to do a dynamic ORDER BY clause that can be done using COALESCE also, but it is not quite as neat as the dynamic WHERE clause. As I stated before, you may still have a need for dynamic SQL, however I usually try to avoid it like the plague.
You know, I never even considered using COALESCE. Let’s see if I understand your solution correctly:<br /><br />When @var1 is NULL, then field1 = COALESCE(@var1, field1) evaluates to field1 = field1, which is always true, and essentially has no filtering effect.<br /><br />If this is the correct behavior, then yes, I won’t have to do any dynamic SQL!<br /><br />Its good to know that I still have stuff to learn.<br /><br />Thanks,<br /><br />Buddy<br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Negative</i><br /><br />My only question is why exactly you need to use dynamic SQL. You may have a valid reason to do this, however many times you can write your queries differently to avoid it. If, for instance, you wanted to have a dynamic WHERE clause as you have described, and a dynamic row count, you should be able to do this:<br /><br />CREATE PROCEDURE dbo.myProc<br />(<br />@rowcountINT = 0,<br />@var1CHAR(5) = NULL,<br />@var2INT = NULL,<br />@var3VARCHAR(10) = NULL<br />)<br />AS<br /><br />SET NOCOUNT ON<br /><br />SET ROWCOUNT @rowcount<br /><br />SELECT *<br />FROM myTable<br />WHERE field1 = COALESCE(@var1, field1),<br />AND field2 = COALESCE(@var2, field2),<br />AND field3 = COALESCE(@var3, field3)<br /><br />SET ROWCOUNT 0<br />GO<br /><br />Then in your application just pass in the parameters you need. If you need to do a dynamic ORDER BY clause that can be done using COALESCE also, but it is not quite as neat as the dynamic WHERE clause.<br /><br />As I stated before, you may still have a need for dynamic SQL, however I usually try to avoid it like the plague.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">
Yes, thankyou for the COALESCE tip. <br /><br />I knew about the command but never thought to use it in this situation, I normally write conditional criteria in longhand such as…<br /><br />SELECT *<br />FROM myTable<br />WHERE <br /> ((field1crit IS NULL) or (field1 = @field1crit)) and<br /> ((@field2crit IS NULL) or (field2 = @field2crit)) and<br /> ((@field3crit IS NULL) or (field3 = @field3crit)) <br /><br />Ive never been overly happy with the OR’s in my where clause. The execution plans didnt seem to mind tho, but I still expect COALESCE is more optimised. I shall have fun testing the performance differences, at least if theyre the same, yours looks nicer <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ /><br />
A bit late now, but I just realised I could have shortened my longhand even so, by using<br /><br />field1 = ISNULL(@field1crit, field1)<br /><br />Live and learn <img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ /><br />
Hey guys, I had posted a similar answer in
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=624 Thought u must have seen that… Anyway, this way of developing the query is more flexible and i guess high performing as well. Gaurav
Also when u use this solution, make sure that u have special handling for NULLABLE columns. e.g. if colummn field1 in chappy’s query
field1 = ISNULL(@field1crit, field1)
is NULLABLE then for every NULL value in the column field1, if the user passes no value or NULL value in @field1crit, the statement becomes NULL = NULL which is unknown for ANSI_NULLS ON. So these records will not show in the resultset. Hence u’ll have to write the query like this…
isnull(field1,”) = ISNULL(ISNULL(@field1crit, field1),”)
or else like isnull(field1,”) = COALESCE(@field1crit, field1,”) This will display the rows in the resultset even if field1 is null.
Also I would like to mention that I have observed that ISNULL has better performance than COALESCE. Gaurav
Interesting point, but the same also applies to COALESCE field1 = COALESCE(@fieldcrit1, field1) if both field1 and @fieldcrit1 are NULL, COALESCE will return NULL, and NULL = NULL will evaluate to false. I have since reverted to my original method of ((field1crit IS NULL) or (field1 = @field1crit)) Secondly because I did not find a noticeable performance difference between the two methods, over a very large data domain set, but primarily for the simple fact that if field1 happens to consist of a complex expression, you must quote it twice, whereas the ISNULL method you may just quote it once so it could be… ((@strikelow IS NULL) or (ROUND(100*CONVERT(FLOAT, CASE ISNULL(COUNT(HIR_PositionNo), 0)
WHEN 0 THEN (SUM(CONVERT(int, CASE WHEN HIR_PositionNo >= 3 THEN 1 ELSE 0
END))) ELSE (CONVERT(FLOAT, SUM(CONVERT(int, CASE WHEN HIR_PositionNo >= 3 THEN 1
ELSE 0 END))) / COUNT(HIR_PositionNo)) END), 2)) >= @strikelow) instead of (COALESCE(@strikelow, ROUND(100*CONVERT(FLOAT, CASE ISNULL(COUNT(HIR_PositionNo), 0) WHEN
0 THEN (SUM(CONVERT(int, CASE WHEN HIR_PositionNo >= 3 THEN 1 ELSE 0 END)))) ELSE
(CONVERT(FLOAT, SUM(CONVERT(int, CASE WHEN HIR_PositionNo >= 3 THEN 1 ELSE 0
END))) / COUNT(HIR_PositionNo)) END), 2)) >= ROUND(100*CONVERT(FLOAT, CASE
ISNULL(COUNT(HIR_PositionNo), 0) WHEN 0 THEN (SUM(CONVERT(int, CASE WHEN
HIR_PositionNo >= 3 THEN 1 ELSE 0 END))) ELSE (CONVERT(FLOAT, SUM(CONVERT(int, CASE
WHEN HIR_PositionNo >= 3 THEN 1 ELSE 0 END))) / COUNT(HIR_PositionNo)) END), 2)) If performance is the same (maybe im mistaken), I choose first method purely because its more maintainable/readable. But a valid point about the NULL = NULL problem. The above queries would need changing if they involved NULLable columns.

Apologies gaurav, I see you had already noticed that the NULL problem applies to COALESCE also, not just to the ISNULL method
I have a doubt here. Say I have a query which after applying SARG conditions, gives me a resultset of 30 rows. Sample data in the table looks like this…
Col1 col2
—- —-
a b
NULL c
d NULL First let’s take the case of ISNULL / COALESCE.
select * from tabXXX
where isnull(col1,”) = coalesce(@some_val,col1,”)
and isnull(col2,”) = coalesce(@some_val2,col2,”)
Here, the ISNULL or COALESCE queries won’t count towards the SARG conditions as we have a funtion on the column. Now the engine will start applying the functions on the column values obtained and will find if any of the rows matches the criteria required in the query. This means each and every row out of 30 rows will be looked for and evaluated once. Secondly if I had written the query using Chappy’s suggestion i.e. evaluating the query using comination of NULL and OR coditions.
select * from tabXXX
where ((@some_val is null) and (col1 is null)) OR (@some_val=col1)
and ((@some_val2 is null) and (col2 is null)) OR (@some_val2=col2)
In this case, the columns can be a part of the SARG conditions as we are directly evaluating their values. But since there is an OR present in the query, the query will have to again search the table for matching records. So the difference in the above methods will be – in the first case i’ll have less SARG conditions and I have to process data. In the second case I’ll have more SARG conditions but will have to look for records atlease twice (depending on the number of OR conditions) in the same column. Which one do you think will be fast? My vote goes to former method. Gaurav
Hmm, but the whole purpose of comparing the passed parameter to NULL was to allow it to be non present, while still allowing the same execution plan to be used for any combination of parameters. So my query never used ((@some_val is NULL) and (col1 IS NULL)) OR (@some_val = col1) but used ((@some_val IS NULL) OR (@some_val = col1)) Here the parser can decide before execution that the criteria can be eliminated purely based on whether @some_val IS NULL or not.
I think you are right. Thanks for correcting me. Gaurav
I missed a few posts this weekend, so I am going to respond to the ISNULL() vs. COALESCE() debate. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />I seem to remember hearing somewhere that ISNULL() is faster than COALESCE(), however ISNULL() is not ANSI standard, and COALESCE() is. Also, ISNULL() can only accept 2 parameters, while COALESCE() can accept quite a few. For me the fact that ISNULL() is not ANSI SQL standard is enough to steer me away from it.<br /><br />As far as the nullable columns go, Chappy’s solution seems like the best answer to me at this time. You could do something like this:<br /><br />WHERE COALESCE(Field1, ‘stringnotinfield’) = COALESCE(@var1, Field1, ‘stringnotinfield’)<br /><br />The problems are, first, it just looks stupid <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> It could also become a real maintenance problem in the future. Additionally, the query will suffer the same index and query plan caching problems that all dynamic WHERE clauses of this nature are bound to.<br /><br />So it looks like, until I can find a better way, Chappy’s solution is the best for nullable columns.
Hi All, Thanks for the useful tips. The Negative’s COALESCE solution works fine for columns where you cannot have NULLs. I also agree that using functions on both sides of the SARG isn’t necessarily good from a query optimization standpoint. Chappy’s original post
SELECT *
FROM myTable
WHERE
((field1crit IS NULL) or (field1 = @field1crit)) and
((@field2crit IS NULL) or (field2 = @field2crit)) and
((@field3crit IS NULL) or (field3 = @field3crit)) is the solution I think I’ll go with. It has worked well in prototyping, and I’m getting ready to run it against a 1M record set. Thanks for all the help. Cheers, Buddy
quote:((@some_val IS NULL) OR (@some_val = col1))
I’d go with Chappy here. It seems to me that since these 2 conditions are in OR, and SQL Server is evaluating them from left to right (am I wrong here?) this should be a better way. When @some_val IS NULL, it should exit the condition without trying to evaluate the column against the variable. Bambola.
I decided to test this isnull vs coalesce:
print ‘Testing isnull vs coalesce’
declare @one varchar(50), @two varchar(50), @i int, @test varchar(50), @start datetime
set @two = ‘test’ select @start = getdate(), @i = 1
while @i<1000000
begin
select @test = isnull(@one,@two)
set @i = @i + 1
end print ‘isnull ‘ + cast(datediff(ss,@start, getdate()) as varchar) select @start = getdate(), @i = 1
while @i<1000000
begin
select @test = coalesce(@one,@two)
set @i = @i + 1
end print ‘coalesce ‘ + cast(datediff(ss,@start, getdate()) as varchar) try it yourselves. On SQL 2k isnull is consistently quicker by about 10%
Im also building and search engine but i have a slightly diffrent situation.<br /><br />If you look at the search form for this forum you see that you can choose to just search thru subject all the whole thread. I have a simular thing on my site.<br /><br />Right now im buldning dynamic sql and have a IF statement like ..<br /><br />SET @SQLString = @SQLString + N’AND (tTopic.subject LIKE @insSearchString OR tTopic.content LIKE @insSearchString ‘ + CHAR(13) <br />IF @searchinwholethread = 1<br /> SET @SQLString = @SQLString + N’OR tMessage.content LIKE @insSearchString)’ + CHAR(13) <br />ELSE<br /> SET @SQLString = @SQLString + N’)’ + CHAR(13)<br /><br /><br />I have read the discussion here but i dont know if i can use your approach (IS NULL) in this situation. I dont know how to explain it more but if anyone understand and have any tip please respond <img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />
I found these threads from google.. very interesting points here! I’m going to ressurect this issue by posting some interesting test results (non-scientific). I made two stored procedures in the Northwind database. One of them is an almost exact copy from Guarev’s compiled sql statement to search orders with variable criteria. The other is the functionally equivalent query using a dynamically built sql string. Both, are posted below (sorry for the long text). I tested the execution of these sp’s in two different ways… one test was run in query analyzer using a simple method like keithhenry’s. The other used ADO.NET to call the procedure from a command line app. Each test called each procedure 500 times with NULL submitted for ALL parameters (this is the default search scenario). The results were surprising… Dynamic SP time: 29.2595472329584
Compiled SP time: 37.7572685913992
——- End of Test ——-
Dynamic SP time: 29.6695255961302
Compiled SP time: 37.6073118231507
——- End of Test ——-
Dynamic SP time: 29.9760880223604
Compiled SP time: 37.5123112015633 I let it go for a while, and tried changing the param values and such… but nothing really changed the consistency of the results. The dynamical sql version of the SP actually performs slightly better. I do not understand why. Without questioning my testing methods (which are very brute force and straight-forward), does anyone have an idea why? I assumed that the ISNULL() method would be faster since it doesn’t need to recompile every time… As a side note, trying the same sort of tests that use optional paramaters that need to use "LIKE" comparisons yielded MUCH more drastic results… the dynamic sql procedure was about 4 times faster than the ISNULL() method, I’m assuming because LIKE must have to compare to NULL values, forcing the filter to execute with the rest of the statement. Any thoughts would be appreciated.. ——————————————————–
Compiled SQL: CREATE PROCEDURE SEARCH_ORDER
@OrderID int = NULL,
@CustomerID nchar(10) = NULL,
@EmployeeID int = NULL,
@OrderDate datetime = NULL,
@RequiredDate datetime = NULL,
@ShippedDate datetime = NULL,
@ShipVia int = NULL,
@Freight money = NULL,
@ShipName nvarchar(80) = NULL,
@ShipAddress nvarchar(120) = NULL,
@ShipCity nvarchar(30) = NULL,
@ShipRegion nvarchar(30) = NULL,
@ShipPostalCode nvarchar(20) = NULL,
@ShipCountry nvarchar(30) = NULL,
@ProductID int = NULL,
@UnitPrice money = NULL,
@Quantity smallint = NULL,
@Discount real = NULL
AS SELECT
Orders.CustomerID, Orders.EmployeeID, Orders.Freight, Orders.OrderDate, Orders.OrderID,
Orders.RequiredDate, Orders.ShipAddress, Orders.ShipCity, Orders.ShipCountry, Orders.ShipName,
Orders.ShippedDate, Orders.ShipPostalCode, Orders.ShipRegion, Orders.ShipVia,[Order Details].Discount,
[Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice
FROM Orders
JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.CustomerID = ISNULL(@CustomerID, Orders.CustomerID)
AND Orders.EmployeeID = ISNULL(@EmployeeID, Orders.EmployeeID)
AND Orders.Freight = ISNULL(@Freight, Orders.Freight)
AND Orders.OrderDate = ISNULL(@OrderDate, Orders.OrderDate)
AND Orders.OrderID = ISNULL(@OrderID, Orders.OrderID)
AND Orders.RequiredDate = ISNULL(@RequiredDate, Orders.RequiredDate)
AND Orders.ShipAddress = ISNULL(@ShipAddress, Orders.ShipAddress)
AND Orders.ShipCity = ISNULL(@ShipCity, Orders.ShipCity)
AND Orders.ShipCountry = ISNULL(@ShipCountry, Orders.ShipCountry)
AND Orders.ShipName = ISNULL(@ShipName, Orders.ShipName)
AND ISNULL(Orders.ShippedDate, ”) = ISNULL(ISNULL(@ShippedDate, Orders.ShippedDate),”)
AND ISNULL(Orders.ShipPostalCode, ”) = ISNULL(ISNULL(@ShipPostalCode, Orders.ShipPostalCode),”)
AND ISNULL(Orders.ShipRegion, ”) = ISNULL(ISNULL(@ShipRegion, Orders.ShipRegion),”)
AND Orders.ShipVia = ISNULL(@ShipVia, Orders.ShipVia)
AND [Order Details].Discount = ISNULL(@Discount, [Order Details].Discount)
AND [Order Details].ProductID = ISNULL(@ProductID, [Order Details].ProductID)
AND [Order Details].Quantity = ISNULL(@Quantity, [Order Details].Quantity)
AND [Order Details].UnitPrice = ISNULL(@UnitPrice, [Order Details].UnitPrice)
————————————————————-
Dynamic SQL:
CREATE PROCEDURE SEARCH_ORDER2
(
@OrderID int = NULL,
@CustomerID nchar(10) = NULL,
@EmployeeID int = NULL,
@OrderDate datetime = NULL,
@RequiredDate datetime = NULL,
@ShippedDate datetime = NULL,
@ShipVia int = NULL,
@Freight money = NULL,
@ShipName nvarchar(80) = NULL,
@ShipAddress nvarchar(120) = NULL,
@ShipCity nvarchar(30) = NULL,
@ShipRegion nvarchar(30) = NULL,
@ShipPostalCode nvarchar(20) = NULL,
@ShipCountry nvarchar(30) = NULL,
@ProductID int = NULL,
@UnitPrice money = NULL,
@Quantity smallint = NULL,
@Discount real = NULL
)
AS DECLARE @sql varchar(1000) SET @sql = ”
SET @sql = @sql + ‘SELECT Orders.CustomerID, Orders.EmployeeID, Orders.Freight, ‘
SET @sql = @sql + ‘Orders.OrderDate, Orders.OrderID, Orders.RequiredDate, ‘
SET @sql = @sql + ‘Orders.ShipAddress, Orders.ShipCity, Orders.ShipCountry, ‘
SET @sql = @sql + ‘Orders.ShipName, Orders.ShippedDate, Orders.ShipPostalCode, ‘
SET @sql = @sql + ‘Orders.ShipRegion, Orders.ShipVia,[Order Details].Discount, ‘
SET @sql = @sql + ‘[Order Details].ProductID, [Order Details].Quantity, ‘
SET @sql = @sql + ‘[Order Details].UnitPrice ‘
SET @sql = @sql + ‘FROM Orders ‘
SET @sql = @sql + ‘JOIN [Order Details] ‘
SET @sql = @sql + ‘ON Orders.OrderID = [Order Details].OrderID ‘
SET @sql = @sql + ‘WHERE 0=0 ‘
If @CustomerID IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.CustomerID = ‘ + @CustomerID
END
If @EmployeeID IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.EmployeeID = ‘ + @EmployeeID
END
If @Freight IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.Freight = ‘ + CAST(@Freight as varchar)
END
If @OrderDate IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.OrderDate = ‘ + @OrderDate
END
If @OrderID IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.OrderID = ‘ + @OrderID
END
If @RequiredDate IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.RequiredDate = ‘ + @RequiredDate
END
If @ShipAddress IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipAddress = ‘ + @ShipAddress
END
If @ShipCity IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipCity = ‘ + @ShipCity
END
If @ShipCountry IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipCountry = ‘ + @ShipCountry
END
If @ShipName IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipName = ‘ + @ShipName
END
If @ShippedDate IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShippedDate = ‘ + @ShippedDate
END
If @ShipPostalCode IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipPostalCode = ‘ + @ShipPostalCode
END
If @ShipRegion IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipRegion = ‘ + @ShipRegion
END
If @ShipVia IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND Orders.ShipVia = ‘ + @ShipVia
END
If @Discount IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND [Order Details].Discount = ‘ + @Discount
END
If @ProductID IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND [Order Details].ProductID = ‘ + @ProductID
END
If @Quantity IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND [Order Details].Quantity = ‘ + @Quantity
END
If @UnitPrice IS NOT NULL
BEGIN
SET @sql = @sql + ‘AND [Order Details].UnitPrice = ‘ + CAST(@UnitPrice as varchar)
END EXEC(@sql)

What happens if you pass some search criteria? I belive the difference will be there because if no data is passed, the dynamic query does not have to validate any fields but the compiled one will have to validate all the fields. However as more no. of fields are being passed to the query, the compiled option should execute faster. I must appreciate your response to the results. Thanks, Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

If I’m going to send paramaters, it helps if the dynamic sql query is actually set up to take strings <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> .. sorry about that.. revised dynamic sql procedure is below. (This does not affect previous tests since those parts of the statement weren’t even executed anyway).<br /><br /><br />Here are the results when adding an example param ans running 1000 times since it’s a smaller result set:<br /> @CustomerID = "Quick"<br /><br />Dynamic SP time: 4.51463104947696 – 86 Records Returned<br />Compiled SP time: 4.92337741249237 – 86 Records Returned<br />——- End of Test ——-<br />Dynamic SP time: 4.51991803427531 – 86 Records Returned<br />Compiled SP time: 4.92313771722384 – 86 Records Returned<br />——- End of Test ——-<br />Dynamic SP time: 4.55333568931247 – 86 Records Returned<br />Compiled SP time: 4.93624133793541 – 86 Records Returned<br />——- End of Test ——-<br />Dynamic SP time: 4.62181785673878 – 86 Records Returned<br />Compiled SP time: 4.92607552077149 – 86 Records Returned<br />——- End of Test ——-<br /><br /><br /> @ShippedDate = "3/19/1998"<br /><br />Dynamic SP time: 1.56193706183328 – 11 Records Returned<br />Compiled SP time: 6.43341514075113 – 11 Records Returned<br />——- End of Test ——-<br />Dynamic SP time: 1.59054991626031 – 11 Records Returned<br />Compiled SP time: 6.45543078799121 – 11 Records Returned<br />——- End of Test ——-<br />Dynamic SP time: 1.59231634188144 – 11 Records Returned<br />Compiled SP time: 6.51422457323487 – 11 Records Returned<br />——- End of Test ——-<br />Dynamic SP time: 1.60249892095224 – 11 Records Returned<br />Compiled SP time: 6.48869758586636 – 11 Records Returned<br />——- End of Test ——-<br /><br />rather surprising one there, with the date.<br /><br /><br />———————————————————–<br /><br />Revised dynamic sql:<br /><br /><font size="1"><br />CREATE PROCEDURE SEARCH_ORDER2<br />(<br />@OrderID int = NULL,<br />@CustomerID nchar(10) = NULL,<br />@EmployeeID int = NULL,<br />@OrderDate datetime = NULL,<br />@RequiredDate datetime = NULL,<br />@ShippedDate datetime = NULL,<br />@ShipVia int = NULL,<br />@Freight money = NULL,<br />@ShipName nvarchar(80) = NULL,<br />@ShipAddress nvarchar(120) = NULL,<br />@ShipCity nvarchar(30) = NULL,<br />@ShipRegion nvarchar(30) = NULL,<br />@ShipPostalCode nvarchar(20) = NULL,<br />@ShipCountry nvarchar(30) = NULL,<br />@ProductID int = NULL,<br />@UnitPrice money = NULL,<br />@Quantity smallint = NULL,<br />@Discount real = NULL<br />)<br />AS<br /><br />DECLARE @sql varchar(1000)<br /><br />SET @sql = ”<br />SET @sql = @sql + ‘SELECT Orders.CustomerID, Orders.EmployeeID, Orders.Freight, ‘<br />SET @sql = @sql + ‘Orders.OrderDate, Orders.OrderID, Orders.RequiredDate, ‘<br />SET @sql = @sql + ‘Orders.ShipAddress, Orders.ShipCity, Orders.ShipCountry, ‘<br />SET @sql = @sql + ‘Orders.ShipName, Orders.ShippedDate, Orders.ShipPostalCode, ‘<br />SET @sql = @sql + ‘Orders.ShipRegion, Orders.ShipVia,[Order Details].Discount, ‘<br />SET @sql = @sql + ‘[Order Details].ProductID, [Order Details].Quantity, ‘<br />SET @sql = @sql + ‘[Order Details].UnitPrice ‘<br />SET @sql = @sql + ‘FROM Orders ‘<br />SET @sql = @sql + ‘JOIN [Order Details] ‘<br />SET @sql = @sql + ‘ON Orders.OrderID = [Order Details].OrderID ‘<br />SET @sql = @sql + ‘WHERE 0=0 ‘<br />If @CustomerID IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.CustomerID = ‘ + ”” + CAST(@CustomerID AS varchar) + ”” <br />END <br />If @EmployeeID IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.EmployeeID = ‘ + ”” + CAST(@EmployeeID AS varchar) + ”” <br />END <br />If @Freight IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.Freight = ‘ + ”” + CAST(@Freight AS varchar) + ”” <br />END <br />If @OrderDate IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.OrderDate = ‘ + ”” + CAST(@OrderDate AS varchar) + ”” <br />END <br />If @OrderID IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.OrderID = ‘ + ”” + CAST(@OrderID AS varchar) + ”” <br />END <br />If @RequiredDate IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.RequiredDate = ‘ + ”” + CAST(@RequiredDate AS varchar) + ”” <br />END <br />If @ShipAddress IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipAddress = ‘ + ”” + CAST(@ShipAddress AS varchar) + ”” <br />END <br />If @ShipCity IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipCity = ‘ + ”” + CAST(@ShipCity AS varchar) + ”” <br />END <br />If @ShipCountry IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipCountry = ‘ + ”” + CAST(@ShipCountry AS varchar) + ”” <br />END <br />If @ShipName IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipName = ‘ + ”” + CAST(@ShipName AS varchar) + ”” <br />END <br />If @ShippedDate IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShippedDate = ‘ + ”” + CAST(@ShippedDate AS varchar) + ”” <br />END <br />If @ShipPostalCode IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipPostalCode = ‘ + ”” + CAST(@ShipPostalCode AS varchar) + ”” <br />END <br />If @ShipRegion IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipRegion = ‘ + ”” + CAST(@ShipRegion AS varchar) + ”” <br />END <br />If @ShipVia IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND Orders.ShipVia = ‘ + ”” + CAST(@ShipVia AS varchar) + ”” <br />END <br />If @Discount IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND [Order Details].Discount = ‘ + ”” + CAST(@Discount AS varchar) + ”” <br />END <br />If @ProductID IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND [Order Details].ProductID = ‘ + ”” + CAST(@ProductID AS varchar) + ”” <br />END <br />If @Quantity IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND [Order Details].Quantity = ‘ + ”” + CAST(@Quantity AS varchar) + ”” <br />END <br />If @UnitPrice IS NOT NULL<br />BEGIN<br />SET @sql = @sql + ‘AND [Order Details].UnitPrice = ‘ + ”” + CAST(@UnitPrice AS varchar) + ”” <br />END <br /><br />EXEC(@sql)<br />GO<br /><br /></font id="size1">
BTW, I’ll be happy to post the complete SQL and .NET code I’m using to test these things if anyone wants to see it. I’ll wait for a request before posting all that junk, though.
Will you please try increasing the no. of passed fields. I just want to understand the theory about dynamic sql is correct. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

in both cases you’ll need to change the parameter values passed for each execution in the loop… If you don’t then the dynamic SQL has an unfair advantage since it will only be compiled once, and this would not mimic reality Cheers
Twan
Next I’m going to try setting up an array of possible northwind values for some of the parameters and have the .net program randomly pick from that list… no doubt this will change the results, but I’m not sure how well it actually will mimic real life results for all situations… most forms that I’ve built for searching are done for corporate intranets. We find that most of the time the search criteria is similar.. repeated searches using the exact same query are very common. The problem with adding many more criteria filters for THIS sample query is that the northwind database doesn’t have enough data to make it worthwhile. As shown in the previous tests, filtering by just one field trimmed the recordset down to the 80’s and teens. It’ll be tough to find a variable list of combinations for this dataset that actually return a significant number of results. I could fake it and add more data, but it’s going beyond the scope of what I wanted to show. I STILL think that the compiled procedure should be faster straight up. It’s being called over and over just like the dynamic sql, isn’t it? Why would the server remember an sp that executed a dynamic sql statement better than another? They’re both executing the same exact query. They’re both comparing against null, they’re both executing the same statement.. one is compiled and one isn’t. I would assume that they’d both cache the query plans the same way, and that the pre-compiled version should be able to resolve that faster. If someone could help me understand this I’d be greatful.
A dynamic SQL if passes the same parameters over and over again (meand the text is not changed) will use the cached plan as SQL Server cached the plan for Dynamic Queries as well. The compiled stored procedure is not executing the same query. The compiled procedure is comparing values. The where conditions are missing in the dynamic version. Why don’t you try this way… Modify the dynamic query so that it generates the same query as the compiled one even when paramters are not present. Let’s see what do we get. Also if possible generate the execution plan for both the original queries and post the same in forum. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>