SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Multiple indexes using same column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CtrlAltDel
Starting Member

Australia
17 Posts

Posted - 06/09/2004 :  12:52:22  Show Profile  Visit CtrlAltDel's Homepage  Reply with Quote
I have a table defined something like this :

CREATE TABLE test (
site_ref VARCHAR(4) NOT NULL,
acc_no VARCHAR(8) NOT NULL,
payroll_no VARCHAR(7) NOT NULL,
ts_id INT NOT NULL,
we_date SMALLDATETIME NOT NULL
)


Now suppose my lookup tables were :

CREATE TABLE account (
site_ref VARCHAR(4) NOT NULL,
acc_no VARCHAR(8) NOT NULL,
acc_name VARCHAR(50) NOT NULL
)

CREATE TABLE payroll (
site_ref VARCHAR(4) NOT NULL,
payroll_no VARCHAR(7) NOT NULL,
payroll_name VARCHAR(50) NOT NULL
)

CREATE TABLE timesheet (
site_ref VARCHAR(4) NOT NULL,
ts_id INT NOT NULL,
we_date SMALLDATETIME NOT NULL,
hours NUMERIC(9, 6) NOT NULL
)


account has a composite primary key of site_ref and acc_no, payroll has a composite primary key of site_ref and payroll_no, and timesheet has a composite primary key of site_ref, ts_id and we_date. If I were to index test, so that each foreign key was indexed (so site_ref and acc_no in a composite index, site_ref and payroll_no, and site_ref, ts_id and we_date), would there be a performance penalty by having the test.site_ref field appearing in each index?

--
David Keaveny

X002548
Not Just a Number

15586 Posts

Posted - 06/09/2004 :  13:22:36  Show Profile  Reply with Quote
Great post...I love when we get DDL..

I don't understand why site_ref is in the parental tables though...

But why not test it...


USE Northwind
GO

CREATE TABLE account99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, acc_name VARCHAR(50) NOT NULL
	, PRIMARY KEY(site_ref,acc_no)
)
GO

CREATE TABLE test99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, payroll_no VARCHAR(7) NOT NULL
	, ts_id INT NOT NULL
	, we_date SMALLDATETIME NOT NULL
	, PRIMARY KEY (site_ref, acc_no, payroll_no, ts_id, we_date)
	, FOREIGN KEY (site_ref, acc_no) REFERENCES account99(site_ref, acc_no)
)
GO

sp_help test99
GO
--[CTRL+K] to turn on Show Execution Plan

SELECT a.* FROM test99 t JOIN account99 a ON a.site_ref = t.site_ref AND a.acc_no = t.acc_no
GO

CREATE INDEX test99_ix1 ON test99(site_ref, acc_no)
GO  

SELECT a.* FROM test99 t JOIN account99 a ON a.site_ref = t.site_ref AND a.acc_no = t.acc_no
GO

--   Both are still an index scan 1st one because of the order of the columns
-- , second because there's no other predicate and it would still need to scan
--

DROP TABLE test99
DROP TABLE account99



Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/09/2004 :  13:31:51  Show Profile  Reply with Quote
Or we could go for some index intersection


USE Northwind
GO


CREATE TABLE account99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, acc_name VARCHAR(50) NOT NULL
	, PRIMARY KEY(site_ref,acc_no)
)
GO

CREATE TABLE test99 (
	  site_ref VARCHAR(4) NOT NULL
	, acc_no VARCHAR(8) NOT NULL
	, payroll_no VARCHAR(7) NOT NULL
	, ts_id INT NOT NULL
	, we_date SMALLDATETIME NOT NULL
	, PRIMARY KEY (site_ref, acc_no, payroll_no, ts_id, we_date)
	, FOREIGN KEY (site_ref, acc_no) REFERENCES account99(site_ref, acc_no)
)
GO

CREATE TABLE payroll99 (
	  site_ref VARCHAR(4) NOT NULL
	, payroll_no VARCHAR(7) NOT NULL
	, payroll_name VARCHAR(50) NOT NULL
	, PRIMARY KEY (site_ref, payroll_no)
)
GO

CREATE INDEX test99_ix1 ON test99(acc_no)
CREATE INDEX test99_ix2 ON test99(payroll_no)
GO  

SELECT a.* 
  FROM test99 t 
  JOIN account99 a 
    ON a.site_ref = t.site_ref 
   AND a.acc_no = t.acc_no
  JOIN payroll99 p 
    ON p.site_ref = t.site_ref 
   AND p.payroll_no = t.payroll_no
 WHERE a.acc_no = '1'
   AND p.payroll_no = '1'
GO

DROP TABLE test99
DROP TABLE account99
DROP TABLE payroll99
GO




Brett

8-)
Go to Top of Page

CtrlAltDel
Starting Member

Australia
17 Posts

Posted - 06/10/2004 :  07:03:40  Show Profile  Visit CtrlAltDel's Homepage  Reply with Quote
quote:
I don't understand why site_ref is in the parental tables though...

Well the site_ref column indicates which server the data was originally received from (this database is an aggregate of data from our UK and Eire servers), so it has to appear in almost every table, as, for example, the same account number could be used in UK and Eire for different accounts.

Since site_ref is not at all selective (only 3 possible values ATM), I should properly have made it the last column in each index.

Hmm, I tried your index intersection idea, and if I leave site_ref out of test99_ix1 and test99_ix2, the QO does a clustered index seek on test99; however, if I add site_ref to the indexes, it uses a clustered index scan. To me, this suggests that I don't want site_ref in anything other than the primary key - am I reading this right?

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000