Mysteriously slow select
-
Hi all,
The following script runs for 5 hours as is, but if the red text is removed, runs in only 45 seconds - any ideas?
Thanks, Jaybee.
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
-
It probably doesn't help that you are joining to a UDF, but check to be sure you have an index on the following fields since you are doing a join:
tblcommunicationsSent.accountID
Where is this query? Are you testing it in Query Analyzer or is this a stored proc?
If it's a stored proc and it still runs slow after the index is created, run sp_updatestats and try again.
-
May be data in table tblcommunicationsSent is exclusive locked? Is big table tblcommunicationsSent?
-
I guess that it is a 1 to Many between fn_getValidSubscriptions and tblcommunicationsSent.
The query is doing a distinct on tblCommunicationsSent.SentTo - and the number of Many in the relationship will affect how long the query runs. It have to run through many more rows to determine the distinct - I suspect it is an exponential increase as if you double the number of rows each row has to be checked against twice as many rows and there are twice as many rows to check so it takes 4 times longer.
J
-
Well, 'tblcommunicationsSent' has 5.5 million rows....
-
Might be the cause of the problem!
Any other way of getting the SentTo information?
J
-
Perhaps some way to get distinct accountID's from a nested select?
-
If tblcommunicationsSent has 5+ million rows then the culprit is the join against the UDF. It's going to be doing a table scan because the UDF doesn't have (can't have) any indexes.
What is that function doing? It may be better to integrate the function's selects into your query here so it will be joining to real tables.
-
Have you got some data/ddl you can share with us?
J
-
Load data into temp table without join to tblcommunicationsSent. After join temp table with tblcommunicationsSent
-
You need clustered index on tblcommunicationsSent.accountID.
There may be other columns included into that index but accountID must be the first one.
_____________
Code for TallyGenerator -
Trying it now....31 minutes and counting.
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.
Here are the table/index structures
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
-
It is doing a full table scan because there is no usable index. AccountId is part of one index but it is not the first column in the index and is not usable.
Putting an index on AccountId would probably help and if you can make it clustered then it would be even better. Stop your query and add the index if you can. No point in waiting 6 hours.
How ad hoc is this? If it is a one off (everything that starts as a one off soon becomes business as usual) then there is not much to be gained by adding the index as it may not be used for anything else and will slow down inserts into the table. If the query will be run several times then it is probably worth creating the index.
Creating a simple index will probably only take a few minutes and will lock the table. Clustering the index will take a lot longer as it will have to sort the table as well.
J
-
I am tempted to put a clustered index on the table but there's a time limit on all this; I'll wait for a code-based solution, or I'll try the Index Tuning Wizard for the first time (a new corner of SQL Server, yayyyy!!) and see if a N/C index will suffice.
Everything else takes time I don't have, because after today it's automatically another DBA's problem, management guard my time jealously, but for the sake of pride I'd like to lick it before then.
Jaybee.
-
Even if you add an index, it will likely still do a table scan since you are joining to a user defined function.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply