Question about query.

[calvinle]

Hi,

I am experiencing very low performance on the form of my database that is stored in the LAN of my company.
On the main form, I have a combobox unbound but query using a code such as:

SELECT tbl_user.full_name FROM tbl_user GROUP BY tbl_user.full_name;

Is there any difference in performance if I use that code above, or I create a new query within Access with that code, and load it as control source for the combobox?

Thanks

 

[Pat Hartman]

Doesn't look like it should be a problem. Is there an index on full_name?

 

[Mark_]

Do you have multiple records in your table with the same value for tbl_user.Full_Name that you need to group together?

If not, why are you grouping? If you are, you would probably want to use a unique identifier rather than simply the [Full_Name] so you can uniquely identify the user.

I think if you replace GROUP BY with ORDER BY you will see an improvement.

I would also try to find out what is really causing you to have slow performance. If you remove this an performance increases you know this was it. In general a table of users is fairly small so I wouldn't expect it to be the source of a slow down.

 

[calvinle]

Doesn't look like it should be a problem. Is there an index on full_name?

There is no indexes on full_name.

 

[calvinle]

Do you have multiple records in your table with the same value for tbl_user.Full_Name that you need to group together?

If not, why are you grouping? If you are, you would probably want to use a unique identifier rather than simply the [Full_Name] so you can uniquely identify the user.

I think if you replace GROUP BY with ORDER BY you will see an improvement.

I would also try to find out what is really causing you to have slow performance. If you remove this an performance increases you know this was it. In general a table of users is fairly small so I wouldn't expect it to be the source of a slow down.

Right, I have removed the GROUP BY and it does help a bit.

But I have another combobox with "case status" which group all the case status of a table linked to a back-end. In this case, I need to use GROUP.
When I click on the combo box of the "case status", it takes about 25 secondes to show the drop down.
There is only a total of 2671 record in that table.

Thanks

 

[arnelgp]

On the Form's Load event, force the combo to get all data:

Dim lngCount As Long
lngCount=me.comboname.listcount

 

[static]

To get unique values from a field you use distinct.

select DISTINCT full_name FROM tbl_user

The fact that you have non unique records in the user table is worrying if they relate to the same person.

25 seconds is too excessive to be just about table indexes. You probably have a very slow network that you can't do anything about plus an inefficient query.

Remove any aggregate functions from sub queries and apply them at a higher level.

 

[calvinle]

On the Form's Load event, force the combo to get all data:

Dim lngCount As Long
lngCount=me.comboname.listcount

If I force to get all data to the combobox On Load then the time to load the form will be too long+new data are added to the table. So I have to requery the combobox every time which wont help the issue.

 

[Pat Hartman]

Combos typically retrieve data from "lookup" tables. These are tables that are created to define the universe of values for a code field. So for example, rather than making a state combo select state from all the order records and use group to get a single value for each state, create a state table.

~2600 isn't a lot of rows but an index still might help.

 

[Mark_]

Right, I have removed the GROUP BY and it does help a bit.

But I have another combobox with "case status" which group all the case status of a table linked to a back-end. In this case, I need to use GROUP.
When I click on the combo box of the "case status", it takes about 25 secondes to show the drop down.
There is only a total of 2671 record in that table.

Thanks

So does this mean that, rather than having a fixed list of "Case Status"es, look through your entire database to build this list? What happens if the last "Open" case is "Closed"? Does that mean you can't add a new "Open" case?

I agree with the others, 25 seconds to query 2671 records is excessive. Are you only asking for the relevant fields or for *? And do the records hold large blocks of information that would be passed along?

 

[calvinle]

Combos typically retrieve data from "lookup" tables. These are tables that are created to define the universe of values for a code field. So for example, rather than making a state combo select state from all the order records and use group to get a single value for each state, create a state table.

~2600 isn't a lot of rows but an index still might help.

I will try to have an index for the case_status and see how fast it will be.


Thanks

 

[calvinle]

So does this mean that, rather than having a fixed list of "Case Status"es, look through your entire database to build this list? What happens if the last "Open" case is "Closed"? Does that mean you can't add a new "Open" case?

I agree with the others, 25 seconds to query 2671 records is excessive. Are you only asking for the relevant fields or for *? And do the records hold large blocks of information that would be passed along?

Each user has their front end that is linked to a backend database.
Because multiple user is using at the same time, it tends to be very slow to access to the backend data. If i try to access late at night without anyone in it, the speed seems to be faster.

The combo box is used to search for cases. I can list all the user fixed in the combobox but that will make the list too large for nothing? As I Only want to list the user that is actually in the case but this is taking that 25 secondes to load the actual data. Only the first load is long, after its not an issue to view the drop down list again unless the user clear the search whcih also clear the combobox data in memory, i guess?