• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How can I make this query take less time?

 
Village Idiot
Posts: 484
jQuery Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to check soem data within a certain data and that has a value in one of the columns. Trying something like this:



but this is taking a long time to run. Something like 30-45 minutes. Even if I try to change it to look at values within one day:



it is taking a while (right now it is on 5 minutes and counting...)

So am I doing something noobish that is making this query take way longer than it should (something to do with how I specify date range I would guess - although I have tried variations on how I do this)? Any tips to make this faster would be great.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I bet that your second query will take more or less the same time. Your database is probably "fullscanning" (reading all of) the table to get the answer. The second run might be faster though, if parts of the table are already in cache.

How many rows satisfy the WHERE condition of your query? If it is just a fraction (say, up to 5%) of all rows in the table, then adding an index on the creation column (perhaps combined with the attribute1 column) should help.

Details depend on the database you're using. In some databases (eg. Oracle) it would be advisable to gather optimizer statistics after you add the index, so that the optimizer can come up with optimal execution plans.

There is not much to improve in your query. You should avoid "SELECT *" and enumerate only the columns you need in your query - it might save some network bandwidth by not fetching columns you don't need (and its a good thing to do anyway, as the list of selected columns becomes fixed and self-documented), but that's about all.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You must have a tremendous amount of data in your table. If it spans a wide time range and adding the index doesn't help enough, you might look at partitioning the table by year/month.
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try executing the dates at last and null validation ast first .. this way it might improve performance based on number of null records.



if still problem is there, go for inner queries checking null first and result records with date.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just to make sure, creation_date is of type datetime? But either way, as suggested, make sure there's an index on it.

Try executing the dates at last and null validation ast first .. this way it might improve performance based on number of null records.


That's unlikely. The query optimizer will make that change automatically if it deems it advantageous.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried generating an execution plan? That will tell you if you are hitting indices and show you where other indices might be sensible.
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Creating an index will help to an extent. But looking at the query, even with the index, the query might take a lot of time to bring out the results (the time taken varies depending on the amount of data the DB has to process for your query predicates).
The best way to make this query run faster is provide any other good predicate to the where clause which brings out only the required data .
 
Matt Kohanek
Village Idiot
Posts: 484
jQuery Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for all the tips everyone - turns out not much can be done as you say - I just have hundreds of thousands of records to go through so there is not much to be done
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As Jeanne suggested partitioning may improve performance.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Matt Kohanek wrote:Thanks for all the tips everyone - turns out not much can be done as you say - I just have hundreds of thousands of records to go through so there is not much to be done



  • Are you using Oracle? If so, then hundreds of thousands of records is not all that much data, assuming you have a reasonable DB server.
  • You might also be doing an implicit type conversion on your BETWEEN values i.e. they are character strings but you are apparently comparing them with a date value. Try using TO_DATE on the strings to make sure the values are actually dates before they get compared with the table column.
  • An index on your CREATION_DATE column would definitely speed this query up, assuming you would normally expect to fetch only a small proportion of the records.
  • Try running the query with each condition separately and see which one seems to run faster. This should tell you which condition is slowing the original query down.
  • As others have suggested, don't use "SELECT *" if you can avoid it, just select the columns you need. Try running the query by just fetching one column initially to see if that makes a difference.
  • Try "SELECT COUNT(*) FROM..." or use "...AND ROWNUM <= 10" to reduce the amount of data you actually pass back from the query. If this makes it return much faster, then the query is finding the data quickly but taking a long time to pass it back.
  • And run an explain plan to see where the query is spending its time.
  • How are you executing this query? Try running it directly in your SQL environment e.g. SQL*Plus, to remove any overhead due to ORM issues etc.

  • If none of that works, talk to your DBA and see if they have any bright ideas.
     
    Martin Vashko
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Though your advice is very valuable, I'd have a small clarification:

    chris webster wrote:

  • Using "IS NULL" or "IS NOT NULL" disables the use of an index on a column, even if you had an index on it.

  • Definitely not, at least in Oracle. Composite indexes do contain null values, assuming at least one of the columns in the index is non-null. Only records with nulls in all columns in the index are not indexed. In this case a composite index would help, because the date column would not be null.

    Moreover, IS NOT NULL queries can be answered using even a simple index, because all the rows matching the condition will be in the index. If the column contains a lot on null values and just a few non-nulls, optimizer will certainly use the index to get these rows.

    More NULL handling tricks can be pulled by using function based indexes, but this is beyond the scope of the original question.
     
    chris webster
    Bartender
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Martin Vajsar wrote:Though your advice is very valuable, I'd have a small clarification:

    chris webster wrote:

  • Using "IS NULL" or "IS NOT NULL" disables the use of an index on a column, even if you had an index on it.

  • Definitely not, at least in Oracle....



    Thanks for clarifying that - I've removed the point from my post to avoid confusion!
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic