473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

database sort time

91
I have an employee database using access, In VB code, I need to sort through the database via Employee ID, The total employee record is about 8000, I sort from the first employee till the end. I feel the sorting time a bit longer, is there any way to shorten the sort time, Or shall I migrate the database to SQL? Thanks.
Aug 20 '09 #1
6 2167
mshmyob
904 Expert 512MB
@qfchen
Is Employee ID the primary key? If not, then create an index on the Employee ID.

cheers,
Aug 20 '09 #2
qfchen
91
Yes, it's already primary key for employeeID. any other suggestion? Thanks
Aug 21 '09 #3
mshmyob
904 Expert 512MB
@qfchen
Could you explain how you are doing the sort? Are you doing it by a query?

What is your table structure?

Have you done a compact/repair?

cheers,
Aug 21 '09 #4
qfchen
91
Here is a bit info of my system: I have 5 devices link to a PC via network switch, each device has a scanner which input employeeID to the PC, a database is sitting in the PC. when the device send the ID to PC, PC software will sort through the database for that ID, then send one info related to this ID back to the device. the PC application is written in VB2005, here is the sort portion of code

frmEmployee.EmployeeTableAdapter.Fill(frmEmployee. Employee_DataSet.Employee)
count = frmEmployee.Employee_DataSet.Employee.Rows.Count

For i = 0 To count - 1
If employee.cardID = frmEmployee.Employee_DataSet.Employee.Rows(i).Item (0) Then
NameStr = frmEmployee.Employee_DataSet.Employee.Rows(i).Item (2) 'first name
DptStr = frmEmployee.Employee_DataSet.Employee.Rows(i).Item (4) 'department
found += 1
goto foundOne
End If
Next

The table has 10 field, includes ID, Name, department, section, shift, workstation and so on. I never do compact/repair, don't what's this mean as well, could you explain? Thanks
Aug 21 '09 #5
ajalwaysus
266 Expert 100+
Not to interject in the middle here mshmyob, but qfchen, if you are storing this information over the network, part of your speed issue may be network related. Also, on compact and repair please see link
http://office.microsoft.com/en-us/ac...874491033.aspx
Aug 21 '09 #6
qfchen
91
may be two possiblities, network and sorting in DB, but when employee number is increased from 1000 to 7000, I feel significant slow, any way to improve from both side? network, I just use winsock, communicate between PC and device via IP address. thanks.
Aug 21 '09 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: krystoffff | last post by:
I currently have PostgreSQL 7.1 installed on a server with about 700 Mb of RAM. I have many problems of speed with a database I created. For example, it took almost 12 sec to run the query...
10
by: SStory | last post by:
I am creating a CD that will have various product images. I need more information on these JPG's so I will need some sort of data on each of them. I will also need to be able to find these images...
2
by: Carl Summers | last post by:
I have a table in an Access database that has no sort applied in Access. When I fill a dataset with data from that table (the entire one dimensional table) my dataset is sorted differently than...
19
by: Andy B | last post by:
Hello, Sorry for this newbish question. Briefly, my problem: ------------------ I expect the database I'm working on to reach something in the order of 12-16 Gigabytes, and I am interested...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
49
by: ARC | last post by:
Hello all, I have one chance to get this right, as I'm nearing a release of a program. I've looked at the database settings, and so far, have set the following: * Unchecked 'Enable design...
39
by: alex | last post by:
I've converted a latin1 database I have to utf8. The process has been: # mysqldump -u root -p --default-character-set=latin1 -c --insert-ignore --skip-set-charset mydb mydb.sql # iconv -f...
0
by: qfchen | last post by:
I have an employee database using microsoft access, In VB code, I need to sort through the database via Employee ID, The total employee record is about 8000, I sort from the first employee till the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.