sql timeout exception

  • hello there

    i am using sql server 2005 at windows xp sp2 with a windows application i developed. my windows app. connect to sql server and fetches records from table. but i have 6,5x,xx,xxx records at one of my customer. i am checking my app. until 40,00,000 records but now with 6.5 millions records i got sql timeout error

    i am used sql data adapter with command.

    code as below-

    str= 'select query

    cmd=new sqlcommand(str,conn)

    cmd.CommandTimeout=0

    da=cmd.excutequery()

    please help me in this

  • There are a lot of things that could be happening here, but without table and index definitions, and the specific query it is hard to say what could be the issue.

    Do you have any indexes?

    Are you specifying a WHERE clause?

    Are you returning all columns or only the columns you need?

    Is the application and database all on one PC or is the database on a server?

  • Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • - thanks for all who replied upto yet.. .

    Database and windows application and web application all are on same pc.

    I am creating table via web application and then uses it from windows application

    here is the create table code from web application

    --------------------

    CREATE TABLE dbo.tablename(MANDKENN numeric(18,0) NOT NULL, VERSIONN varchar(100) NOT NULL, SN varchar(100) NULL, NUMMER varchar(100) NOT NULL, DELROW bit NULL,...... ")

    create index idx on dbo.tablename(SN,VERSIONN)

    create clustered index delX on dbo.tablename(DELROW)

    ---------------

    now in this table i am getting data from somewhere. i have lots of table like same structure (means different table has different column but above first five column is same in all with same index) but got error at only one table because this table has lots of data. this table has approx. 200 columns. upto 2,00,000 of records i check at my system it works fine.

    but at client side i got 6,55,xx,xxx records. where i got time out problem

    this is the below code what i am doing in windows application-

    myappset is the module where i gave connection setting etc and declaring variables

    -----------------------------

    'all this below code are inside a for loop of tables

    select CRITERIA,SORTKEY from Mandant_List where TBL_NAME='" & varTblName.ToString & "' and MANDANT_ID=" & myAppSet.ITBIDBID

    myAppSet.cmd = New SqlCommand(varStr1, myAppSet.conn)

    ' this does not have a problem this is required for my further process of code

    myAppSet.cmd.CommandTimeout = 0

    myAppSet.da = myAppSet.cmd.ExecuteReader()

    if da.read then

    'process something

    endif

    myAppSet.da.close

    myAppSet.cmd.close

    select sn,versionn from " & varTblName.ToString & " where MANDKENN=" & myAppSet.ITBIDBID & " order by versionn desc

    myAppSet.cmd = New SqlCommand(varStr1, myAppSet.conn)

    myAppSet.cmd.CommandTimeout = 0

    myAppSet.da = myAppSet.cmd.ExecuteReader()

    Dim varDaRead As Boolean = False

    If myAppSet.da.Read() Then

    varDaRead = True

    End If

    myAppSet.da.close

    ' i thought i got timeout here due to query is not completely run.

    myAppSet.cmd.close

    myAppSet.conn.close

    if varDaRead then

    'process further with code only. no more sql query.

    endif

    ----------------------------------------------------

    might be you all will give me solution now...

  • Execution plan?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am waiting for execution plan from client side machine.

    but i can solve my problem by doing some change in code like this

    now i get no problem in 6,55xxxxx records

    ----------------------

    select SN,VERSIONN from " & varTblName.ToString & " where MANDKENN=" & myAppSet.ITBIDBID & " order by VERSIONN desc

    myAppSet.cmd = New SqlCommand(varStr1, myAppSet.conn)

    myAppSet.cmd.CommandTimeout = 0

    myAppSet.da = myAppSet.cmd.ExecuteReader()

    Dim varDaRead As Boolean = False

    If myAppSet.da.Read() Then

    varDaRead = True

    End If

    if varDaRead then

    'process further with code only. no more sql query.

    endif

    myAppSet.da.close

    myAppSet.cmd.close

    myAppSet.conn.close

    ---------------------

    means closing of da and cmd is after my code is process so query get sometime to execute but this is i feel not a professional solution because here i am not using records for further processing but in future if need to process with records too inside then might be this is more slow and might be timeout again happens..

  • now i attached the execution plan

    speed is slower than then expected in my windows application with these 6.5 million records

    and running command in sql server query designer will take 2.44 minute for 66,36,240 records with only 21 columns there (as in attached execution plan show) in select statement below

    select * from verkauf$Position where mandkenn=1

    but my windows application taking more time (about 7+ minutes)than this

    but no timeout happens now

    can i increase performance somehow ?

  • gr8.jain (11/18/2009)


    now i attached the execution plan

    Where?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i think now you see execution plan

  • We still need the table definition and sample data like Gail (GilaMonster) asked? Your execution plan shows a clustered index scan, which essentially is a full table scan, so there is no way to speed up the query without adding or changing indexes or changing the query.

Viewing 10 posts - 1 through 10 (of 10 total)

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