Post reply

TimeOut Expired

  • August 22, 2005 at 2:13 am

    #90782

    Hi Everybody..

    I having a problem here  can you just help me
     
    I am having an EXE built on .net which does the following
     
    1. Collects data from the database.
    2. Writes to a txt file
    3. Uploads the file to some FTP site
     
    There problem i am facing is .. the database part by retrieving the data itself...
     

    Function

    OpenDataset(ByVal mySql As String, ByVal myDB As String ) As DataTable

    Dim myDS As New DataTable

    Dim Conn2 As New SqlConnection("server=NLDBOWA12;database=" & myDB & ";uid=sa;pwd=XXXXXX; Connect Timeout = 900000")

    Dim mySQLDA As New SqlDataAdapter(mySql, Conn2)

    Dim mytrans As SqlTransaction

    mySQLDA.SelectCommand.CommandType = CommandType.Text

    mySQLDA.Fill(myDS) - In this Area i am getting an Error as  "Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

    OpenDataset = myDS

    mySQLDA.Dispose()

    End Function

    I am having a View which takes a long time to run .. it takes 2-3 mins for it to run ...

     
    CREATE VIEW dbo.V_AcerRM_HDA_AFRReport 

    AS 

    SELECT    DISTINCT  ISNULL(AcerCSS.dbo.CODE_MASTER.ACM_CODE_VALUE, '*') AS Company, dbo.V_AcerRM_HDA_HDATicketBase.SerialNumber,  

                          AcerCSS.dbo.Repairs.DATETIMELASTMODIFIED , AcerCSS.dbo.Repairs.IDTicket, AcerCSS.dbo.COMPONENTFAILURE.CFLFGPARTNUMBER,  

                          AcerCSS.dbo.SPAREPARTRETURNS.SPRPARTNUMBER, AcerCSS.dbo.SPAREPARTRETURNS.SPRQTY, B.FailureCode + A.FailureCode AS ErrorCode,  

                          AcerCSS.dbo.PRODUCT_MASTER.PRM_MODEL, AcerCSS.dbo.PRODUCT_MASTER.PRM_SMALL_CLASS, AcerCSS.dbo.Repairs.IDLocation,  

                          AcerCSS.dbo.PRODUCT_MASTER.PRM_PART_NUMBER,dbo.V_AcerRM_HDA_HDATicketBase.CreateDate  

    FROM         AcerCSS.dbo.CODE_MASTER RIGHT OUTER JOIN 

                          AcerCSS.dbo.PRODUCT_MASTER INNER JOIN 

                          AcerCSS.dbo.FailureCodes B INNER JOIN 

                          AcerCSS.dbo.FailureCodes A ON B.IDFailureCode = A.IDParentFailureCode INNER JOIN 

                          dbo.V_AcerRM_HDA_HDATicketBase INNER JOIN 

                          AcerCSS.dbo.Repairs ON dbo.V_AcerRM_HDA_HDATicketBase.IDTicket = AcerCSS.dbo.Repairs.IDTicket INNER JOIN 

                          AcerCSS.dbo.COMPONENTFAILURE ON AcerCSS.dbo.Repairs.IDRepair = AcerCSS.dbo.COMPONENTFAILURE.IDREPAIR INNER JOIN 

                          AcerCSS.dbo.SPAREPARTRETURNS ON AcerCSS.dbo.Repairs.IDRepair = AcerCSS.dbo.SPAREPARTRETURNS.IDREPAIR AND  

                          AcerCSS.dbo.COMPONENTFAILURE.CFLCOMPPARTNUMBER = AcerCSS.dbo.SPAREPARTRETURNS.SPRPARTNUMBER ON  

                          A.IDFailureCode = AcerCSS.dbo.COMPONENTFAILURE.IDFAILURECODE ON REPLACE(AcerCSS.dbo.PRODUCT_MASTER.PRM_PART_NUMBER, '.', '')  

                          = AcerCSS.dbo.COMPONENTFAILURE.CFLFGPARTNUMBER ON  

                          AcerCSS.dbo.CODE_MASTER.ACM_CHAR1 = dbo.V_AcerRM_HDA_HDATicketBase.Country   

      

      

     Do you guys have any idea .. what is going wrong where and how can i fix this...

  • August 22, 2005 at 5:40 am

    #583860

    Regardless of how this intereacts with .NET, your 1st priority is to ensure that it executes efficiently in T-SQL.  Have a look at (or post) the execution plan for the query (and DDL for related tables).  Eliminating the bottlenecks there will go a long way to solving your problem.

    How may rows of data do you have in these tables?

     

    ps....aliasing the tables with shorter names (a,b,c,d, etc) makes code a lot easier to read.

  • August 22, 2005 at 6:28 am

    #583870

    This is your bottleneck :

    REPLACE(AcerCSS.dbo.PRODUCT_MASTER.PRM_PART_NUMBER, '.', '')

    = AcerCSS.dbo.COMPONENTFAILURE.CFLFGPARTNUMBER ON

    AcerCSS.dbo.CODE_MASTER.ACM_CHAR1 = dbo.V_AcerRM_HDA_HDATicketBase.Country

    That join will force a table scan and that can't be fast (unless you have bery few rows).

    BTW, aliasing is a good idea but PLEASE use meaningfull alias, a,b,c,d mean nothing and that takes longer to debug the code because you always have to try to guess from which table the alias comes from.

  • August 22, 2005 at 6:31 am

    #583874

    Another item to look at (from what I can tell) is that the 1st couple of joined tables you have not defined the joins.  This can lead to performance problems and possible cartesian (cross-join) queries.....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • August 22, 2005 at 11:43 pm

    #584146

    thanks Remi Gregoire

    That was a useful information .. and futher more on that .. is there any other way which i can avoid using the replace

    i have this .. on that AcerCSS.dbo.PRODUCT_MASTER.PRM_PART_NUMBER the information are stored with "." in it and on AcerCSS.dbo.COMPONENTFAILURE.CFLFGPARTNUMBER  i dont have them

    these are two matching column on which i can join... since i need both the information for my use..

    is there  any other way which i could use it.. can you help me on that....

     

    thanks in advance

    Arun karthik

  • August 23, 2005 at 6:53 am

    #584180

    Make a new calculated column (with the replaced version). Index it and use it in the join.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply