I test my C# applicaiton both with Access and SQL Server database. I note
that the following code snippets takes 24-35 seconds with Access database
while takes only 2-3 seconds with SQL Server to fetch same number of records
(80,000 records).

//with Access Database
this.da = new OdbcDataAdapter("select Barcode, Description from
Items_Detail", DB.cn);

//with SQL Server
this.da = new SqlDataAdapter("select Barcode, Description from
Items_Detail", DB.cn);

//common code
this.da.Fill(this.ds, "Get_Quantity_Result");
grd_All_Recs.SetDataBinding(this.ds, "Get_Quantity_Result");


for my customer simplicity i want to give him solution with Access database
but with this un-acceptable delay i can't do this.

Please help in identifying that why it is taking much time to fetch records
from Access database.

Arif.

Do you have indexes on the relevant fields?

Are you running over a network?

With Access (presumably you mean JET database engine) all the work is being
done by the local computer, so if you are running over a network the
database the process has to download data from the file server, process
that, and get the necessary records.

With SQL, the processing takes place on the server and the requested records
are sent in a batch.

SQL Server also caches information in RAM so that can significantly impact
performance on subsequent calls for the same or similar data.



"Arif" <Arif> wrote in message
news:ce19
[..]

Arif wrote:

[..]
>//common code
>this.da.Fill(this.ds, "Get_Quantity_Result");
>grd_All_Recs.SetDataBinding(this.ds, "Get_Quantity_Result");
>>for my customer simplicity i want to give him solution with Access database
>but with this un-acceptable delay i can't do this.
>
>Please help in identifying that why it is taking much time to fetch records
>from Access database.


I want to argue the validity of your test. It is near
meaningless to fetch 80,000 records at one time. You are
never going to use more than a single, or at most a few,
records in any operation so your query should use a where
clause to severly filter the dataset.

As John mentioned, caching will probably play a significant
role if you run the query multiple times. I don't know what
the OdbcDataAdapter does, but I would not be surprised if
the local machine did not cache the data too.

One other point, the sql statement must be compiled and if
this is done in the local machine (as with DAO) there may be
a significant delay while table statistics are retrieved and
the query's strategy is determined. If you can use a
presaved query, it may be quicker.

I concur with MB's issue with the 'test'. You're not testing a real
world scenario.

Marshall Barton wrote:
[..]

In addition to the points that others have made, you may get better
performance using the OleDb data provider rather than the ODBC data provider
with a JET database.

You don't give any details of your setup here (like is a network involved
for example??).

If a network is involved, then you might try opening a dummy table in your
code BEFORE you try and execute your query.
(so, in some previous code, open up a table in the back end....say a tiny
table with 1 record...keep it open, and THEN try
your query.

Forcing (keeping) a connection open can eliminate many delays.....give this
idea a try...

Thanks John for your kind support,

I have 10 columns with only one primary key column.
I am using on my local PC.

arif.


"John Spencer" wrote:
[..]

Hi,


I don't see any perceptible difference, here, within the two procedures:


=================
private void button1_Click(object sender, EventArgs e)
{
string conn;
conn = @"Data Source=(local)\beta;" +
"Initial Catalog=Testings;Integrated Security=SSPI;";
SqlConnection cn = new SqlConnection(conn);
SqlDataAdapter adapter = new SqlDataAdapter(cn.CreateCommand());
DataSet dataSet = new DataSet();
adapter.SelectCommand.CommandText = "SELECT * FROM iotas WHERE
iota < 10000";
adapter.Fill(dataSet);

}

private void button2_Click(object sender, EventArgs e)
{
string conn;
conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db1.mdb;";
OleDbConnection cn = new OleDbConnection(conn);
OleDbDataAdapter adapter = new
OleDbDataAdapter(cn.CreateCommand());
DataSet dataSet = new DataSet();
adapter.SelectCommand.CommandText = "SELECT * FROM iotas WHERE
iota < 10000";
adapter.Fill(dataSet);
}
===================


Is it possible that your two databases are different in their tables, more
precisely, in the number of records they have stored ?

Is it possible that your MS SQL Server version LIMIT the number of rows
returned (which is usual, by default, on some configuration) ?



Hoping it may help,
Vanderghast, Access MVP

"Arif" <Arif> wrote in message
news:ce19
[..]

Thanks Brendan Reynolds.

Yes, using OleDb data provider rather than the ODBC data provider i see much
more better performance. Now it is also taking the same time taken by SQL
Server.
And it solved my problem.

Tanks again Brendan Reynolds.

arif.

"Brendan Reynolds" wrote:
[..]

"Arif" wrote:

[..]
>
> for my customer simplicity i want to give him solution with Access database
> but with this un-acceptable delay i can't do this.
>
> Please help in identifying that why it is taking much time to fetch records
> from Access database.
>
> Arif.
>Try a Where clause using the Primary key.