SQL Server Performance Forum – Threads Archive

Stored Procedure Performance (4 questions)

I have some questions to make… I hope you can help me.
1. How can I reduce the lines of this SP? I have two similar querys… one gives the data between 2 dates. 2. What’s wrong with between statement? whats is the format that I need to put?! If I put M_Data BETWEEN ‘2004-07-01’ AND ‘2004-07-30’ the SP return correct data! 3. Do you think is a good alternative using variavel opt?! 4. How is the best way to retur the AVG and SUM? THANKS FOR THE ATTENTION!
**************** CODE *********************
ALTER PROCEDURE spMovimentoTeste
@Opt int,
@C_ID int,
@M_Descricao nvarchar(50),
@BeginDate as char(10),
@EndDate as char(10),
@SaldoMedio as int OUTPUT
As /* Filtrando por Conta, Pesquisa de Descricao */
IF @Opt=1
BEGIN
SELECT M_ID, M_Data, M_Doc, M_Descricao, M_Debito, M_Credito, M_Saldo
FROM Movimento, Conta
WHERE M_C_ID=C_ID AND [email protected]_ID AND M_Descricao LIKE @M_Descricao + ‘%’
ORDER BY M_Data
END /* Filtrando por Conta, Pesquisa de Descricao e entre duas datas */
IF @Opt=2
BEGIN
SELECT M_ID, M_Data, M_Doc, M_Descricao, M_Debito, M_Credito, M_Saldo
FROM Movimento, Conta
WHERE M_C_ID=C_ID AND [email protected]_ID AND M_Descricao LIKE @M_Descricao + ‘%’
AND M_Data BETWEEN ‘@Begindate’ AND ‘@EndDate’
END /* Calcular Saldo Medio da pesquisa definida */
SELECT @SaldoMedio=AVG(M_Saldo)
FROM Movimento, Conta
WHERE M_C_ID=C_ID AND [email protected]_ID AND M_Descricao LIKE @M_Descricao + ‘%’
/* set nocount on */
RETURN ******************************************************** Pedro Perfeito
MCSD
Portugal

Alright, let me try to give this a shot. 1. How can I reduce the lines of this SP? I have two similar querys… one gives the data between 2 dates. –You don’t need to worry about reducing the lines, so much as breaking this up into smaller queries. Many times people try to combine code like this in SQL. It’s a bad idea. This causes recompiles when it hits your IF statements. You need to do the IF logic in the application, or have a wrapper procedure that will contain the IF logic and just call one of two other procedures. The AVG and SUM should be another procedure also. 2. What’s wrong with between statement? whats is the format that I need to put?! If I put M_Data BETWEEN ‘2004-07-01’ AND ‘2004-07-30’ the SP return correct data! –If you are going to do your dates like this, you need to make sure that it’s between 2004-07-01 00:00:00.000 and 2004-07-30 11:59:59.999. You can search for between in this forum for examples. 3. Do you think is a good alternative using variavel opt?! I’m assuming you mean your IF logic. The answer is no, unless you call other stored procedures to get the results after the IF statements. 4. How is the best way to retur the AVG and SUM? –Seperate procedures. For the sake of your applications, try to limit the return recordsets to one per procedure. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi Derrick, just picking up on >> You don’t need to worry about reducing the lines, so much as breaking this up into smaller queries. Many times people try to combine code like this in SQL. It’s a bad idea. This causes recompiles when it hits your IF statements. You need to do the IF logic in the application, or have a wrapper procedure that will contain the IF logic and just call one of two other procedures. The AVG and SUM should be another procedure also. << I don’t think this is quite true… The if statements won’t cause recompiles, but it may result in less than optimal execution plans depending on the parameters passed. So the end recommendation is the same, i.e. be wary of combining this sort of thing in sql. Cheers
Twan

Try it. It will cause a recompile in this case because it’s running two completely different things. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi Derrick, I have tried this in the past and have never seen a recompile…? At compile time SQL isn’t smart enough to only compile the bits of the proc that might be followed based on the params, however it is smart enough to compile the sql statements based on the params, but that doesn’t cause any recompile…? Cheers
Twan

I was wrong on this one. It’s always good to run tests and verify things. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> In this case, it’s actually using a COND test. It compiles the query with both options in the query. I created the following test query: <pre> – DROP PROCEDURE nw_OrderSummary – CREATE PROCEDURE nw_OrderSummary – – @Opt int, – @BeginDate DATETIME, – @EndDate DATETIME – – AS – – IF @Opt = 1 – BEGIN – SELECT – c.CategoryName, – c.Description, – p.ProductName, – p.SupplierID, – p.Discontinued – FROM – Categories c – INNER JOIN Products p ON c.CategoryID = p.CategoryID – INNER JOIN [Order Details] od ON p.ProductID = od.ProductID – INNER JOIN Orders o ON od.OrderID = :confused:

rderID – END – – IF @Opt = 2 – BEGIN – SELECT – c.CategoryName, – c.Description, – p.ProductName, – p.SupplierID, – p.Discontinued – FROM – Categories c – INNER JOIN Products p ON c.CategoryID = p.CategoryID – INNER JOIN [Order Detals] od ON p.ProductID = od.ProductID – INNER JOIN Orders o ON od.OrderID = :confused:

rderID – WHERE – :confused:

rderDate BETWEEN @BeginDate AND @EndDate – END – SET STATISTICS PROFILE ON EXEC nw_OrderSummary @Opt = 1, @BeginDate = ’01/01/03′, @EndDate = ’04/04/04′ EXEC nw_OrderSummary @Opt = 2, @BeginDate = ’01/01/75′, @EndDate = ’04/04/04′ SET STATISTICS PROFILE OFF </pre> Here were the execution plan results; and there were no recompiles in Profiler. First Execution: <pre> SELECT c.CategoryName, c.Description, p.ProductName, p.SupplierID, p.Discontinued FROM Categories c INNER JOIN Products p ON c.CategoryID = p.CategoryID INNER JOIN [Order Details] od ON p.ProductID = od.ProductID INNER JOIN Orders o ON od.OrderID = :confused:

rderID |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[c].[Description]=[c].[Description])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[c].[CategoryID])=([p].[CategoryID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[c].[CategoryID]=[p].[CategoryID])) |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Categories].[PK_Categories] AS [c])) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[o].[OrderID])=([od].[OrderID])) |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Orders].[ShippersOrders] AS [o])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[od].[ProductID]) WITH PREFETCH) |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Order Details].[ProductsOrder_Details] AS [od])) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Products].[PK_Products] AS [p]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[p].[ProductID]=[od].[ProductID]) ORDERED FORWARD) </pre> Second Execution: <pre> SELECT c.CategoryName, c.Description, p.ProductName, p.SupplierID, p.Discontinued FROM Categories c INNER JOIN Products p ON c.CategoryID = p.CategoryID INNER JOIN [Order Details] od ON p.ProductID = od.ProductID INNER JOIN Orders o ON od.OrderID = :confused:

rderID WHERE :confused:

rderDate BETWEEN @BeginDate AND @EndDate |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[c].[Description]=[c].[Description])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[p].[CategoryID])) |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[od].[ProductID])) | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[o].[OrderID])) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Orders].[OrderDate] AS [o]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[o].[OrderDate] >= [@BeginDate] AND [o].[OrderDate] <= [@EndDate]) ORDERED FORWARD) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Order Details].[OrderID] AS [od]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[od].[OrderID]=[o].[OrderID]) ORDERED FORWARD) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion -6.gif’ alt=’

‘ />[Northwind].[dbo].[Products].[PK_Products] AS [p]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[p].[ProductID]=[od].[ProductID]) ORDERED FORWARD) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Northwind].[dbo].[Categories].[PK_Categories] AS [c]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[c].[CategoryID]=[p].[CategoryID]) ORDERED FORWARD) </pre> What this doesn’t show is the COND statements in the graphical execution plan. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

thanks for checking it out Derrick Cheers
Twan

Hi pedritopm, IF @Opt=2
BEGIN
SELECT M_ID, M_Data, M_Doc, M_Descricao, M_Debito, M_Credito, M_Saldo
FROM Movimento, Conta
WHERE M_C_ID=C_ID AND [email protected]_ID AND M_Descricao LIKE @M_Descricao + ‘%’
AND M_Data BETWEEN ‘@Begindate’ AND ‘@EndDate’
END for statement above, @Begindate and @Begindate should be variable and should not use single quote for it. " AND M_Data BETWEEN ‘@Begindate’ AND ‘@EndDate’ " this statement is compare M_Data to string value ‘@Begindate’ and ‘@EndDate’

if you really wana reduce the number of lines
try this — DROP PROCEDURE nw_OrderSummary
— CREATE PROCEDURE nw_OrderSummary — @Opt int,
— @BeginDate DATETIME,
— @EndDate DATETIME — AS
— SELECT c.CategoryName,
— c.Description,
— p.ProductName,
— p.SupplierID,
— p.Discontinued
— FROM Categories c
— INNER JOIN Products p ON c.CategoryID = p.CategoryID
— INNER JOIN [Order Details] od ON p.ProductID = od.ProductID
— INNER JOIN Orders o ON od.OrderID = :confused:

rderID
— WHERE ( (@Opt = 1) or ( :confused:

rderDate BETWEEN @BeginDate AND @EndDate)) you can also use this:
— WHERE ( (@Opt = 2) and ( :confused:

rderDate BETWEEN @BeginDate AND @EndDate)) remember that T-SQL uses short-circuit evaluation.

]]>