slow running Query
-
I have a query and a view. Query calls the view and executes the output.
Query:
SELECT a_day,o_id,s_id FROM v_CC_view WITH(NOLOCK) WHERE ((o_id='12A1F0D' OR p_id='12A1F0D2') ) ORDER BY a_day
V_CC_view is a view consists of following:
CREATE VIEW v_CC_view as
select obj.a_day,obj.o_id,obj.s_id, obj_cc.p_id
from obj LEFT OUTER JOIN dbo.ob_cc
ON dbo.obj.a_id = dbo.obj_cc.a_id
WHERE
ob_t_id IN (17, 18) AND (s_id <> 2 OR comp<> 1)
The query execution plan of the query is like this:
|--Sort(ORDER BY
[obj].[a_day] ASC))|--Parallelism(Gather Streams)
|--Filter(WHERE
[obj].[o_id]='12A1F0D' OR [obj_cc].[p_id]='12A1F0D2'))|--Hash Match(Left Outer Join, HASH
[obj].[a_id])=([obj_cc].[a_id]), RESIDUAL[obj].[a_id]=[obj_cc].[a_id]))|--Bitmap(HASH
[obj].[a_id]), DEFINE[Bitmap1004]))| |--Parallelism(Repartition Streams, PARTITION COLUMNS
[obj].[a_id]))| |--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[DBTEST].[dbo].[obj]) WITH PREFETCH)| |--Index Seek(OBJECT
[DBTEST].[dbo].[obj].[IX_obj_today1]), SEEK[obj].[ob_t_id]=17 OR [obj].[ob_t_id]=18), WHERE[obj].[|--Parallelism(Repartition Streams, PARTITION COLUMNS
[obj_cc].[a_id]), WHEREPROBE([Bitmap1004])=TRUE))|--Index Scan(OBJECT
[DBTEST].[dbo].[obj_cc].[IX_obj_cc_today1]))there are two tables : obj and obj_cc
In obj: a_id is clustered,Unique,PK index and in Obj_cc table acc_id is clustered, Unique PK Index.
IX_obj_today1 index has (ob_t_id,s_id,comp)
IX_obj_cc_today1 has(a_id,p_id)
This above query executed very slowly.
Can you give me alternative query solution or view solution or index recommendations for above query/view ?
Please help me as soon as possible. if anything you need let me know.
AKP
-
Hi there,
Please send us the complete table structure to be able to tell you what is wrong (since I'm not clear on which table the following field belongs
"ob_t_id ,s_id and comp".
I think your biggest problem is in the index scan that is done on table obj_cc , that is your biggest problem, you need to convert it to an index seek.
As a general comment, your query doesn't seem to be sargable, you are using a lot of "Or" "<>" and "in" which are not good for query performance, if it is
possible to convert them to "and" , "=" ">" "<" this would be a lot better.
Some things that you can think of to replace the or is to use union all, for example you can convert
SELECT a_day,o_id,s_id FROM v_CC_view WITH(NOLOCK) WHERE ((o_id='12A1F0D' OR p_id='12A1F0D2') ) ORDER BY a_day
to
SELECT a_day,o_id,s_id FROM v_CC_view WITH(NOLOCK) WHERE o_id='12A1F0D'
union all
SELECT a_day,o_id,s_id FROM v_CC_view WITH(NOLOCK) WHERE p_id='12A1F0D2'
-
Fields "ob_t_id ,s_id and comp" are in obj table which is a biggest table in our database.
Actually can you tell me how can i replace <> with other saragable operators.
Send me your answers soon please.
-
- It's a view
- It does an outer join
- It scans which indicates less than useful indexes
- it uses not equals which will likely cause scans
You need to improve the indexes to optimse the query. There's no issues with AND and OR , you just have to optimse the query.
- Read BOL
- Buy Inside SQL Server Book
- Attend training course
- Search for articles on indexing
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Please also give us an estimated number of rows per table. And a more complete table structure, so that we can help you optimize the query, and improve the indexes. One thing, that I noticed, that already slow down performance, is the Order by clause, which can potential take up your performance time, since a_day is not indexed.
MCTS
-
Hi There,
There are many ways that you can convert <> , but it depends on the values that you have in each field, for example in your where clause you have put (comp<> 1) , if comp takes positive values larger than zero, then you can convert it to (comp > 1).
But don't get me wrong, the target here is not converting all operators to be sargable, the main target is to allow SQL server uses the best index to execute your query, since if only a part of the query is sargable and it directs SQL server to use the best index this will be fine.
Since you are the one who knows your database better and you know the size of each table, the best thing to do is:
1. Imagine you are SQL server and think which index would make you reach the result faster.
2. If the index doesn't exist then create it.
3. Tune your query to use that index and does an "Index Seek" on that index (not an index scan).
4. You can force SQL server to use that index (by using Index hint) if you failed to tune the query to use the index itself (but this is not recommended).
Note: Colin mentioned that "AND" and "OR" doesn't affect performance, I agree on the "AND" (I didn't say it affect performance), but "OR" can hurt performance, so if you can convert it , this would be better.
Good Luck
-
If the result set is more than a few rows chances are you'll get index scans rather than seeks - this is why such questions about tuning queries are difficult to answer in a post - sitting in front of the server and viewing the data, query plan and results with io stats etc. etc. allows a much better success rate. If you can get your OR into a covered index you may replace the intersection and take out a scan , generally each OR requires a seperate scan of the table/index to satisfy the query, but not always, and sometimes a simple query will use an intersection of two indexes to combine the result set.
I do this for a living and to be honest it's a bit of a black art, for every "rule" there's always an exception, I like the term "It Just Depends".
Index hints are best avoided, however, I've seen Itzik Ben-Gan do some cool stuff with the xml query plans in 2005 to optimse queries.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply