I use the following lines for retrieving data from sql server database:
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = connection.ConnectionTimeout;
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = connection;
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
dataAdapter.Fill(dataset);
My query takes only 10 seconds in sql server management studio.
the fill method in .Net takes 15 minutes for the first run.
and then fill method in .Net takes 10 seconds for the rest of runs.
Yes dataset will take time. It will consume more memory then datareader. And to allocate memory as per data is receiving from sql server takes more time.
Only use dataset if you need to manupulate data (and you can not do it in sql query). Always use datareader when you are developing web application.
timeSqlDataAdapterfill
Sandip R Patel
Please do not forget to click 'Mark as Answer' if this answer helps.
I tested datareader and found it similar to dataset in my case
follow these tips for effective asp.net website
Tips For Database Operations
1) Return Multiple Resultsets
The database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve.
Solution:
Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the database. You'll be making your system more scalable, too, as you'll cut down on the work the database server is doing managing requests.
2) Connection Pooling and Object Pooling
Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead of opening and closing a connection for each request. It's done implicitly, but you get one pool per unique connection string. Make sure you call
Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.
Account for the following issues when pooling is a part of your design:
? Share connections
? Avoid per-user logons to the database
? Do not vary connection strings
? Do not cache connections
3) Use SqlDataReader Instead of Dataset wherever it is possible
If you are reading a table sequentially you should use the DataReader rather than DataSet. DataReader object creates a read only stream of data that will increase your application performance because only one row is in memory at a time.
4) Keep Your Datasets Lean
Remember that the dataset stores all of its data in memory, and that the more data you request, the longer it will take to transmit across the wire.
Therefore Only put the records you need into the dataset.
5) Avoid Inefficient queries How it affects performance:
Queries that process and then return more columns or rows than necessary, waste processing cycles that could best be used for servicing other requests.
Cause of Inefficient queries:
? Too much data in your results is usually the result of inefficient queries.
? The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make the WHERE clause as specific as possible
to ensure that the least number of rows are returned.
? Queries that do not take advantage of indexes may also cause poor performance.
6) Unnecessary round trips How it affects performance:
Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic
and processing load on the database server can adversely affect performance. Solution:
Keep round trips to an absolute minimum.
7) Too many open connections
Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability.
Solution:
To ensure the efficient use of connection pooling, avoid keeping connections open and avoid varying connection strings.
8) Avoid Transaction misuse How it affects performance:
If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure.
Solution:
Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only where necessary.
9) Avoid Over Normalized tables
Over Normalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.
10) Reduce Serialization
Dataset serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, Dataset serialization often introduces performance bottlenecks.
You can reduce the performance impact in a number of ways:
? Use column name aliasing
? Avoid serializing multiple versions of the same data
? Reduce the number of DataTable objects that are serialized
11) Do Not Use CommandBuilder at Run Time How it affects performance:
CommandBuilder objects such as as SqlCommandBuilder and OleDbCommandBuilder are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects
performance. Solution:
Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.
12) Use Stored Procedures Whenever Possible
?Stored procedures are highly optimized tools that result in excellent performance when used effectively.
?Set up stored procedures to handle inserts, updates, and deletes with the data adapter
?Stored procedures do not have to be interpreted, compiled or even transmitted from the client, and cut down on both network traffic and server overhead.
?Be sure to use CommandType.StoredProcedure instead of CommandType.Text
13) Avoid Auto-Generated Commands
When using a data adapter, avoid auto-generated commands. These require additional trips to the server to retrieve meta data, and give you a lower level of interaction control. While using auto-generated commands is convenient, it's worth the effort to do it
yourself in performance-critical applications.
14) Use Sequential Access as Often as Possible
With a data reader, use CommandBehavior.SequentialAccess. This is essential for dealing with blob data types since it allows data to be read off of the wire in small chunks. While you can only work with one piece of the data at a time, the latency for loading
a large data type disappears. If you don't need to work the whole object at once, using
Sequential Access will give you much better performance.
breceivemail
Member
82 Points
43 Posts
SqlDataAdapter fill method takes too long time for the first run
Oct 17, 2011 11:31 AM|LINK
I use the following lines for retrieving data from sql server database:
My query takes only 10 seconds in sql server management studio.
the fill method in .Net takes 15 minutes for the first run.
and then fill method in .Net takes 10 seconds for the rest of runs.
why?
time SqlDataAdapter fill
v.arunkarthi...
Participant
1247 Points
341 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 17, 2011 11:50 AM|LINK
dataset is a disconnected architecture.. use datareader it works in forward only mode.. its fater wen compared to dataset..
time SqlDataAdapter fill
karthik..
Please mark as Answer if it helps. !!???
breceivemail
Member
82 Points
43 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 17, 2011 01:36 PM|LINK
unfortunately no deference :(
sandiprp
Member
210 Points
45 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 17, 2011 01:37 PM|LINK
Yes dataset will take time. It will consume more memory then datareader. And to allocate memory as per data is receiving from sql server takes more time.
Only use dataset if you need to manupulate data (and you can not do it in sql query). Always use datareader when you are developing web application.
time SqlDataAdapter fill
Please do not forget to click 'Mark as Answer' if this answer helps.
breceivemail
Member
82 Points
43 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 18, 2011 05:40 AM|LINK
I tested datareader and found it similar to dataset in my case
v.arunkarthi...
Participant
1247 Points
341 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 18, 2011 06:38 AM|LINK
follow these tips for effective asp.net website
Tips For Database Operations
1) Return Multiple Resultsets
The database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve.
Solution:
Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the database. You'll be making your system more scalable, too, as you'll cut down on the work the database server is doing managing requests.
2) Connection Pooling and Object Pooling
Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead of opening and closing a connection for each request. It's done implicitly, but you get one pool per unique connection string. Make sure you call Close or Dispose on a connection as soon as possible. When pooling is enabled, calling Close or Dispose returns the connection to the pool instead of closing the underlying database connection.
Account for the following issues when pooling is a part of your design:
? Share connections
? Avoid per-user logons to the database
? Do not vary connection strings
? Do not cache connections
3) Use SqlDataReader Instead of Dataset wherever it is possible
If you are reading a table sequentially you should use the DataReader rather than DataSet. DataReader object creates a read only stream of data that will increase your application performance because only one row is in memory at a time.
4) Keep Your Datasets Lean
Remember that the dataset stores all of its data in memory, and that the more data you request, the longer it will take to transmit across the wire.
Therefore Only put the records you need into the dataset.
5) Avoid Inefficient queries
How it affects performance:
Queries that process and then return more columns or rows than necessary, waste processing cycles that could best be used for servicing other requests.
Cause of Inefficient queries:
? Too much data in your results is usually the result of inefficient queries.
? The SELECT * query often causes this problem. You do not usually need to return all the columns in a row. Also, analyze the WHERE clause in your queries to ensure that you are not returning too many rows. Try to make the WHERE clause as specific as possible to ensure that the least number of rows are returned.
? Queries that do not take advantage of indexes may also cause poor performance.
6) Unnecessary round trips
How it affects performance:
Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance.
Solution:
Keep round trips to an absolute minimum.
7) Too many open connections
Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability.
Solution:
To ensure the efficient use of connection pooling, avoid keeping connections open and avoid varying connection strings.
8) Avoid Transaction misuse
How it affects performance:
If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure.
Solution:
Transactions are necessary to ensure the integrity of your data, but you need to ensure that you use the appropriate type of transaction for the shortest duration possible and only where necessary.
9) Avoid Over Normalized tables
Over Normalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.
10) Reduce Serialization
Dataset serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, Dataset serialization often introduces performance bottlenecks.
You can reduce the performance impact in a number of ways:
? Use column name aliasing
? Avoid serializing multiple versions of the same data
? Reduce the number of DataTable objects that are serialized
11) Do Not Use CommandBuilder at Run Time
How it affects performance:
CommandBuilder objects such as as SqlCommandBuilder and OleDbCommandBuilder are useful when you are designing and prototyping your application. However, you should not use them in production applications. The processing required to generate the commands affects performance.
Solution:
Manually create stored procedures for your commands, or use the Visual Studio® .NET design-time wizard and customize them later if necessary.
12) Use Stored Procedures Whenever Possible
?Stored procedures are highly optimized tools that result in excellent performance when used effectively.
?Set up stored procedures to handle inserts, updates, and deletes with the data adapter
?Stored procedures do not have to be interpreted, compiled or even transmitted from the client, and cut down on both network traffic and server overhead.
?Be sure to use CommandType.StoredProcedure instead of CommandType.Text
13) Avoid Auto-Generated Commands
When using a data adapter, avoid auto-generated commands. These require additional trips to the server to retrieve meta data, and give you a lower level of interaction control. While using auto-generated commands is convenient, it's worth the effort to do it yourself in performance-critical applications.
14) Use Sequential Access as Often as Possible
With a data reader, use CommandBehavior.SequentialAccess. This is essential for dealing with blob data types since it allows data to be read off of the wire in small chunks. While you can only work with one piece of the data at a time, the latency for loading a large data type disappears. If you don't need to work the whole object at once, using
Sequential Access will give you much better performance.
credits:http://www.dotnetfunda.com/articles/article45.aspx
karthik..
Please mark as Answer if it helps. !!???
kratos_Vimal
Contributor
3744 Points
960 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 18, 2011 06:53 AM|LINK
How much data are you fetching and how are you using the dataset
Vimal
breceivemail
Member
82 Points
43 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 18, 2011 07:22 AM|LINK
about 50 rows and this is my code. First run takes about 15 minutes. Second run takes about 20 seconds.
SqlCommand cmd = new SqlCommand(); cmd.CommandTimeout = connection.ConnectionTimeout; cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.Connection = connection; connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet", dr)); ReportViewer1.LocalReport.EnableExternalImages = true; StringReader sReader = new StringReader(rptXmlString); ReportViewer1.LocalReport.LoadReportDefinition(sReader);kratos_Vimal
Contributor
3744 Points
960 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 18, 2011 07:28 AM|LINK
What is the query you are using?
Try creating a View or stored procedure in SQl and see if it changes thing ...
Are you sure it is the data filling not the binding that is taking time
Try to fill the data in a datatable and place a break point there to check if it is filling in time
Vimal
breceivemail
Member
82 Points
43 Posts
Re: SqlDataAdapter fill method takes too long time for the first run
Oct 18, 2011 07:29 AM|LINK
about 50 rows and this is my code. First run takes about 15 minutes. Second run takes about 20 seconds.
SqlCommand cmd = new SqlCommand(); cmd.CommandTimeout = connection.ConnectionTimeout; cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.Connection = connection; connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet", dr)); ReportViewer1.LocalReport.EnableExternalImages = true; StringReader sReader = new StringReader(rptXmlString); ReportViewer1.LocalReport.LoadReportDefinition(sReader);