TimeOut Expired

Question

TimeOut Expired

Arun karthik

SSC Rookie

Points: 27
More actions
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...

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

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

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 1

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

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

Arun karthik SSC Rookie Points: 27 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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