A small Question

[Eku]

Does anyone know how can i improve the speed of retrieving a result set from a query?

Here is my scenario, Im using Java as my front end. In small scale to medium scale(around 100K rows in DB) database it works OK. But the problem is when im fetching a specific column in a huge database. Example im going to get all calls of a telephone specific number for the previous month. Ot took me around 2 hours just to get that data. The Database is Gigantic, it contains all calls of all telephone numbers of our company's subscribers. it s like a whole country, our database warehouse only contains the last 3 months of those records. Can anyone had a idea to speed it up a little.

What im doing is fetching the record they want to rebill and fixed the problem, My program fetches that record and i have my own database where the user can save that fetch records and store it. so that there would only be 1 connection per record, if that record exist locally it would retrieve the local copy. I hope someone migth give me an idea =P

[Eranga]

I'm not clever with DBMS. But most of developers talking about data partitioning. Kind of a partitioning on a database and work on them. Did you know about that more?

[ProjectKaiser]

Quote:

Originally Posted by Eku

Ot took me around 2 hours just to get that data.

Answer "depends".
1) How many approx. rows you get as a result during 2 hours ?
2) Do you have an index for "telephone number" field ?

[Eranga]

Quote:

Originally Posted by ProjectKaiser

2) Do you have an index for "telephone number" field ?

Are you talking about data row index? It's a really bad practice if don't have, right. In most of development my DBMS admin talking seriously about that.

[ProjectKaiser]

I'm talking about something like:

Code:

create index my_table_phone_number on my_table(phone_number)

[Eranga]

Hey ProjectKaiser, what's the use of it even need to retrieve large number of data. How can be this is helpful on that? I'm asking this, because I'm not much familiar with DB related development and I want to learn something on this.

[fishtoprecords]

Quote:

Originally Posted by Eranga

Hey ProjectKaiser, what's the use of it even need to retrieve large number of data. How can be this is helpful on that? I'm asking this, because I'm not much familiar with DB related development and I want to learn something on this.

I'm not Kaiser. But if you have something like all the call data records of a telco switch or of a cell tower, then there are millions of records reflecting the thousands of numbers that use the switch or telco.

So by using an index on the "phone_number" field, and doing an equals search, you are eliminating millions of records that don't match. Thus you only have to search through the records for the calls you made.

A select using something like
select * from bigcallrecordtable where phone_number = "201 555-1212" is massively faster with an index than without.

If you don't work in the DBMS space, you probably should let someone who does answer the questions.

And OP, your thread title leaves a lot to be desired. Better titles are likely to get more responses.

The OP doesn't talk about how many tables are being joined, how many fields are being searches, whether or not a "like" verb is being used, etc.

DBMS query optimization is a wide open topic, there are lots of books on it, and there are PhD dissertations yet to be written

[Eranga]

Quote:

Originally Posted by fishtoprecords

I'm not Kaiser. But if you have something like all the call data records of a telco switch or of a cell tower, then there are millions of records reflecting the thousands of numbers that use the switch or telco.

So by using an index on the "phone_number" field, and doing an equals search, you are eliminating millions of records that don't match. Thus you only have to search through the records for the calls you made.

A select using something like
select * from bigcallrecordtable where phone_number = "201 555-1212" is massively faster with an index than without.

Thanks for the explanation pal. It's really helpful to me and better to learn more about DBMS.