Query performance

I have a query as follow

Select product.name, vendor.name from product join vendor on product.vendor_key = vendor_key

Vendor_key is the primary key on Vendor Table

If I want to increase the performance should I use Stored Procedure or create index for the vendor_key on Product Table

The first thing to check is that both tables have indices on the linking columns.

Vendor(Vendor_Key) is a PK, so it is automatically indexed.

Does Product(Vendor_Key) have an index?

|||Product(Vendor_key) has no index|||Performance should improve if you add an Index for Product(Vendor_Key).|||

A sproc will only help if you call the query repeatedly. Also, if you are calling it remotely with ADO and are using a bad cursor type.

It seems that you should have vendor.vendor_key as the last part of that query, right?

Also, if you are doing read-only activity, consider using (NOLOCK) hint for both tables, which provides a small performance gain and also improves concurency.

|||

Also, the index will really help the performance if it has product.name as an Included column.

As always, when creating secondary indexes, you should consider the added cost for insert/update/delete operations.

|||you means to add the Product(name) index or Product(name, vendor_key) index. Is it every time I use the join statement, then I will add the index on the foreign key column to increase the performance.