Everything was working fine ....

but sudently i have started seeing delay in loading of home page.

In logs i see:

procedure: bvc_Order_d line: 25 Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Exception: System.Data.SqlClient.SqlException: procedure: bvc_Order_d line: 25 Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at BVSoftware.Bvc5.Core.Datalayer.SqlDataHelper.ExecuteNonQuery(DataRequest request)

I have narrowed it down to Top10 products widget.
If I remove everything works fine.

also seeing:


Cannot find table 0.[ at System.Data.DataTableCollection.get_Item(Int32 index) at BVSoftware.Bvc5.Core.Catalog.InternalProduct.Mapper.FindTotalProductsOrdered(DateTime startDate, DateTime endDate) at BVSoftware.Bvc5.Core.Catalog.InternalProduct.FindTotalProductsOrdered(DateTime startDate, DateTime endDate) at BVModules_ContentBlocks_Top_10_Products_view.LoadProducts() in D:\Inetpub\wwwroot\domains\officialticketcenter\BVModules\ContentBlocks\Top 10 Products\view.ascx.vb:line 16 at BVModules_ContentBlocks_Top_10_Products_view.Page_Load(Object sender, EventArgs e) in D:\Inetpub\wwwroot\domains\officialticketcenter\BVModules\ContentBlocks\Top 10 Products\view.ascx.vb:line 9 at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) ]


procedure: bvc_Order_ByCriteria_s line: 31 Transaction (Process ID 61) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Exception: System.Data.SqlClient.SqlException: procedure: bvc_Order_ByCriteria_s line: 31 Transaction (Process ID 61) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.NextResult() at System.Data.ProviderBase.DataReaderContainer.NextResult() at System.Data.Common.DataAdapter.FillNextResult(DataReaderContainer dataReader) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at BVSoftware.Bvc5.Core.Datalayer.SqlDataHelper.ExecuteDataSet(DataRequest request, Int32 startRowIndex, Int32 maximumRows, Int32& rowCount)

yes. i didnt see deadlocks so far but i only turned it on for short while as the delay is huge.

Default parameters for sp are

bvc_Product_ProductsOrderedCount_s procedure '1900-01-01 00:00:00.000' and '3000-12-31 00:00:00.000'

this query in Profiler shows that it takes average of 5-6 seconds alone causing delay on page load.

I am considering making it

bvc_Product_ProductsOrderedCount_s procedure getdate() -100 getdate()

This is what i have done.


Create a new table to store top 10 products.

Create a new Store Procedure to populate above

Update existing Store Procedure to select records instead of query from above table

Create a nightly job to execute sp to populate table with top 10 products.




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bvc_TopTenProducts](
[bvin] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Total Ordered] [decimal](38, 10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


create procedure bvc_PopulateTopTenProducts as
begin
truncate table bvc_TopTenProducts;
insert into bvc_TopTenProducts
SELECT TOP 10
p.bvin,
p.ProductName,
SUM(l.Quantity) AS "Total Ordered"
FROM
bvc_LineItem l
JOIN bvc_Product p ON l.productID = p.bvin
JOIN bvc_Order ord ON ord.bvin = l.OrderBvin
WHERE
IsPlaced = 1
AND dbo.bvc_ProductAvailableAndActive(ProductID, 0) = 1
GROUP BY p.bvin, p.ProductName
ORDER BY SUM(l.Quantity) DESC;
end;
go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[bvc_Product_ProductsOrderedCount_s]

@StartDate datetime = NULL,
@EndDate datetime = NULL,
@MaxRows bigint = 1000
AS
BEGIN TRY
select * from bvc_TopTenProducts
RETURN
END TRY
BEGIN CATCH
EXEC bvc_EventLog_SQL_i
END CATCH
go