Index - 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

Index

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-09-21 : 05:05:19

Hi

Table2 like master table and total records is 60491
Table1 is detail table and total records is 56087

No index both the tables

SELECT A.*,
b.*
FROM dbo.Table1 a
LEFT OUTER JOIN dbo.table2 b
ON a.Number=b.Number
and source = @source
Please advice which index is good practice. please advice why we need to use

Thanks a Lot friends

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 05:58:49

For someone with over 200 posts this is an awful question.

Please post the DDL for the tables involved. Depending on the columns and other things different indexes would be recommended.

Also -- do both tables have a primary key (existing clustered index) or are they both heaps?

Do you need to select EVERYTHING from each table? if you don't then we may recommend an index with an include clause.

What version of SQL server are you running?

Please post a decent sample query you want to optimise.

Or are you telling me that your production code is like that? SELECT A.*, B.*

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-09-21 : 06:11:12

Please post the DDL for the tables involved. Depending on the columns and other things different indexes would be recommended.

Also -- do both tables have a primary key (existing clustered index) or are they both heaps?
both the tables are heap

Do you need to select EVERYTHING from each table? if you don't then we may recommend an index with an include clause.

Only required columns like table1.number, table1.source, table2.emp_name, table2.address,table2.city

What version of SQL server are you running?
SQL SERVER 2005 service pack 2

Please post a decent sample query you want to optimise.



SELECT a.Number           ,
       a.source           ,
       a.xyz,
	   a.xyz,
	   b.emp_name,
	   b.emp_address,
	  b.city,
		b.xyz  		
FROM   table1 a
       LEFT OUTER JOIN dbo.table2 b
       ON		a.Number=b.Number
	   AND		a.SOURCE = 'VALUE'

Or are you telling me that your production code is like that? SELECT A.*, B.*
No only required columns

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 07:16:49

OK. Thanks for that.

1) Give the tables a decent primary key / clustered index.

The reason for this is that as the tables grow they will just be allocated wherever the engine pleases. Also when querying anything that can't use a complete covering index then you'll have to suffer through RID lookups.

You'll have to think about the best column(s) for the CI for each table.

Once the clustered index is in place I'd be tempted to go for....

2) Some sort of referential integrity.

The tables seem to join on the number column? Should this be a foreign key from one table to the other. Should one of the tables maybe have this column as its PRIMARY KEY?

Then....

3) For Table 1 an Index on number. (is this a good candidate for the primary key? What does this number represent) For this *specific* query then you may also want to cover the SOURCE column

4) For Table 2 - probably an index on number.

---------------------------------

Once that's all been done I'd imagine that you'd get index seek + key lookup (the columns in the SELECT list aren't part of an index) but that's maybe the best you could do. You don't seem sure which columns you need.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-09-21 : 09:51:39

Hi TC

Thanks a Lot

Shall i create an identity column in Table1 and create clustered index on that column.

Please advice is this good practise?

Thx

quote:
Originally posted by Transact Charlie

OK. Thanks for that.

1) Give the tables a decent primary key / clustered index.

The reason for this is that as the tables grow they will just be allocated wherever the engine pleases. Also when querying anything that can't use a complete covering index then you'll have to suffer through RID lookups.

You'll have to think about the best column(s) for the CI for each table.

Once the clustered index is in place I'd be tempted to go for....

2) Some sort of referential integrity.

The tables seem to join on the number column? Should this be a foreign key from one table to the other. Should one of the tables maybe have this column as its PRIMARY KEY?

Then....

3) For Table 1 an Index on number. (is this a good candidate for the primary key? What does this number represent) For this *specific* query then you may also want to cover the SOURCE column

4) For Table 2 - probably an index on number.

---------------------------------

Once that's all been done I'd imagine that you'd get index seek + key lookup (the columns in the SELECT list aren't part of an index) but that's maybe the best you could do. You don't seem sure which columns you need.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 10:18:55

Are you saying a.number and b.number are not the keys?

We need to see the DDL

And what table is source in?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 10:31:15

quote:
Originally posted by WoodHouse

Hi TC

Thanks a Lot

Shall i create an identity column in Table1 and create clustered index on that column.

Please advice is this good practise?

Thx

No I'm not suggesting that you just arbitrarily make a new column for your primary key.

The primary key should uniquely identify a row in the table. Ideally it should also be something pertinent to the data if possible. An IDENTITY column certainly provides a UNIQUE value for each row but it has nothing to do with the data. That's why it commonly called a SURROGATE primary key if used in that way.

PLEASE POST THE DLL of the two tables involved.

Do you KNOW what the data looks like in the tables?

number seems like it could be a good candidate for a key. However we don't know your data so we can't really advise further.

Have you got any experience with database design? You seem to know what the terms are but not why you'd do certain things.

I don't mean to offend you but you are asking the sorts of questions that would scare me if one of our developers were asking them.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 10:38:39

quote:
Originally posted by Transact Charlie

[quote]Originally posted by WoodHouse
I don't mean to offend you but you are asking the sorts of questions that would scare me if one of our developers were asking them.

Scared? I'd get the bat out

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources