6 Replies - 742 Views - Last Post: 28 August 2013 - 12:27 AM Rate Topic: -----

#1 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 389
  • Joined: 04-January 11

Query Performance: Too Many Conditions in Select

Posted 26 August 2013 - 09:07 PM

I am query a large data (millions of data) so I already expected the query to run slower but I need to improve its performance and see to it that the query will run in the quickest possible.

I am using #tempTables and some @tableVariables as needed. The table to be queried is located in another server so it takes a lot of time executing the remote query.

Well, I need some tips to improve speed.

My query has lots of condition in the select statement. I have multiple nested Case-When clause in the select statement. Does it affect performance greatly if I have so many of these conditions in the select statement?

Is This A Good Question/Topic? 0
  • +

Replies To: Query Performance: Too Many Conditions in Select

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8850
  • View blog
  • Posts: 33,163
  • Joined: 12-June 08

Re: Query Performance: Too Many Conditions in Select

Posted 26 August 2013 - 09:16 PM

It's a bit hard to suggest improvements in speed without seeing it, right? Let alone knowing how long an average run takes, the environment, etc. I mean it would be like having you suggest concrete ways of knowing how to speed up my car without knowing a damn thing about it except that it is a card.

As it is - most major DBs have a 'profiler', 'execution plan', etc that you can turn on and see what is being generated where and how long.

http://msdn.microsof...y/ff650699.aspx

http://technet.micro...sql.105%29.aspx


Come on - you've been around here long enough to know how this whole question/details/answer dance goes.
Was This Post Helpful? 2
  • +
  • -

#3 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 389
  • Joined: 04-January 11

Re: Query Performance: Too Many Conditions in Select

Posted 27 August 2013 - 01:41 AM

I'm sorry for the vague question.

I read the two webpages you gave. Thanks for that. I wasn't able to try the first link since I can't find the Trace in New. I do not have full access in SQL.

The next link is something new to me. Wow! Thanks really.

I attached a small part of the execution plan. Though it is not complete since it cannot fit the size of the screen. I have read something about execution plans but I really am having a hard time understanding everything so I hope you can help me. I wish I could show you the whole plan. Is there a way I can save the execution plan?

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,446
  • Joined: 07-April 08

Re: Query Performance: Too Many Conditions in Select

Posted 27 August 2013 - 07:29 AM

First, none of your performance hit's are being affected by the image you sent. The only thing I can see that i would work on is the Hash Match areas. Can you post several images so we can see the entire execution plan? I want to see where the percentages are within your query.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5749
  • View blog
  • Posts: 12,556
  • Joined: 16-October 07

Re: Query Performance: Too Many Conditions in Select

Posted 27 August 2013 - 07:40 AM

Well, @tableVariables are always slow, particularly as they grow, so prefer #tempTables. Also, rather than just doing a select into temp table, use a create statement for it and add some indexes. Indexes on a temp table, while having a little overhead in creation, will make everything after that significantly better. Add the indexes after the data load.

If you're pulling stuff across a database link, try to pull as little as possible. That is, do as much filtering work on the native server before moving data across.

The impact of "multiple nested Case-When"... at that point, you really have to see the SQL. That kind of issue sounds like the SQL could be tuned a lot better.
Was This Post Helpful? 1
  • +
  • -

#6 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 389
  • Joined: 04-January 11

Re: Query Performance: Too Many Conditions in Select

Posted 27 August 2013 - 06:39 PM

The execution plan resulted for 8 queries in the stored proc. The first query costs 100% whereas the others all cost 0% each. The multiple case-when statements are not in the first query so I think that it does not affect the speed too much.

I am not getting the whole table from the remote server. I am only getting columns and rows that I need.

The following are the other screenshots, continuation of the first I attached:

Note: These are all screenshots of the first query.

The following screenshots are Left, Bottom, Bottom Left of the first screenshot from my previous post.

Attached image(s)

  • Attached Image
  • Attached Image
  • Attached Image

This post has been edited by November-06: 27 August 2013 - 06:42 PM

Was This Post Helpful? 0
  • +
  • -

#7 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,675
  • Joined: 30-January 09

Re: Query Performance: Too Many Conditions in Select

Posted 28 August 2013 - 12:27 AM

Your killer is the "Nested Loops (Left Outer Join)" in the first image, obviously given that it is 71% of the execution plan. In order to solve this, you'll have to determine what is occurring at that point of the plan. You should be able to hover over that plan node (maybe click on it, I can't remember) and SSMS will show a tooltip that describes what is going on. It will look something like this image. You need to look at the two parts down the bottom - Predicates and Output. This is where it gets tricky, as you need to apply indexes based on this information, to assist the DB engine when it selects the execution plan to use.

In order to determine the indexes, I'd need to see a screenshot of the tooltip, as well as the full query, so that I could advise what indexes I'd recommend.

Alternately, you could remove parts of the query, checking the execution plan each time, until you track down which particular JOIN is resulting in the 71% Nested Loops being chosen. This is how I tend to track down required indexes - simmer the query down to the one/two elements that are incurring the greatest cost in the overall plan. Once you've narrowed it down to a few JOINs, determining the optimal index is peanuts.

This post has been edited by e_i_pi: 28 August 2013 - 12:28 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1