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
 SQL Server Development (2000)
 Mysteriously slow select

Author  Topic 

jaybee
Yak Posting Veteran

72 Posts
Posted - 2007-07-18 : 11:00:17
SET NOCOUNT ON ;
Select DISTINCT (tblAccounts.accountID) ,
tblAccounts.accountFirstName ,
tblAccounts.accountLastName,
tblServicesTree.serviceName,
tblcommunicationsSent.SentTo


FROM dbo.fn_getValidSubscriptions() as vs
JOIN tblAccounts on tblAccounts.accountID = vs.accountID
JOIN tblServicesTree on tblServicesTree.serviceID = vs.serviceID
JOIN tblcommunicationsSent on tblcommunicationsSent.accountID = vs.accountID
WHERE tblAccounts.accountEnabled <> 0 AND tblAccounts.accountSubscriptionsEnabled <> 0
and tblAccounts.accountID <> 3159
AND tblAccounts.ntUserID NOT IN ('Domain1','Domain2','Domain3')

order by tblAccounts.accountLastname, tblAccounts.accountFirstname

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-07-18 : 11:17:08
What indexes do you have on each of the tables involved in your query? Could you post the CREATE TABLE statement for each of the table including the CREATE INDEX statements?

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2007-07-18 : 15:25:05
I believe that <> and NOT IN conditions are generally slower than > < or IN.

If possible, try to change the logic of the query, to exlude those ... it might help.

Also, you might explore using table variables (i.e. storing partial query results in them and then joining with rest of the tables)

And ... how slow is it? What is the subtree cost, logical reads? Looking at the execution plan can you see where the problem could be arising from?

Are your statistics updated, indexes on tables in question properly set and not fragmented?
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2007-07-19 : 04:21:38
I should add that this is ad hoc reporting, so no Stored Proc. The exe plan states that a table scan on tblcommunicationsSent is costing 87% of the time, and an inner join to AccountsID another 12%. The time difference between the script with red entries, and without is 6 hours(minimum) to 46 seconds.

Tara -

CREATE TABLE [dbo].[tblCommunicationsSent] (
[communicationID] [int] NOT NULL ,
[accountID] [int] NOT NULL ,
[sentThrough_communicationMedium_listItemID] [int] NOT NULL ,
[sentTo] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[successfullySent] [bit] NOT NULL ,
[updateInformationType_listItemID] [int] NOT NULL ,
[smscReference] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[comments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[createdDateTime] [datetime] NOT NULL ,
[createdBy_accountID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_tblCommunicationsSent] ON [dbo].[tblCommunicationsSent]([communicationID]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE INDEX [IX_tblCommunicationsSent_1] ON [dbo].[tblCommunicationsSent]([communicationID], [sentThrough_communicationMedium_listItemID], [accountID], [updateInformationType_listItemID], [createdDateTime] DESC ) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE INDEX [IX_tblCommunicationsSent_2] ON [dbo].[tblCommunicationsSent]([createdDateTime] DESC , [communicationID] DESC ) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblAccounts] (
[accountID] [int] IDENTITY (1, 1) NOT NULL ,
[ntUserID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ntUserDomain] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[accountType_listItemID] [int] NOT NULL ,
[parent_accountID] [int] NOT NULL ,
[accountName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[accountFirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[accountLastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[accountEnabled] [bit] NOT NULL ,
[createdDateTime] [datetime] NOT NULL ,
[createdBy_accountID] [int] NOT NULL ,
[accountSubscriptionsEnabled] [bit] NOT NULL ,
[useHtmlOnEmails] [bit] NOT NULL ,
[lastModifiedDateTime] [datetime] NOT NULL ,
[lastModifiedBy_accountID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblAccounts] ON [dbo].[tblAccounts]([ntUserID], [ntUserDomain]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE INDEX [IX_tblAccounts_1] ON [dbo].[tblAccounts]([accountFirstName], [accountLastName], [accountType_listItemID]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE INDEX [IX_tblAccounts_2] ON [dbo].[tblAccounts]([parent_accountID]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblServicesTree] (
[serviceID] [int] IDENTITY (1, 1) NOT NULL ,
[serviceName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[parentID] [int] NOT NULL ,
[serviceShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[enabled] [bit] NOT NULL ,
[createdDateTime] [datetime] NOT NULL ,
[createdBy_accountID] [int] NOT NULL ,
[lastModifiedDateTime] [datetime] NOT NULL ,
[lastModifiedBy_accountID] [int] NOT NULL
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_tblServicesTree] ON [dbo].[tblServicesTree]([parentID], [serviceID]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblServicesTree_1] ON [dbo].[tblServicesTree]([serviceName]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-07-19 : 11:47:26
Why don't you have primary keys set on these tables?

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2007-07-20 : 01:05:27
3 Things...

1. Tara is correct... you need to add Primary Keys to each table.
2. You need a composite index that has AccountID and SentTo for tblcommunicationsSent.
3. tblcommunicationsSent violates the 8060 max row length... you need to find some way to get it down to 8000. Recommend you split of the VARCHAR(8000) column into a "sister table".

--Jeff Moden
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2007-07-20 : 05:37:44
quote:
Originally posted by tkizer

Why don't you have primary keys set on these tables?

Tara Kizer
http://weblogs.sqlteam.com/tarad/


Beeeeeeeeeeeeeeeeeecause I'm a DBA, not a developer! (as evinced by Jeff's astute observation - and startling revelation - that one of them exceeds 8K rows).

Not my problem any more - but it's a good point, and doubtless the next DBA will be emailing me for help, and I'll pass on those points.

Cheers guys,


Jaybee
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-07-20 : 12:37:51
But that is the DBAs responsibility!

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2007-07-21 : 06:04:09
quote:
Originally posted by tkizer

But that is the DBAs responsibility!

Tara Kizer
http://weblogs.sqlteam.com/tarad/


Tara you've got 16,000 posts here...I'm a little surprised you don't know the difference between Development and Admin.

A developer builds table structures (including primary/foreign keys), inserts data, and then passes it to the DBA. That's standard across the world. A DBA should NOT have to add PK's. I am aware that in very small environments, you have DBA's doing Dev work as the client is too cheap to pay for separate staff.

Yes, I can ADD a PK to a column, because clearly it needs a clustered index, but this should have been foreseen and done by someone in Dev. You asked, "Why doesn't this table have a PK"? That was a good question. I'd like an answer to that myself, and I'm going to get it from the vendor if I have to tie him up and beat it out of him with a bat....

:)


Jaybee.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-07-21 : 15:54:48
It's the DBA's responsibility to ensure that the schema is correct (properly normalized, indexes added for performance, constraints added for data integrity, ...) after developers have designed it.

I absolutely do not work in a small environment. I never have. All of my jobs have been at large organizations. But at any rate, I'm done explaining myself to you. I'm also done with this thread.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2007-07-22 : 08:24:04
quote:
Originally posted by tkizer

It's the DBA's responsibility to ensure that the schema is correct (properly normalized, indexes added for performance, constraints added for data integrity, ...) after developers have designed it.


Welllllllll....now you're getting into contentious areas. In practice, it's a rare sign of excellence on the part of a DBA to perform a reality check on the table structures of the systems he administers. However, getting them right fundamentally remains the province of the developer, and if the job is botched, it's the developer who deserves a black eye for failing to follow the elemental standards of development, not the DBA for not going above and beyond the call of duty. I have been a consultant DBA for 7 years across more multi-dba environments than I care to remember, and NEVER have I encountered a DBA whos job description entailed checking a developers work for flaws.

I wonder if there's a script that'll ID poor builds such as this and report back, could be interesting.

quote:
I absolutely do not work in a small environment. I never have. All of my jobs have been at large organizations. But at any rate, I'm done explaining myself to you. I'm also done with this thread.

Tara Kizer
http://weblogs.sqlteam.com/tarad/


I've never worked in small organisations either. I have though walked out of interviews where I was asked Developery questions, but they were very much the exception.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-07-22 : 14:13:43
are you talking about DB developers or front end developers?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-22 : 14:35:39
What happens if you reorganize the referenced tables?
Most often it is more efficient to start with the tables that returns least records first.
SELECT DISTINCT	a.accountID, 
		a.accountFirstName,
		a.accountLastName,
		st.serviceName,
		cs.SentTo
FROM		(
			SELECT	AccountID
			FROM	tblAccounts
			WHERE	AccountEnabled <> 0
				AND AccountSubscriptionsEnabled <> 0
				AND AccountID <> 3159
				AND ntUserID NOT IN ('Domain1', 'Domain2', 'Domain3')			
		) AS a
INNER JOIN	dbo.fn_GetValidSubscriptions() AS vs ON vs.AccountID = a.AccountID
INNER JOIN	tblServicesTree AS st ON st.ServiceID = vs.ServiceID
INNER JOIN	tblCommunicationsSent AS cs ON cs.AccountID = a.AccountID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts
Posted - 2007-07-23 : 02:18:46
quote:

Welllllllll....now you're getting into contentious areas. In practice, it's a rare sign of excellence on the part of a DBA to perform a reality check on the table structures of the systems he administers. However, getting them right fundamentally remains the province of the developer, and if the job is botched, it's the developer who deserves a black eye for failing to follow the elemental standards of development, not the DBA for not going above and beyond the call of duty. I have been a consultant DBA for 7 years across more multi-dba environments than I care to remember, and NEVER have I encountered a DBA whos job description entailed checking a developers work for flaws.

I wonder if there's a script that'll ID poor builds such as this and report back, could be interesting.

I've never worked in small organisations either. I have though walked out of interviews where I was asked Developery questions, but they were very much the exception.


Soooo..... why are you asking Developer questions? Tell AppDev they have slow running code and let them figure it out.

--Jeff Moden
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts
Posted - 2007-07-23 : 04:40:26
quote:
Tara you've got 16,000 posts here...I'm a little surprised you don't know the difference between Development and Admin.

That's a little patronising and unnecessary, Jaybee. Here's one back at ya.
quote:
..... Welllllllll....now you're getting into contentious areas. In practice, it's a rare sign of excellence on the part of a DBA to perform a reality check on the table structures of the systems he administers


You may not believe it's the DBAs job to check the entirety of the database schema and it's tables structures, however for someone who's "been a DBA for 7 years across multi-dba environments", I'd have expected that (in light of this being a "mysteriously" slow Select), you may, just may, have noticed, mentioned and raised an eyebrow at the fact the table has no Primary Key... before posting to a SQL DBA forum.



Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-07-23 : 13:43:50
quote:
Originally posted by jaybee
Tara you've got 16,000 posts here...I'm a little surprised you don't know the difference between Development and Admin.


And not only does she has 16k+ post, she also has an MVP from Microsoft

quote:

A developer builds table structures (including primary/foreign keys), inserts data, and then passes it to the DBA.


Not in my world....let's see, coming up on 27 years in the b'dness

quote:

That's standard across the world.


And what world would that be? Pluto?

quote:

A DBA should NOT have to add PK's. I am aware that in very small environments, you have DBA's doing Dev work as the client is too cheap to pay for separate staff.


Ummmm..usually it's the other way around

quote:

Yes, I can ADD a PK to a column, because clearly it needs a clustered index, but this should have been foreseen and done by someone in Dev. You asked, "Why doesn't this table have a PK"? That was a good question. I'd like an answer to that myself, and I'm going to get it from the vendor if I have to tie him up and beat it out of him with a bat....


And that's not really true either...and since you're dealing with a 3rd party product, good luck. I haven't seen one that wasn't a piece of junk



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

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

jaybee
Yak Posting Veteran

72 Posts
Posted - 2007-07-23 : 16:05:25
quote:
Originally posted by Jeff Moden

quote:

Welllllllll....now you're getting into contentious areas. In practice, it's a rare sign of excellence on the part of a DBA to perform a reality check on the table structures of the systems he administers. However, getting them right fundamentally remains the province of the developer, and if the job is botched, it's the developer who deserves a black eye for failing to follow the elemental standards of development, not the DBA for not going above and beyond the call of duty. I have been a consultant DBA for 7 years across more multi-dba environments than I care to remember, and NEVER have I encountered a DBA whos job description entailed checking a developers work for flaws.

I wonder if there's a script that'll ID poor builds such as this and report back, could be interesting.

I've never worked in small organisations either. I have though walked out of interviews where I was asked Developery questions, but they were very much the exception.


Soooo..... why are you asking Developer questions? Tell AppDev they have slow running code and let them figure it out.

--Jeff Moden


No need mate - the vendor accepted fault and "patched" (a glorified way of saying some developer added the PK). I'd let you know how the script is shaping up, but I've had to pass the work out to another resource with more time.

Mr 123...you're still playing with SQL Server after 27 years of work in this business? Well, I suppose if you have love for the tools of your craft, no amount of money can replace that happiness, eh?




Jaybee.
Go to Top of Page

TallCowboy0614
Starting Member

17 Posts
Posted - 2007-08-13 : 18:14:48
Err....maybe because he is GOOD at it?

I think you've got a spanking or two comin' your way, mi perro.

I'm a bit confuzilated. However, such things have never been viewed as a halter on my open mouth.

I am compelled to stare in slack-jawed amazement at this thread.

Since when is it NOT a DBA's job to do things like make sure the developers aren't doing something stupid, like not adding primary keys to tables where they oughta be there? You know, stuff like...well...ADMINISTERING the database?

I have been around in the industry for (OMG!) 24 years, primarily on the backend, and have NEVER, EVER, even HEARD of a DBA/Developer job description such as that which you seem to have. Errr...what exactly DO YOU DO as a DBA?

OK, probably enough button-pushing for the moment, but you need a reality check, dude. Ain't nowhere (at least in mainstream USA, and I would venture a guess that 98.987234 % of the rest of the known world also fits into this statement) that your described job description actually exists.

I am surprised that you have a job, but the whole "contractor" statement brings light to that confusion also, at least based on my experience as a NON-contractor searching for qualified contractors for my projects.

_________________________________
aka "Paul"
Non est ei similis.

"He's not the Messiah. He's a very naughty boy!" - Brian's mum
Go to Top of Page

TallCowboy0614
Starting Member

17 Posts
Posted - 2007-08-13 : 18:17:32
Errr....no offense meant to the compentent contractors out there that I am aware of, mind you. But a suspicious eye upon the rest of the lot of you ;)

_________________________________
aka "Paul"
Non est ei similis.

"He's not the Messiah. He's a very naughty boy!" - Brian's mum
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site