Sql Query takes three times longer:

Question

EDIT

I have changed the stored procedure in use. This new query skips the part where it checks the "In Stock" column of results (as this is actual available easier, elsewhere) and instead only checks the "Committed" stock.

USE [PCSQL]
GO
/****** Object:  StoredProcedure [dbo].[xw_GetAvailableStockLevel]    Script Date: 05/18/2015 09:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[xw_GetAvailableStockLevel] 
    @ProductID nvarchar(128)

AS

DECLARE @TotalCommited int
SET @TotalCommited = 0

SELECT @TotalCommited = SUM(Unsup_quant)  

FROM SALESORD_LINES 

WHERE DATEDIFF(MONTH,DUEDATE,GETDATE()) < 3 

AND Location IN('4','12') AND STOCKCODE=@ProductID AND Hdr_Status < 2

SELECT isnull(@TotalCommited,0) as TotalCommited

The load times have dropped dramatically from about 40 seconds to about 8-15 seconds per page depending on the number of items.

My problem now is, this query is still 'Too Slow' for management. Do you think it's possible to streamline this query any further?

I've tried only checking records from the last three months, but I don't know how useful that will be. there are over 250,000 table rows dating back sporadically over the last 5 years.

END EDIT

I have attempted to update a small section of a website involving checking if products are in stock.

I have changed from a large function that called two SQL queries and compiled the results with extra data, to a smaller function that calls slightly altered queries that return less data... and my page load times have more than doubled?

Is my new function actually more complicated that the original somehow? How can I speed up my page loads?

Queries in question:

Original (VB.net with a call to two stored procedures)

Public Shared Function GetStockLevelsComplete(ByVal strProductCode As String) As String
        Dim strOutput As String = "<div class=""product-column-right-location"">&nbsp;</div>" & _
        "<div class=""product-column-right-stock""><strong>Stock</strong></div>" & _
        "<div class=""product-column-right-committed""><strong>Committed</strong></div>" & _
        "<div class=""product-column-right-on-order""><strong>On Order</strong></div>" & _
        "<div class=""product-column-right-available""><strong>Available</strong></div>"
        Dim dt As DataTable = New DataTable
        'Dim dt As DataTable = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetStockLevelComplete", strProductCode, 1).Tables(0)
        'If Not dt Is Nothing Then
        '    If dt.Rows.Count > 0 Then
        '        Dim dr As DataRow = dt.Rows(0)
        '        strOutput += "<tr>" & _
        '        "<td>St Asaph</td>" & _
        '        "<td>" & dr("TotalInStock") & "</td>" & _
        '        "<td>" & dr("TotalCommited") & "</td>" & _
        '        "<td>" & dr("TotalOnOrder") & "</td>" & _
        '        "<td>" & (dr("TotalInStock") - dr("TotalCommited")) & "</td>" & _
        '        "</tr>"
        '    End If
        'End If
        dt = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetStockLevelComplete", strProductCode, 4).Tables(0)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                Dim dr As DataRow = dt.Rows(0)
                strOutput += "<div class=""product-column-right-location""><a href=""/contact-us.aspx"" alt=""Global PC Tower Junction contact information"" Title=""Global PC Tower Junction contact information"">Tower Junction - Riccarton</a></div>" & _
                "<div class=""product-column-right-stock"">" & dr("TotalInStock") & "</div>" & _
                "<div class=""product-column-right-committed"">" & dr("TotalCommited") & "</div>" & _
                "<div class=""product-column-right-on-order"">" & dr("TotalOnOrder") & "</div>" & _
                "<div class=""product-column-right-available"">" & (dr("TotalInStock") - dr("TotalCommited")) & "</div>"
            End If
        End If
        dt = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetStockLevelComplete", strProductCode, 12).Tables(0)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                Dim dr As DataRow = dt.Rows(0)
                strOutput += "<div class=""product-column-right-location""><a href=""/contact-us.aspx"" alt=""Global PC Homebase contact information"" Title=""Global PC Homebase contact information"" >Homebase - Shirley</a></div>" & _
                "<div class=""product-column-right-stock"">" & dr("TotalInStock") & "</div>" & _
                "<div class=""product-column-right-committed"">" & dr("TotalCommited") & "</div>" & _
                "<div class=""product-column-right-on-order"">" & dr("TotalOnOrder") & "</div>" & _
                "<div class=""product-column-right-available"">" & (dr("TotalInStock") - dr("TotalCommited")) & "</div>"
            End If
        End If

        dt = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetStockLevelComplete", strProductCode, 10).Tables(0)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                Dim dr As DataRow = dt.Rows(0)
                strOutput += "<div class=""product-column-right-location"">In-Transit</div>" & _
                "<div class=""product-column-right-stock"">" & dr("TotalInStock") & "</div>" & _
                "<div class=""product-column-right-committed"">" & dr("TotalCommited") & "</div>" & _
                "<div class=""product-column-right-on-order"">" & dr("TotalOnOrder") & "</div>" & _
                "<div class=""product-column-right-available"">" & (dr("TotalInStock") - dr("TotalCommited")) & "</div>"
            End If
        End If

        dt = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetWarehouseStockLevelComplete", strProductCode, 11).Tables(0)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                Dim dr As DataRow = dt.Rows(0)
                strOutput += "<div class=""product-column-right-location"">Warehouse</div>" & _
                "<div class=""product-column-right-stock"">" & dr("TotalInStock") & "</div>" & _
                "<div class=""product-column-right-committed"">" & dr("TotalCommited") & "</div>" & _
                "<div class=""product-column-right-on-order"">" & dr("TotalOnOrder") & "</div>" & _
                "<div class=""product-column-right-available"">" & (dr("TotalInStock") - dr("TotalCommited")) & "</div>"
            End If
        End If
        Return strOutput

SQL query #1:

/****** Object:  StoredProcedure [dbo].[xw_GetWarehouseStockLevelComplete]    Script Date: 05/15/2015 14:32:24 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER PROCEDURE [dbo].[xw_GetWarehouseStockLevelComplete]
        @ProductID nvarchar(128),
        @Location int
    AS

        DECLARE @TotalInStock int

        SELECT  @TotalInStock = SUM(QTY)  
        FROM    STOCK_LOC_INFO 
        WHERE   Location >= 13 
            AND STOCKCODE=@ProductID

        DECLARE @TotalCommited int

        SELECT  @TotalCommited = SUM(Unsup_quant)  
        FROM    SALESORD_LINES 
        WHERE   Hdr_Status <> 2 
            AND Location >= 13 
            AND STOCKCODE=@ProductID

        DECLARE @TotalOnOrder int

        SELECT  @TotalOnOrder = SUM(BKORD_QUANT)  
        FROM    PURCHORD_LINES 
        WHERE   Location >= 13 
            AND STOCKCODE=@ProductID


        SELECT  isnull(@TotalInStock,0)  as TotalInStock, 
                isnull(@TotalCommited,0) as TotalCommited, 
                isnull(@TotalOnOrder,0)  as TotalOnOrder

SQL query #2:

/****** Object:  StoredProcedure [dbo].[xw_GetStockLevelComplete]    Script Date: 05/15/2015 14:37:19 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

    ALTER PROCEDURE [dbo].[xw_GetStockLevelComplete]
        @ProductID nvarchar(128),
        @Location int
    AS

        DECLARE @TotalInStock int

        SELECT  @TotalInStock = SUM(QTY)  
        FROM    STOCK_LOC_INFO 
        WHERE   Location = @Location 
            AND STOCKCODE=@ProductID

        DECLARE @TotalCommited int

        SELECT  @TotalCommited = SUM(Unsup_quant)  
        FROM    SALESORD_LINES 
        WHERE   Hdr_Status <> 2 
            and Hdr_Status <> 3
            AND HDR_STATUS <> 6  
            AND Location = @Location 
            AND STOCKCODE=@ProductID

        DECLARE @TotalOnOrder int

        SELECT  @TotalOnOrder = SUM(BKORD_QUANT)  
        FROM    PURCHORD_LINES 
        WHERE   Location = @Location 
            AND STOCKCODE=@ProductID


        SELECT  isnull(@TotalInStock,0)     as TotalInStock, 
                isnull(@TotalCommited,0)    as TotalCommited, 
                isnull(@TotalOnOrder,0)     as TotalOnOrder

Here is my new code:

Public Shared Function GetGalleryStockCheck(ByVal strProductCode As String) As Integer

            Dim intone As Integer = 0
            Dim inttwo As Integer = 0
            'Dim intthree As Integer = 0
            Dim dt1 As DataTable = New DataTable

            dt1 = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetAvailableStockLevel", strProductCode).Tables(0)
            If dt1.Rows.Count > 0 Then
                Dim dr1 As DataRow = dt1.Rows(0)
                intone = (dr1("TotalInStock") - dr1("TotalCommited"))
            End If
            dt1 = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetWarehouseStockLevelComplete", strProductCode, 11).Tables(0)
            If dt1.Rows.Count > 0 Then
                Dim dr1 As DataRow = dt1.Rows(0)
                inttwo = (dr1("TotalInStock") - dr1("TotalCommited"))
            End If
            'dt1 = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings("dbConn"), "xw_GetStockLevelComplete", strProductCode, 12).Tables(0)
            'If dt1.Rows.Count > 0 Then
            '    Dim dr1 As DataRow = dt1.Rows(0)
            '    intthree = (dr1("TotalInStock") - dr1("TotalCommited"))
            'End If

            Dim intAvailable = intone + inttwo

            Return intAvailable

        End Function

SQL

/****** Object:  StoredProcedure [dbo].[xw_GetAvailableStockLevel]    Script Date: 05/15/2015 14:33:19 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

    ALTER PROCEDURE [dbo].[xw_GetAvailableStockLevel] 
        @ProductID nvarchar(128)

    AS

        DECLARE @TotalInStock int

        SELECT  @TotalInStock = SUM(QTY)  
        FROM    STOCK_LOC_INFO 
        WHERE   Location IN('4','12') 
            AND STOCKCODE=@ProductID

        DECLARE @TotalCommited int

        SELECT  @TotalCommited = SUM(Unsup_quant)  
        FROM    SALESORD_LINES 
        WHERE   Hdr_Status <> 2 
            and Hdr_Status <> 3 
            AND HDR_STATUS <> 6  
            AND Location IN('4','12') 
            AND STOCKCODE=@ProductID

        DECLARE @WarehouseCommit int

        SELECT  isnull(@TotalInStock,0) as TotalInStock, 
                isnull(@TotalCommited,0) as TotalCommited

Can you tell us about things like indexes and whether there's enough memory on the system? If you don't have the right indexes and don't have enough memory, these can severely limit the performance of a query no matter how much you tweak it. What is the page life expectancy when the query is running--see http://www.brentozar.com/archive/2014/05/much-memory-sql-server-need/ — Tony Hinkle, May 20 '15 at 00:12
A SQL query cannot be optimized on the basis of us staring at some code dumps. You have to show us the Actual Query Plans (preferably by embedding or linking to the .sqlplan XML file). — RBarryYoung, May 20 '15 at 00:14
Failing that, the stuff that @TonyHinkle is asking for is the next best fallback. — RBarryYoung, May 20 '15 at 00:15
One easy way to tell if the server has enough memory to do this most efficiently is to run "SET STATISTICS ON" in SQL Server Management Studio, and then run this query. If there are a lot of physical reads, then that means it doesn't have enough memory to store the tables and indexes in memory, so it has to read from disk, which takes sooooooo much longer. — Tony Hinkle, May 20 '15 at 00:17
I've added a screenshot of the execution plan and a copy of the XML to a [Drive](https://drive.google.com/folderview?id=0B1iAdaMNelXufjJhTVhkY3U5aEx4VGJLTmVzakF6cUZIWFk1Wkd0UWhlNDI1cWlTeU1CMk0&usp=sharing) folder. I'm fairly new at this, so hopefully that 's the info you were after. — Wompguinea, May 20 '15 at 01:08
It also came back with this message: /* Missing Index Details from SQLQuery54.sql - SERVER-SQL01.PCSQL (PC\1PC1 (98)) The Query Processor estimates that implementing the following index could improve the query cost by 91.891%. */ /* USE [SQL] GO CREATE NONCLUSTERED INDEX [] ON [dbo].[SALESORD_LINES] ([LOCATION],[HDR_STATUS]) INCLUDE ([UNSUP_QUANT],[DUEDATE]) GO */ MY manager/boss/slavemaster has told me he doesn't want me messing around with indexes for some reason, but will this be a good idea? — Wompguinea, May 20 '15 at 01:10
If you cannot create any new indexes, then you should show us what indexes you already have on the table. — RBarryYoung, May 20 '15 at 14:21
It's the weirdest thing... my edit to the Stored procedure didn't do much for about 3 hours... than suddenly it speed right the hell up and is working fantastic. Speed issue must have partly been somewhere else on the network? Thanks for all the tips everyone. — Wompguinea, May 20 '15 at 22:09

score 0 · Answer 1 · edited May 23 '17 at 10:28

As others commented above, it is hard to troubleshoot these type of issues wihtout being able to see all the related info. I strongly suspect that (obvious causes such as hardware issues/limitations, index fragmentation, etc aside), the problem might be related to the following two isues:

your queries being non-sargeable. See here for a good explanation but essentially a lot of your predicates have functions etc. in them that make them non-sargable e.g.DATEDIFF(MONTH,DUEDATE,GETDATE())
Parameter sniffing. See here. Essentially, when you pass in parameters to your stored procs they get compiled and optimized for that specific parameter and can then perform horrendously for any other parameter that is different.

Sql Query takes three times longer:

1 Answers1