0

I am working on performance tuning all the slow running queries. I am new to Oracle have been using sql server for a while. Can someone help me tune the query to make it run faster. 

Select distinct x.a,  x.b from 
from xyz_view x 
where x.date_key between 20101231 AND 20160430

Appreciate any help or suggestions

APC
  • 144,005
  • 19
  • 170
  • 281
SqlBrain
  • 43
  • 1
  • 11
  • Are you getting duplicate records without `distinct` keyword and Do you have any index on `date_key` field – Pரதீப் Apr 22 '16 at 18:05
  • maybe try using actual dates for the parameters: where x.date_key between to_date('20101231', 'YYYYMMDD') and to_date('20160430', 'YYYYMMDD') – tbone Apr 22 '16 at 18:19
  • @tbone - won't that be happening implicitly – Pரதீப் Apr 22 '16 at 18:20
  • @SqlBrain, I assume you have index on date_key field, you have not mentioned though – FLICKER Apr 22 '16 at 18:26
  • @Prdp not sure what you mean. I never assume something will happen implicitly if I can explicitly specify it (dates especially). Another example is when people use numbers instead of single quotes strings. Oracle may do an implicit conversion, but it won't be able to use an index built off a varchar field, for example. That said, its still unsure if Oracle will use an index on date_key field for this example, even if specifying the dates properly, it depends on how much data is to be pulled (Oracle may just full scan anyway). Explain plans would help too – tbone Apr 22 '16 at 18:31
  • 1
    ?? SQL Server or Oracle? I assume it's not both. –  Apr 22 '16 at 19:35
  • Are you really pulling back five years' worth of data? Out of how many years in total? How roes in total? How many rows in the result set? Is the table partitioned? Is it really a view? – APC Apr 23 '16 at 04:44
  • If you're not tuning SQL Server you should remove the tag. You're got a bunch of answers for tuning MSSQL which are not helpful for tuning Oracle and just wasting the responders time. Don't be confusing. – APC Apr 23 '16 at 04:46
  • the object **xyz_view** is view or table. If this is view then check the query written for the view. And if it is table then there's nothing very much to do but minimize the number of days of data to improve performance. – Mr. K Aug 25 '16 at 07:17

3 Answers3

2

First, I'd start by looking at why the DISTINCT is there. In my experience many developers tack on the DISTINCT because they know that they need unique results, but don't actually understand why they aren't already getting them.

Second, a clustered index on the column would be ideal for this specific query because it puts all of the rows right next to each other on disk and the server can just grab them all at once. The problem is, that might not be possible because you already have a clustered index that's good for other uses. In that case, try a non-clustered index on the date column and see what that does.

Keep in mind that indexing has wide-ranging effects, so using a single query to determine indexing isn't a good idea.

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • If this is Oracle and not MS SQL (just asked the OP to clarify) - there are no clustered indexes in Oracle. –  Apr 22 '16 at 19:40
1

I would also add if you are pulling from a VIEW, you should really investigate the design of the view. It typically has a lot of joins that may not be necessary for your query. In addition, if the view is needed, you can look at creating an indexed view which can be very fast.

J Greene
  • 261
  • 1
  • 7
  • And if that view calls other views, you can get into performance tuning hell! Been there, done that, have the t-shirt. – HLGEM Apr 22 '16 at 20:42
0

There is not much more you can do to optimize this query so long as you have established that the DISTINCT is really needed.

You can add a [NOLOCK] to the FROM clause if reading uncommitted pages is not an issue.

However you can analyze if the time is being inserted as well, and if so, is it really relevant, if not set the time to midnight this will improve indexes.

Biggest improvements I've seen is dividing the date field in the table into 3 fields, 1 for each date part. This can really improve performance.

domenicr
  • 352
  • 3
  • 14