Joins and where statements - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Joins and where statements

Author

Topic

bornecw
Starting Member

6 Posts

Posted - 2010-05-06 : 15:59:22

Hello,
I have a query that I am trying to imporve the speed of.
select 0, p.product_id, p.handheld_desc,coalesce(ps.unit1qty_on_hand, 0), ps.unit1qty_physical, Convert(Int, p.product_id) as SORTID
from product p
left join product_stock ps on '8547' = ps.loc_id and p.product_id = ps.product_id
order by SORTID

If I make that:
select 0, p.product_id, p.handheld_desc,coalesce(ps.unit1qty_on_hand, 0), ps.unit1qty_physical, Convert(Int, p.product_id) as SORTID
from product p
left join product_stock ps on p.product_id = ps.product_id
where '8547' = ps.loc_id
order by SORTID

the results are different. Can you explain to me the difference? Also, any optimization tips would be appreciated.

Clay Borne

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-06 : 16:16:58

Check out this article for why they are not equivalent when using an OUTER JOIN such as LEFT JOIN: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

They are equivalent for an INNER JOIN.

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

Subscribe to my blog

bornecw
Starting Member

6 Posts

Posted - 2010-05-06 : 18:04:49

There are 700 rows in product and 300 rows in product_stock

Just running:
select count(*)
from product p
left join product_stock ps on '8547' = ps.loc_id and p.product_id = ps.product_id

takes 1 minute. ps.loc_id, ps.product_id, and p.product_id are indexed. What else can I do to speed this up?

Clay Borne

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-06 : 18:07:03

Have you checked the execution plan? Is it using the correct indexes (the optimizer doesn't always pick the right one)?

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

Subscribe to my blog

bornecw
Starting Member

6 Posts

Posted - 2010-05-07 : 08:56:41

Tara,
It's strange, the table is destroyed, created, records imported, indexes built, and compacted every day. I have a script that builds indexes:
create unique index uq_product_stock on product_stock (loc_id, product_id)

But the query was running like there were not any indexes. So I destroyed the uq_product_stock, and did this:
create unique index uq_product_id on product_stock (product_id)
create unique index uq_loc_id on product_stock (loc_id)

After that the query came back in 1 sec as opposed to 1 min. Can you provide any insights into this? I'm not entirely familiar with indexes with multiple fields. Thanks!

Clay Borne

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-07 : 09:55:50

is ps.locID an int column? If so, remove the quotes around the value in the query and put the index back to the 2-column (it's more useful for this query).

I'm guessing that you had implicit conversion which completely prevents index seeks. By splitting the index, you allow SQL to seek on the join column. However removing the implicit conversion and putting back the 2-column index should be even better.

--
Gail Shaw
SQL Server MVP

bornecw
Starting Member

6 Posts

Posted - 2010-05-07 : 10:18:05

Thanks for responding,
ps.locID is a nvarchar(6) column. Would removing the implicit conversion still help? If so, how do you remove it?

Clay Borne

bornecw
Starting Member

6 Posts

Posted - 2010-05-07 : 10:19:30

BTW this is Sql Compact 3.5

Clay Borne

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-07 : 11:07:59

No, if it's a nvarchar, leave the quotes.

I know nothing about compact edition, so can't help any further. It would have been useful if you'd stated that in your initial post.

--
Gail Shaw
SQL Server MVP

bornecw
Starting Member

6 Posts

Posted - 2010-05-07 : 14:04:35

Thank you, I learned from you, so don't fret.

Clay Borne

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources