Slow Query and High IO

Question

Post reply

Slow Query and High IO

David Kranes

SSCrazy

Points: 2523
More actions
January 4, 2008 at 12:08 pm

#62998

Hello, the following query generates a lot of IO and pegs the CPU.
Select pt3.TRANS_NUM
From POS_TRANSACTION pt3, POSTRANS_SKU pts
Where pt3.STORE_KEY = pts.ORIG_STORE_KEY and
pt3.REGISTER_NUM = pts.ORIG_REGISTER_NUM and
pt3.TRANS_NUM = pts.ORIG_TRANS_NUM
go
The query plan is attached. Any ideas on how to improve? Thank you.
David
Best Regards,
~David

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you post the definitions of the two tables and all the indexes on them please.

All I can see from the exec plan is that you have two index scans. Might be fixable, might not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

It would appear from things like "[Expr1002]=Convert([pt3].[STORE_KEY]) " found in the execution plan that 1 or 2 things may really be causing the performance problems...

The first thing is that that part of the execution plan seems to indicate that one or more of the columns in your join are not the same datatype in each table. I'd fix that first because it's just about impossible to use an index properly in the face of implicit conversions.

The other major problem is that the rowcount in parts of the plan exceeds 100 MILLION!!!! What that usually means is that your criteria is probably not sufficient to prevent an accidental cross join... it also means that the query will not fit in memory and it's going to beat TempDB to death on the I/O side of the house...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Kranes SSCrazy Points: 2523 More actions · Answer 3

Gail/Jeff, attached is the DDL for both the tables and their indexes and constraints. Thank you for your time.

David

Best Regards,

~David

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

I guess we also need to know the number of rows in the two tables, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Jeff Moden (1/5/2008)
I guess we also need to know the number of rows in the two tables, please.

I guess execution plan shows the numbers:

3.5631036E+7 in [pos_transaction] and 1.0039982E+8 in [postrans_sku]

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Then, I guess I don't understand the need for the join... the query is just returning every transaction number that also has a match in the SKU table... multiple times!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Heh... at least throw a GROUP BY on it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

just a little remark:

If you post ddl with objects using UDT's, also post the UDT's ddl.

as allready stated, you just join all rows of both tables without a filter.

What's the clusterratio for both objects ? That will have massive impact towards the IO overhead.

Find out why it performs the implicit conversion.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

David Kranes SSCrazy Points: 2523 More actions · Answer 9

This query is just a portion of the much bigger query. This is the portion that is causing the problem. I have attached 2 queries along with the query plans. One runs fast and the other which containt the following statement runs very slow.

(SELECT pt3.trans_num

FROM pos_transaction pt3

WHERE pt3.store_key = pts.orig_store_key

AND pt3.register_num = pts.orig_register_num

AND pt3.trans_num = pts.orig_trans_num)

With out this statement, the query runs fine. Thanks you all for your input. I will investigate the implicit conversion as well.

David

Best Regards,

~David

David Kranes SSCrazy Points: 2523 More actions · Answer 10

After further investigation I see that the store_key in the pos_transaction table is a smallint while the orig_store_key column in the postran_sku is an integer. Could that be causing this issue?

David

Best Regards,

~David

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Yes... and sorry I missed that... I verified that Store_key was the same in both tables and didn't even look Orig_Store_key.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Kranes SSCrazy Points: 2523 More actions · Answer 12

Thank you Jeff, I will pursue this and let you know how it turns out.

David

Best Regards,

~David

David Kranes SSCrazy Points: 2523 More actions · Answer 13

Jeff, that fixed it. I thank you and all who responded for your patients and expertise in helping me solve this issue. Have a great day!

🙂

David

Best Regards,

~David

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Perfect... thanks for the feedback.

Yeah, anytime you have a join on different datatypes, it must do an implicit conversion to do the join... best you can get out of that is an INDEX SCAN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)