Results 1 to 8 of 8
  1. #1
    Eku
    Eku is offline Senior Member
    Join Date
    May 2008
    Location
    Makati, Philippines
    Posts
    234
    Rep Power
    6

    Default A small Question

    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
    Mind only knows what lies near the heart, it alone sees the depth of the soul.

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

    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?

  3. #3
    Join Date
    Aug 2008
    Location
    Saint-Petersburg, Russia
    Posts
    66
    Rep Power
    0

    Default

    Quote Originally Posted by Eku View Post
    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 ?

  4. #4
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

    Quote Originally Posted by ProjectKaiser View Post
    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.

  5. #5
    Join Date
    Aug 2008
    Location
    Saint-Petersburg, Russia
    Posts
    66
    Rep Power
    0

    Default

    I'm talking about something like:
    Java Code:
    create index my_table_phone_number on my_table(phone_number)
    ;)

  6. #6
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

    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. :)

  7. #7
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    6

    Default

    Quote Originally Posted by Eranga View Post
    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

  8. #8
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    Default

    Quote Originally Posted by fishtoprecords View Post
    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.

Similar Threads

  1. Urgent small code
    By karingulanagaraj in forum New To Java
    Replies: 7
    Last Post: 08-11-2008, 04:11 AM
  2. Small problem
    By ayoood in forum New To Java
    Replies: 2
    Last Post: 06-06-2008, 12:27 PM
  3. small error
    By ayoood in forum New To Java
    Replies: 23
    Last Post: 05-27-2008, 12:18 PM
  4. small issues with a program
    By jimJohnson in forum New To Java
    Replies: 6
    Last Post: 04-25-2008, 08:28 AM
  5. Small tennis simulation in Java
    By diego in forum New To Java
    Replies: 1
    Last Post: 12-02-2007, 01:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •