0

I have a table with 20,000 records when i write query as follows to retrieve data

select emp_id from emp;

it is talking 13 to 15 sec to execute how can i get data faster from the table. Note it is not 20,000 also more.

here is the out put emp_id nothing but mtrl_cd here

 columnname ID      PK null       datatype     Histogram  numdistinct
    MTRL_DESC   2       Y   VARCHAR2 (100 Byte)   None    19480         
    MTRL_CD     1   1   N   VARCHAR2 (9 Byte)     None    19990
Sreedhar goud
  • 81
  • 2
  • 5
  • 15
  • http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc and check this also http://howto.techworld.com/applications/3219110/how-to-get-faster-sql-queries/ – riti Oct 18 '12 at 05:44
  • 1
    Its(taking more time) not dependent on select query There many problems 1. Accessing data from Netwrok 2. More connections to same database 3.Your code – andy Oct 18 '12 at 05:44
  • @Scott Bartell yes emp_id is primary – Sreedhar goud Oct 18 '12 at 05:47
  • Primary keys should be already indexed. What are you doing with the data? Why do you need a list of 20,000+ ids? – Scott Bartell Oct 18 '12 at 05:51
  • @ScottBartell just i want to bind the emp_id to combo box. when i try this my screen is not working due to large time. – Sreedhar goud Oct 18 '12 at 05:54
  • run `describe emp;` and provide the output for the `emp_id` row – Scott Bartell Oct 18 '12 at 06:08

2 Answers2

2

if emp_id is primary key already, you won't be able to do much for this simple SELECT. the reason why the screen is slow is because you bind > 20000 items to the combo box.

one possible solution is: ajax autocomplete, here is a sample for .net. http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/AutoComplete/AutoComplete.aspx

urlreader
  • 6,319
  • 7
  • 57
  • 91
1

The main fact when you access a large data is always get those data which actually required by you. For that we use conditions in quires. Common conditions we use are, paging, search conditions, show only those records which are related to some data etc. So consider this as a design issue.

[Edit]

If you want those large data to be linked to a combo box , jut see the following article.

jQuery Searchable DropDown Plugin Demo

ASP.NET AJAX Control Toolkit

How do I use the ComboBox Control? (C#)

Jomy John
  • 6,308
  • 4
  • 28
  • 32