November 16, 2009 at 6:44 am
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
November 16, 2009 at 7:17 am
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?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2009 at 8:48 am
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
November 17, 2009 at 12:37 am
- 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...
November 17, 2009 at 1:06 am
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
November 17, 2009 at 2:03 am
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..
November 18, 2009 at 3:27 am
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 ?
November 18, 2009 at 5:25 am
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
November 18, 2009 at 5:54 am
i think now you see execution plan
November 18, 2009 at 6:21 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy