Adding a column affects query performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding a column affects query performance

Author

Topic

bgutschow
Starting Member

7 Posts

Posted - 2008-09-30 : 14:00:57

I have a query to return a set. I'm looking back 10 years to find the first time an item was sold and include all subsequent sales. If is was sold more than 10 years ago, I don't want to include it in the set.

If I run the query without the fields inv_item.qty_invoiced and inv_item.price, it returns my set (321000 rows) in 52 seconds. If I include those fields, it runs a long time (I killed it after 10 minutes).

I'm confused to why it would take so long given the fact I have the table joined in no matter which way I run it. I just want to return the additional columns.

Any suggestions would greatly be appreciated.

Thanks,
Bill Gutschow
Curbell Inc.

select top 100 percent inv_item.item,inv_hdr.inv_date,inv_hdr.inv_num,
inv_hdr.inv_seq,YEAR(inv_hdr.inv_date) as yi,
MONTH(inv_hdr.inv_date) as mi ,item.family_code,inv_item.qty_invoiced ,inv_item.price
from inv_hdr
join inv_item on inv_hdr.inv_num = inv_item.inv_num
and inv_hdr.inv_seq = inv_item.inv_seq
join item on inv_item.item = item.item
where inv_date > DATEADD (yy, -10, GETDATE())

and inv_item.item not in
(select top 100 percent inv_item.item
from inv_hdr
join inv_item on inv_hdr.inv_num = inv_item.inv_num
and inv_hdr.inv_seq = inv_item.inv_seq
where inv_date <= DATEADD (yy, -10, GETDATE()))
order by item.family_code,inv_item.item,inv_date

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 14:07:11

Show us your indexes on those tables. Compare the execution plan of both queries, what are the differences?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bgutschow
Starting Member

7 Posts

Posted - 2008-09-30 : 15:11:37

Tara,
Here is the index info. I'm relatively new at this so excuse my lack of in-depth knowledge. I've only included the indexes that I believe to have an effect.

inv_hdr:
Primary Key, Clustered, inv_num,inv_seq
Other Indexes:
co_num
cust_num,cust_seq
inv_date,
rowpointer

inv_item:
Primary Key, Clustered, inv_num,inv_seq,inv_line,co_num,co_line,co_release
Other Indexes:
item
rowpointer

item:
Primary Key, item
Other Indexes:
family_code
rowpointer

I have run both queries with the Execution Plan but I don't know what to look for. Can you point me in the right direction?

Thanks,
Bill

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 15:14:58

Well what are the differences in the execution plans. If performance is difference, then the query optimizer is probably picking a bad plan or you don't have a good index to cover the columns returned.

Can you post pics of the two execution plans?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bgutschow
Starting Member

7 Posts

Posted - 2008-09-30 : 16:05:16

How do I post pictures?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 16:14:11

Submit to some web server and then post the links.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-30 : 16:34:12

Why do you have that whole "and inv_item.item not in" part there? That seems completely redundant, it is just repeating the criteria you have already applied in the first part of your WHERE clause and it is needlessly complicating your entire SELECT and probably making it twice as slow.

- Jeff
http://weblogs.sqlteam.com/JeffS

bgutschow
Starting Member

7 Posts

Posted - 2008-09-30 : 19:18:41

Tara,

I was unable to get a picture, but ran it in text. I hope that is good. You can find it here.

Thanks!
Bill
[url]http://rcpt.yousendit.com/610978671/dc98edfa0fb0699c8233653151d3bd4b [/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources