This is a mirror of official site: http://jasper-net.blogspot.com/

Why does this query consumes so much CPU?

| Sunday, May 1, 2011
Recently I worked with a customer who reported a slow running query.  Let me simplify this to illustrate the problem.  

There are two tables  t1 (pk int identity primary key, c1 int, c2 int, c3 int, c4 varchar(50)) and  t2 (pk int identity primary key, c1 int, c2 int, c3 int, c4 varchar(50)).  Each table has about 10000 rows. 

But the following query is very slow.  

select  COUNT (*)  from t1 inner join t2 on t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3 and t1.c4<>t2.c4

This query runs over 30 seconds and consumes over 30 seconds of CPU.  The query actually returns 0 rows.  With two tables of size of 10,000 each, this seems to be unreasonable. 

When investigate CPU consumption by a query, we normally look at a few things.  First, we look at how many logical reads this query has done.  Secondly, we look at the plan to see how many rows are processed by each operator.

But when we track logical reads via profiler trace (reads column), we see very low logical reads (less than 60).   When we look at the plan, the number of rows processes are not that many either.   The partial execution plan is shown below.

5657.image_5F00_thumb_5F00_24499B2D.png

Posted via email from Jasper-net

11 comments:

Anonymous said...

Great delivery. Outstanding arguments. Keep up the good work.


Feel free to surf to my blog post :: http://www.gobayuenergy.com/profile/91527

Anonymous said...

Sometimes we can't imagine that sports are so funny but we really enjoyed when we look at funny sports pictures, moments and videos. If you have your own website and want to use these Funny Animated Gif Images on the home page of your website then you can easily take these images and place them on your websites. The moment you think of funny video the first name that probably comes to your mind is You - Tube.

My webpage; funny pictures and quotes for myspace

Anonymous said...

Take a look at your schedule and come of with solutions ahead
of time for lunches and dinners out, this will give you a game plan to follow.
According to experts, water and juices from various kinds of fruit are essential part of good diet.

Take note also that you know how to perform the exercise well to avoid sickness and injuries in the
future.

my web blog: canaldemusica.com

Anonymous said...

The new tablet would be a further extension of these concepts and yet another step into the future of computing for
Apple. Researches have also found the Broccoli also contains necessary
enzymes such as myrosinase in it. Committee on Gulf War and Health: Health Effects of Serving in the Gulf War, Update 2009.



Feel free to visit my web page :: Latest Daily News

Anonymous said...

These fitness tips in above some basic ideas for keeping fit and healthy
body. One last thing that isn't mandatory but recommended is to set up a Pay - Pal account to process your payments. Emotion linked to a definite objective is a strong mixture.

Feel free to visit my web-site :: http://Wartaggaming.com/index.php?do=/profile-1919/info

Anonymous said...

Anyone who needs a new idea to stay motivated should definitely
try this. If you weigh yourself everyday or every other
day and see that you've gained weight, your motivation may go down and you'll be likely
to give up. Having some company can also make the fitness
session more fun.

Also visit my blog http://www.Fffin.com

Anonymous said...

My brother suggested I may like this website.
He was once entirely right. This publish actually made my day.
You can not consider simply how so much time I had spent for this info!
Thanks!

Feel free to surf to my web site ... http://www.biotechnologyreview.net/ar/food-biotechnology.php

Anonymous said...

Sometimes funny pictures are created automatically, and now need a little
time to our own. It seems like the internet today is almost entirely full of
nothing but funny pictures and videos that people use to pass their days.
Many of the paintings have a tendency to use
a signature in order to improve a funny message
that's shown in the photo.

Have a look at my site: lol pictures

Anonymous said...

They aim at miming various good qualities of the counterparts of the assumed celebrity.
We realize that we don't need to listen to outside authorities; we are not guided by our internal authority. Due to the recent horrifying terror attacks and other such couple of events people want to be aware and alerted of the whereabouts of the nation.

Feel free to surf to my page ... Latest Daily News

Anonymous said...

Generally, they're visuals that will be revised by means of enthusiast combined with listed on numerous comedy internet pages. For example, we often do some driving trains to encourage the students to speak a few numbers in turn. The world will suddenly look a much better place to live in.

Feel free to surf to my website; click the next webpage

Anonymous said...

Hello, its fastidious piece of writing concerning media print, we all be
familiar with media is a impressive source of information.



My blog post - fitness tips women