I would like a faster and more effective way to implement the following lines of codes.

Question

0.00/5 (No votes)

See more:

Hello, I would like the know a faster way to implement these lines of codes, The concept is that I want to select certain records from a query which selects a couple of tables linked with one another within Microsoft access that follow certain criteria.. which worked fine, but now that the access database has become much larger, it's become slow and unreliable taking 5 minutes and more to finish executing, How can I optimize the following code in a faster and more effective manner?

This is the code:

C#

Datatable datatable;
Datatable datatable1;
Datatable datatable2;
Int loopint = 0;
 
OledbDataAdapter dataadapter = new OleDbDataAdapter("Select Distinct EmpNo from Table ",connection);
dataadapter.Fill(datatable);
 
While ( loopint < datatable.rows.count) //for each employee...
{
    OledbDataAdapter dataadapter1 = new OleDbDataAdapter("Select * from whatever1 where col1 = '"+something+"' AND col2 = '"+datatablet.rows[loopint][0]+"' AND etc", connection);
    OledbdataAdapter dataadapter2 = new OleDbDataAdapter ("Select * from whatever2 where col1 = ETC", connection);
    dataadapter1.fill(datatable1);
    dataadapter2.fill(datatable2);
    loopint++;
}

....rest of the method

the while loop shown above has become super slow and gets slower as the database continues to grow.

Posted 1-Sep-14 14:37pm

Member 11051430

Updated 1-Sep-14 18:48pm

George Jonsson

v4

Add a Solution

Comments

[no name] 1-Sep-14 20:58pm

The easiest, quickest, most optimizing thing you can do is..... switch your database away from Access.

Sergey Alexandrovich Kryukov 1-Sep-14 22:24pm

Oh yes... Access... OleDb... sounds bad enough to stop even thinking about any good performance...
—SA

syed shanu 1-Sep-14 22:48pm

If you cont work with SQL Server ,try to use MYSQL as its open Source.
I have one more question as why your binding the dataset in looping it seems bad idea.If you tell your requirement lets think about some best way to do that.
and insted of using 2 separate select query .
Try to use join or Union if both not possible then try like this.
Dataset ds=new dataset();
OledbdataAdapter dataadapter2 = new OleDbDataAdapter ("Select * from whatever1 where col1 = '"+something+"' AND col2 = '"+datatablet.rows[loopint][0]+"' AND etc;Select * from whatever2 where col1 = ETC;",connection);
dataadapter1.fill(ds);
now in ds.tables[0] you will have 1st selected table records and in
ds.tables[1] you will have 2nd selected table records.
This will avoid DD hit by 2 time.

Member 11051430 2-Sep-14 0:15am

thanks, It's easier for me to use sql but I guess I initially assumed that the problems with microsoft access revolves around the number of users using the application, but it seems like it's also a problem with the coding itself as well? if that's the case then I will switch to sql immediately as this is something I won't endure :P.

syed shanu 2-Sep-14 0:22am

It depends on your project.is your self project or your working for soem project.Its web based or winform based,How big is your project and how many users use that.how much data you will store and etc.First make a Plan for project and then decide which Db will be best for you and then start your code.

3 solutions

Solution 3

- As access as limitation in size and SP are not used; as a result the processing is slow
- In order to enhance the performance migrate the Access database to SQL Server which is avialable inbuild in VS.NET
- Use the Stored Procedures to improve the performance of the queries

Posted 1-Sep-14 19:44pm

MukeshSagar

Solution 4

Agree with other answers, you should do migration. And stored procedure'd be better.
0) Never use SELECT * (Mention required columns)
1) Move that loop logic to stored procedure
2) When you migrate your database to some other database, use appropriate data provider. (Ex. SqlClient[^] for SQL Server, OracleClient[^] for Oracle)

Free attachment
a) Use Parameterized Query[^]

Posted 1-Sep-14 21:35pm

thatraja

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raul Iloc · Accepted Answer · 2014-09-01T19:37:00

1.I am strongly suggesting you to migrate your database to MS SQL (you could use an Express free version);

2.You should optimize your code by using pagination and stored procedures.
You could find details about using pagination based on stored procedures in my next article:
Advanced ASPX GridView Pagination and Data Entities[^]