Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with a .NET/SQL Timout problem

Posted on 2009-07-02
4
Medium Priority
?
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
Bob Hoffman earned 1400 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 600 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

660 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