Solved

Help with a .NET/SQL Timout problem

Posted on 2009-07-02
4
153 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
Comment Utility
set the command timeout in your connection object to 0.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
Comment Utility
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
Comment Utility
Changed it around and it completes in 8 seconds.  Thanks.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now