Solved

Help with a .NET/SQL Timout problem

Posted on 2009-07-02
4
157 Views
Last Modified: 2012-05-07
Hi,
   I've been wrrestling with this problem for over a week,  I am using the the query posted below to create a dataset that feeds a Crystal Report.  This query does return data in SQL Manager, although it takes about 2 minutes.  However, when I call the stored procedure in my ASP.NET app, it always times out in about 30 seconds.  I've changed the connection timeout value in my WEB.CONFIG and in SQL Server (both posted).  Doesn't make sense to me.  What can I do?  Thanks!

WEB,CONFIG
----------------

  <add name="XXX_prodSQLConnectionString" connectionString="Data Source=XXX-PC\SQLEXPRESS;Initial Catalog=OrderEntry_prodSQL;Integrated Security=True;Connection Timeout=0" providerName="System.Data.SqlClient"/>



USE [OrderEntry_prodSQL]
GO
/****** Object:  StoredProcedure [dbo].[SELECTInvoiceForm]    Script Date: 07/02/2009 13:22:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SELECTInvoiceForm]
(@InvoiceNumber int)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
SELECT DISTINCT i.Consignment,n.InvoiceNumber,n.InvoiceDate,n.ShipVia,n.CustomerNumber,n.ShipToNumber,n.BillToNumber,a.BOLNumber,a.ShipDate,e.Name, g.Name,g.Address1,g.Address2,g.City,g.State, g.ZipCode, g.Country,c.PricePerCWT,i.RodSize,i.Grade,i.Price,i.CustomerPO AS RodPO,j.Name AS VendorName, CASE WHEN f.Name != Null AND f.CustomerNumber != g.CustomerNumber THEN f.Name ELSE g.Name END AS ShipToName, CASE WHEN f.Name != Null AND f.CustomerNumber != g.CustomerNumber THEN f.Address1 ELSE g.Address1 END AS ShipToAddress1, CASE WHEN f.Name != Null AND f.CustomerNumber != g.CustomerNumber THEN f.Address2 ELSE g.Address2 END AS ShipToAddress2, CASE WHEN f.Name != Null AND f.CustomerNumber != g.CustomerNumber THEN f.City ELSE g.City END AS ShipToCity, CASE WHEN f.Name != Null AND f.CustomerNumber != g.CustomerNumber THEN f.State ELSE g.State END AS ShipToState, CASE WHEN f.Name != Null AND f.CustomerNumber != g.CustomerNumber THEN f.ZipCode ELSE g.ZipCode END AS ShipToZipCode,CASE WHEN h.Name != Null AND f.CustomerNumber != h.CustomerNumber THEN h.Name ELSE g.Name END AS BillToName, CASE WHEN h.Name != Null AND f.CustomerNumber != h.CustomerNumber THEN h.Address1 ELSE g.Address1 END AS BillToAddress1, CASE WHEN h.Name != Null AND f.CustomerNumber != h.CustomerNumber THEN h.Address2 ELSE g.Address2 END AS BillToAddress2, CASE WHEN h.Name != Null AND f.CustomerNumber != h.CustomerNumber THEN h.City ELSE g.City END AS BillToCity, CASE WHEN h.Name != Null AND f.CustomerNumber != h.CustomerNumber THEN h.State ELSE g.State END AS BillToState, CASE WHEN h.Name != Null AND f.CustomerNumber != h.CustomerNumber THEN h.ZipCode ELSE g.ZipCode END AS BillToZipCode,b.*,m.CoilNumber,m.Weight AS CoilWeight,l.CoilID,l.Weight
           --  ( SELECT DISTINCT SUM(m.Weight) AS RodWeight FROM tblCoilID m JOIN tblCoilLog l ON l.MasterCoilNumber = m.CoilNumber AND l.BOLNumber = a.BOLNumber AND l.Control = c.Control GROUP BY l.MasterCoilNumber)
             FROM tblInvoiceHeader AS n 
             JOIN tblInvoiceDetail AS o ON o.InvoiceNumber = n.InvoiceNumber
             JOIN tblBOLHeader AS a ON a.BOLNumber = o.BOLNumber
             JOIN tblBOLDetail AS b ON a.BOLNumber = b.BOLNumber 
             JOIN tblOrderDetail AS c ON c.Control = b.Control
             JOIN tblOrderHeader AS d ON d.OrderNumber = c.OrderNumber
             JOIN tblHeatTransaction AS k ON k.Control = c.Control
             JOIN tblHeat AS i ON i.ID = k.HeatID
             JOIN tblVendor AS j ON j.VendorNumber = i.VendorNumber
             LEFT JOIN tblCoilLog AS m ON a.BOLNumber = m.BOLNumber AND m.Control = c.Control
             JOIN tblCoilID AS l ON l.CoilNumber = m.MasterCoilNumber   
             LEFT JOIN tblCarrier AS e ON a.CarrierNumber=e.CarrierNumber
             LEFT JOIN tblMultipleShipToAddress AS f ON a.ShipToNumber=n.ShipToNumber
             LEFT JOIN tblCustomer AS g ON g.CustomerNumber=n.CustomerNumber
             LEFT JOIN tblMultipleBillToAddress AS h ON a.BillToNumber=n.BillToNumber
            WHERE n.InvoiceNumber = @InvoiceNumber
            GROUP BY n.InvoiceNumber,n.InvoiceDate,n.ShipVia,n.CustomerNumber,n.ShipToNumber,n.BillToNumber,a.BOLNumber,b.Control,a.ShipDate,e.Name,
            g.Name,g.Address1,g.Address2,g.City,g.State,g.ZipCode,g.Country,c.PricePerCWT,i.RodSize,i.Grade,i.Price,i.CustomerPO,
            j.Name,b.ID,b.BOLNumber,b.HeatID,b.NoOfCoils,b.Description,b.CustomerPONumber,b.PartNumber,b.NetWeight,b.TareWeight,b.SplitCoils,b.NoOfCarriers,b.HeatNumber,b.Grade,b.FinishedSize,b.BW,b.PackageDescription,b.MaterialDescription,b.ClassRate,b.HazardousMaterial,b.upsize_ts,c.Control,
            i.Consignment,l.CoilID,l.Weight,m.CoilNumber,m.Weight,
            h.CustomerNumber,f.CustomerNumber,g.CustomerNumber,f.Name,f.Address1,f.Address2,f.City,f.State,f.ZipCode,f.Country,h.Name,h.Address1,h.Address2,h.City,h.State,h.ZipCode,h.Country
            
END

Open in new window

0
Comment
Question by:jvalescu
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
Bob Hoffman earned 350 total points
ID: 24765795
set the command timeout in your connection object to 0.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24769200
>>set the command timeout in your connection object to 0.<<
And be prepared to wait indefinitely or fix the problem in the first place by optimizing the query.

>> it always times out in about 30 seconds. <<
That is because the default is 30 seconds. :)
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 24769230
On second thoughts, unless you are prepared to re-write your query and perhaps the underlying table structure you would be better of waiting a long time.

Please do not take this the wrong way, but I have very rarely seen on this site (and i have seen a few) such a poorly written query.  With a GROUP BY clause like yours I am surprised it takes only 2 minutes. Here is the Stored Procedure in a clearer format:

USE [OrderEntry_prodSQL]
GO
/****** Object:  StoredProcedure [dbo].[SELECTInvoiceForm]    Script Date: 07/02/2009 13:22:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SELECTInvoiceForm]
(@InvoiceNumber int)
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
SELECT DISTINCT
        i.Consignment,
        n.InvoiceNumber,
        n.InvoiceDate,
        n.ShipVia,
        n.CustomerNumber,
        n.ShipToNumber,
        n.BillToNumber,
        a.BOLNumber,
        a.ShipDate,
        e.Name,
        g.Name,
        g.Address1,
        g.Address2,
        g.City,
        g.State,
        g.ZipCode,
        g.Country,
        c.PricePerCWT,
        i.RodSize,
        i.Grade,
        i.Price,
        i.CustomerPO AS RodPO,
        j.Name AS VendorName,
        CASE WHEN f.Name != Null
                  AND f.CustomerNumber != g.CustomerNumber THEN f.Name
             ELSE g.Name
        END AS ShipToName,
        CASE WHEN f.Name != Null
                  AND f.CustomerNumber != g.CustomerNumber THEN f.Address1
             ELSE g.Address1
        END AS ShipToAddress1,
        CASE WHEN f.Name != Null
                  AND f.CustomerNumber != g.CustomerNumber THEN f.Address2
             ELSE g.Address2
        END AS ShipToAddress2,
        CASE WHEN f.Name != Null
                  AND f.CustomerNumber != g.CustomerNumber THEN f.City
             ELSE g.City
        END AS ShipToCity,
        CASE WHEN f.Name != Null
                  AND f.CustomerNumber != g.CustomerNumber THEN f.State
             ELSE g.State
        END AS ShipToState,
        CASE WHEN f.Name != Null
                  AND f.CustomerNumber != g.CustomerNumber THEN f.ZipCode
             ELSE g.ZipCode
        END AS ShipToZipCode,
        CASE WHEN h.Name != Null
                  AND f.CustomerNumber != h.CustomerNumber THEN h.Name
             ELSE g.Name
        END AS BillToName,
        CASE WHEN h.Name != Null
                  AND f.CustomerNumber != h.CustomerNumber THEN h.Address1
             ELSE g.Address1
        END AS BillToAddress1,
        CASE WHEN h.Name != Null
                  AND f.CustomerNumber != h.CustomerNumber THEN h.Address2
             ELSE g.Address2
        END AS BillToAddress2,
        CASE WHEN h.Name != Null
                  AND f.CustomerNumber != h.CustomerNumber THEN h.City
             ELSE g.City
        END AS BillToCity,
        CASE WHEN h.Name != Null
                  AND f.CustomerNumber != h.CustomerNumber THEN h.State
             ELSE g.State
        END AS BillToState,
        CASE WHEN h.Name != Null
                  AND f.CustomerNumber != h.CustomerNumber THEN h.ZipCode
             ELSE g.ZipCode
        END AS BillToZipCode,
        b.*,
        m.CoilNumber,
        m.Weight AS CoilWeight,
        l.CoilID,
        l.Weight
           --  ( SELECT DISTINCT SUM(m.Weight) AS RodWeight FROM tblCoilID m JOIN tblCoilLog l ON l.MasterCoilNumber = m.CoilNumber AND l.BOLNumber = a.BOLNumber AND l.Control = c.Control GROUP BY l.MasterCoilNumber)
FROM    tblInvoiceHeader AS n
        JOIN tblInvoiceDetail AS o ON o.InvoiceNumber = n.InvoiceNumber
        JOIN tblBOLHeader AS a ON a.BOLNumber = o.BOLNumber
        JOIN tblBOLDetail AS b ON a.BOLNumber = b.BOLNumber
        JOIN tblOrderDetail AS c ON c.Control = b.Control
        JOIN tblOrderHeader AS d ON d.OrderNumber = c.OrderNumber
        JOIN tblHeatTransaction AS k ON k.Control = c.Control
        JOIN tblHeat AS i ON i.ID = k.HeatID
        JOIN tblVendor AS j ON j.VendorNumber = i.VendorNumber
        LEFT JOIN tblCoilLog AS m ON a.BOLNumber = m.BOLNumber
                                     AND m.Control = c.Control
        JOIN tblCoilID AS l ON l.CoilNumber = m.MasterCoilNumber
        LEFT JOIN tblCarrier AS e ON a.CarrierNumber = e.CarrierNumber
        LEFT JOIN tblMultipleShipToAddress AS f ON a.ShipToNumber = n.ShipToNumber
        LEFT JOIN tblCustomer AS g ON g.CustomerNumber = n.CustomerNumber
        LEFT JOIN tblMultipleBillToAddress AS h ON a.BillToNumber = n.BillToNumber
WHERE   n.InvoiceNumber = @InvoiceNumber
GROUP BY n.InvoiceNumber,
        n.InvoiceDate,
        n.ShipVia,
        n.CustomerNumber,
        n.ShipToNumber,
        n.BillToNumber,
        a.BOLNumber,
        b.Control,
        a.ShipDate,
        e.Name,
        g.Name,
        g.Address1,
        g.Address2,
        g.City,
        g.State,
        g.ZipCode,
        g.Country,
        c.PricePerCWT,
        i.RodSize,
        i.Grade,
        i.Price,
        i.CustomerPO,
        j.Name,
        b.ID,
        b.BOLNumber,
        b.HeatID,
        b.NoOfCoils,
        b.Description,
        b.CustomerPONumber,
        b.PartNumber,
        b.NetWeight,
        b.TareWeight,
        b.SplitCoils,
        b.NoOfCarriers,
        b.HeatNumber,
        b.Grade,
        b.FinishedSize,
        b.BW,
        b.PackageDescription,
        b.MaterialDescription,
        b.ClassRate,
        b.HazardousMaterial,
        b.upsize_ts,
        c.Control,
        i.Consignment,
        l.CoilID,
        l.Weight,
        m.CoilNumber,
        m.Weight,
        h.CustomerNumber,
        f.CustomerNumber,
        g.CustomerNumber,
        f.Name,
        f.Address1,
        f.Address2,
        f.City,
        f.State,
        f.ZipCode,
        f.Country,
        h.Name,
        h.Address1,
        h.Address2,
        h.City,
        h.State,
        h.ZipCode,
        h.Country
           
END


At the very least lose the DISTINCT clause, with the existing GROUP BY clause, it serves no purpose whatsoever.
0
 

Author Closing Comment

by:jvalescu
ID: 31599287
Changed it around and it completes in 8 seconds.  Thanks.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question