sql server - Sql Query takes three times longer: -
edit
i have changed stored procedure in use. new query skips part checks "in stock" column of results (as actual available easier, elsewhere) , instead checks "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) declare @totalcommited int set @totalcommited = 0 select @totalcommited = sum(unsup_quant) salesord_lines datediff(month,duedate,getdate()) < 3 , location in('4','12') , stockcode=@productid , hdr_status < 2 select isnull(@totalcommited,0) totalcommited the load times have dropped dramatically 40 seconds 8-15 seconds per page depending on number of items.
my problem is, query still 'too slow' management. think it's possible streamline query further?
i've tried checking records last 3 months, don't know how useful be. there on 250,000 table rows dating sporadically on last 5 years.
end edit
i have attempted update small section of website involving checking if products in stock.
i have changed large function called 2 sql queries , compiled results data, smaller function calls altered queries return less data... , page load times have more doubled?
is new function more complicated original somehow? how can speed page loads?
queries in question:
original (vb.net call 2 stored procedures)
public shared function getstocklevelscomplete(byval strproductcode string) string dim stroutput string = "<div class=""product-column-right-location""> </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 datatable = new datatable 'dim dt datatable = sqlhelper.executedataset(system.configuration.configurationmanager.appsettings("dbconn"), "xw_getstocklevelcomplete", strproductcode, 1).tables(0) 'if not dt nothing ' if dt.rows.count > 0 ' dim dr 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 nothing if dt.rows.count > 0 dim dr 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 nothing if dt.rows.count > 0 dim dr 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 nothing if dt.rows.count > 0 dim dr 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 nothing if dt.rows.count > 0 dim dr 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 declare @totalinstock int select @totalinstock = sum(qty) stock_loc_info location >= 13 , stockcode=@productid declare @totalcommited int select @totalcommited = sum(unsup_quant) salesord_lines hdr_status <> 2 , location >= 13 , stockcode=@productid declare @totalonorder int select @totalonorder = sum(bkord_quant) purchord_lines location >= 13 , stockcode=@productid select isnull(@totalinstock,0) totalinstock, isnull(@totalcommited,0) totalcommited, isnull(@totalonorder,0) 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 declare @totalinstock int select @totalinstock = sum(qty) stock_loc_info location = @location , stockcode=@productid declare @totalcommited int select @totalcommited = sum(unsup_quant) salesord_lines hdr_status <> 2 , hdr_status <> 3 , hdr_status <> 6 , location = @location , stockcode=@productid declare @totalonorder int select @totalonorder = sum(bkord_quant) purchord_lines location = @location , stockcode=@productid select isnull(@totalinstock,0) totalinstock, isnull(@totalcommited,0) totalcommited, isnull(@totalonorder,0) totalonorder here new code:
public shared function getgallerystockcheck(byval strproductcode string) integer dim intone integer = 0 dim inttwo integer = 0 'dim intthree integer = 0 dim dt1 datatable = new datatable dt1 = sqlhelper.executedataset(system.configuration.configurationmanager.appsettings("dbconn"), "xw_getavailablestocklevel", strproductcode).tables(0) if dt1.rows.count > 0 dim dr1 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 dim dr1 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 ' dim dr1 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) declare @totalinstock int select @totalinstock = sum(qty) stock_loc_info location in('4','12') , stockcode=@productid declare @totalcommited int select @totalcommited = sum(unsup_quant) salesord_lines hdr_status <> 2 , hdr_status <> 3 , hdr_status <> 6 , location in('4','12') , stockcode=@productid declare @warehousecommit int select isnull(@totalinstock,0) totalinstock, isnull(@totalcommited,0) totalcommited
as others commented above, hard troubleshoot these type of issues wihtout being able see related info. suspect (obvious causes such hardware issues/limitations, index fragmentation, etc aside), problem might related following 2 isues:
- your queries being non-sargeable. see here explanation lot of predicates have functions etc. in them make them non-sargable e.g.datediff(month,duedate,getdate())
- parameter sniffing. see here. essentially, when pass in parameters stored procs compiled , optimized specific parameter , can perform horrendously other parameter different.
Comments
Post a Comment