Performance of query syntax on multi-multi link

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)

Performance of query syntax on multi-multi link

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-03 : 15:58:55

I am new to SQL Server. I'd like to know whether there are any performance implications of using the following SELECT syntaxes. I am getting the same result, but don't have enough data to test performance. I am getting all Contacts linked to a particular Account via the TRLNK001 linking table.

Syntax 1:

Select RTrim(CONTACT.LName) + ', ' + CONTACT.FName AS 'Contact Name' FROM CONTACT, ACCOUNT, TRLNK001
Where ACCOUNT.FormID = TRLNK001.ACCOUNT AND CONTACT.FormID = TRLNK001.CONTACT AND TRLNK001.ACCOUNT=2
Order By CONTACT.LName

Syntax 2:

SELECT RTrim(CONTACT.LName) + ', ' + CONTACT.FName AS 'Contact Name' FROM CONTACT
JOIN TRLNK001 ON CONTACT.FormID = TRLNK001.CONTACT
JOIN ACCOUNT ON ACCOUNT .AcctID = TRLNK001.ACCOUNT
WHERE ACCOUNT .AcctID = 2
Order By CONTACT.LName

By the way, I couldn't post a new topic in the Developer section. It kept complaining about invalid username and password. Is this just broken or the section requires a different logon?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-03 : 16:05:35

The queries are identical. Use the second one though as that's the preferred syntax. Syntax1 may be deprecated in future versions.

Make sure that all join columns are indexed.

Your invalid userid/pwd thing is probably just a fluke. Try closing and reopening your browser.

You might consider just returning LName, FName, then doing the formatting in your application.

Tara Kizer

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-07 : 11:09:30

Tara, how would you index the link table (TRLNK001)? Would you have one index

CONTACT+ACCOUNT

or two indexes

CONTACT
ACCOUNT

or something else?

I am querying tables from both sides.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-07 : 12:40:24

I would use two indexes unless its a composite foreign key, then I'd use a composite index.

Tara Kizer

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-08 : 18:10:21

quote:
Originally posted by tduggan

I would use two indexes unless its a composite foreign key, then I'd use a composite index.

Do I have a "composite foreign key?" Pardon my ignorance of the SQL Server terminology. Here's what I have now:

CONTACT table:
Index on field 'ID'

ACCOUNT table:
Index on field 'ID'

TRLNK001 table field:
CONTACT (containing the Contact's ID)
ACCOUNT (containing the Account's ID)
Index composed of CONTACT+ACCOUNT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-08 : 18:15:38

I don't think that you do, but... Right click on the database in Enterprise Manager. All tasks..generate SQL script. Select show all, then click on all tables involved in this query to be scripted. Go to the Options tab, select script PKs, FKs, etc... Post that code here. While you're at it, script the indexes too.

A composite foreign key is a foreign key constraint that has more than one column in it. It appears that you have two foreign key constraints that each point to one column.

Tara Kizer

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-09 : 11:16:43

Unrelated problem: When previewing or clicking OK in Generate SQL Scripts dialog, I am getting an error:

[SQL-DMO]CreateFile error on 'SERVERNAME.DATABASENAME.DP1'.

What is .DP1? I am trying to generate scripts on the server where SS runs. The server is Win2k SP4.

I managed to create a connection from my PC and generate the script below. This database is created and maintained by a metadata-driven application building platform. I would also like you to comment on the link table schema of another database that uses GUIDs for replication. Should I include more or less than I did here?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AccountTR]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[AccountTR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContactTR]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[ContactTR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Account]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRLNK001]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TRLNK001]
GO

CREATE TABLE [dbo].[Account] (
	[FormID] [decimal](10, 0) NULL ,
	[ATrail] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Locker] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Locktime] [char] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AcctID] [decimal](10, 0) NULL ,
	[Status] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Company] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Priority] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address1] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address2] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AccState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Industry] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ZipCode] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MainPh] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MainFax] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SICCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[URL] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Created] [datetime] NULL ,
	[Source] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Type] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TeamLead] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Target] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AcctNo] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contact] (
	[FormID] [decimal](10, 0) NULL ,
	[ATrail] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Locker] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Locktime] [char] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Contact] [decimal](10, 0) NULL ,
	[AddType] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Company] [char] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FName] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LName] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address1] [char] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address2] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StatePr] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ZipPost] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SalesRep] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[JobTitle] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ConRole] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BPhone] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Mobile] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[email] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Nletstat] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[JobFunc] [char] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomePh] [char] (39) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Priority] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NActDate] [datetime] NULL ,
	[NAction] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[TRLNK001] (
	[ACCOUNT] [decimal](10, 0) NULL ,
	[CONTACT] [decimal](10, 0) NULL 
) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [SFCRM8500AccountFormID] ON [dbo].[Account]([FormID]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountAcctID] ON [dbo].[Account]([AcctID]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountAddress2] ON [dbo].[Account]([Address2]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountCompany] ON [dbo].[Account]([Company]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountCountry] ON [dbo].[Account]([Country]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountCreated] ON [dbo].[Account]([Created]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountIndustry] ON [dbo].[Account]([Industry]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountMainFax] ON [dbo].[Account]([MainFax]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountMainPh] ON [dbo].[Account]([MainPh]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountStatus] ON [dbo].[Account]([Status]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountURL] ON [dbo].[Account]([URL]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountAccState] ON [dbo].[Account]([AccState]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountType] ON [dbo].[Account]([Type]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500AccountTeamLead] ON [dbo].[Account]([TeamLead]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [SFCRM8500ContactFormID] ON [dbo].[Contact]([FormID]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactAddType] ON [dbo].[Contact]([AddType]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactBPhone] ON [dbo].[Contact]([BPhone]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactCity] ON [dbo].[Contact]([City]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactCompany] ON [dbo].[Contact]([Company]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactConRole] ON [dbo].[Contact]([ConRole]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactContact] ON [dbo].[Contact]([Contact]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactCountry] ON [dbo].[Contact]([Country]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactFName] ON [dbo].[Contact]([FName]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactJobTitle] ON [dbo].[Contact]([JobTitle]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactLName] ON [dbo].[Contact]([LName]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactMobile] ON [dbo].[Contact]([Mobile]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactStatePr] ON [dbo].[Contact]([StatePr]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactStatus] ON [dbo].[Contact]([Status]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactZipPost] ON [dbo].[Contact]([ZipPost]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactSalesRep] ON [dbo].[Contact]([SalesRep]) ON [PRIMARY]
GO

 CREATE  INDEX [SFCRM8500ContactNActDate] ON [dbo].[Contact]([NActDate]) ON [PRIMARY]
GO

 CREATE  INDEX [IDXTRLNK001] ON [dbo].[TRLNK001]([ACCOUNT], [CONTACT]) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER AccountTR ON SFCRM8500..Account 
FOR UPDATE 
AS 
 IF UPDATE (Status) 
BEGIN 
  DECLARE @chDate CHAR(8), @chTime CHAR(8), @dtime INT 

  DECLARE @FormID INT, @ChngBy CHAR(32), @oldStatus char(24), @newStatus char(24) 
  SELECT @dtime =DATEDIFF(second, '01/01/1970', getdate()) 
  SELECT @chDate =CONVERT(CHAR(8), GetDate(), 112), @chTime =CONVERT(CHAR(8), getdate(), 108) 
  SELECT @FormID =FormID, @ChngBy =Locker, @newStatus =Status FROM inserted 
  SELECT   @oldStatus =Status FROM deleted 

  IF ( (@oldStatus is null and @newStatus is not null) or (@oldStatus is not null and @newStatus is null) or (@oldStatus <> @newStatus) ) INSERT INTO SFCRM8500..Accounthist (FormID, DateTime, ChngDate, ChngTime, ChngBy, ChngWhat, ChngFrom, ChngTo) VALUES(@FormID,@dtime,@chDate, @chTime,@ChngBy,'Status',@oldStatus,@newStatus)
 
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER ContactTR ON SFCRM8500..Contact 
FOR UPDATE 
AS 
 IF UPDATE (Status) 
BEGIN 
  DECLARE @chDate CHAR(8), @chTime CHAR(8), @dtime INT 

  DECLARE @FormID INT, @ChngBy CHAR(32), @oldStatus char(64), @newStatus char(64) 
  SELECT @dtime =DATEDIFF(second, '01/01/1970', getdate()) 
  SELECT @chDate =CONVERT(CHAR(8), GetDate(), 112), @chTime =CONVERT(CHAR(8), getdate(), 108) 
  SELECT @FormID =FormID, @ChngBy =Locker, @newStatus =Status FROM inserted 
  SELECT   @oldStatus =Status FROM deleted 

  IF ( (@oldStatus is null and @newStatus is not null) or (@oldStatus is not null and @newStatus is null) or (@oldStatus <> @newStatus) ) INSERT INTO SFCRM8500..Contacthist (FormID, DateTime, ChngDate, ChngTime, ChngBy, ChngWhat, ChngFrom, ChngTo) VALUES(@FormID,@dtime,@chDate, @chTime,@ChngBy,'Status',@oldStatus,@newStatus)
 
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-09 : 12:34:43

You've got a fundamental problem with your triggers. Please see my weblog for details:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

You don't have any foreign keys defined.

Tara Kizer

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-11 : 13:45:25

Tara, I read your article and see what you mean. Looks like we may not go with the tool of this vendor anyway.

Are you in a position to provide some consulting help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-11 : 13:49:36

No I am not, however there are others here that are.

Tara Kizer
aka tduggan

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-11 : 14:38:57

Would you recommend anyone? Is the job postings forum the right place to post an ad?

Would you mind looking at another schema script and discussing the indexing strategy for it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-11 : 14:50:39

I'd recommend Mark Caldwell, aka AjarnMark. The job postings forum actually costs money to post.

Feel free to start a new thread with your next schema script, that way more eyes are on it than just me.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources