February 19, 2016 at 8:03 am
It is not I don't want to provide the information, but I can't post my company info on the website. I am trying to provide the info with a twist version.
BTW how do I post the execution plan?
February 19, 2016 at 8:19 am
Here is the create table statement, this table is created by the software company so I can't change anything.
CREATE TABLE [dbo].[user_log](
[user_id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[actn_cd] [smallint] NULL,
[user_accnt_id] [bigint] NULL,
[evnt_cst_id] [bigint] NULL,
[cnsmr_id] [bigint] NULL,
[rslt_Cd] [smallint] NULL,
[wrkchn_id] [bigint] NULL,
[wrkgrp_id] [bigint] NULL,
[wrkgrp_scan_lst_id] [int] NULL,
[cmpgn_id] [bigint] NULL,
[wrk_lst_id] [bigint] NULL,
[scan_lst_id] [bigint] NULL,
[audit_lst_id] [bigint] NULL,
[cnsmr_accnt_ar_log_crt_usr_id] [bigint] NOT NULL,
[cnsmr_accnt_ar_mssg_txt] [varchar](8000) NULL,
[upsrt_dttm] [datetime] NOT NULL,
[upsrt_soft_comp_id] [int] NOT NULL,
[upsrt_trnsctn_nmbr] [int] NOT NULL,
[upsrt_usr_id] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([actn_cd])
REFERENCES [dbo].[actn_Cd] ([actn_Cd])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([audit_lst_id])
REFERENCES [dbo].[audit_lst] ([audit_lst_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([cmpgn_id])
REFERENCES [dbo].[cmpgn] ([cmpgn_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([cnsmr_accnt_ar_log_crt_usr_id])
REFERENCES [dbo].[usr] ([usr_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([cnsmr_id])
REFERENCES [dbo].[cnsmr] ([cnsmr_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([cnsmr_accnt_id])
REFERENCES [dbo].[cnsmr_accnt] ([cnsmr_accnt_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([evnt_cst_id])
REFERENCES [dbo].[evnt_Cst] ([evnt_cst_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([rslt_Cd])
REFERENCES [dbo].[rslt_Cd] ([rslt_Cd])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([scan_lst_id])
REFERENCES [dbo].[scan_lst] ([scan_lst_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([wrk_lst_id])
REFERENCES [dbo].[wrk_lst] ([wrk_lst_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([wrkgrp_id])
REFERENCES [dbo].[wrkgrp] ([wrkgrp_id])
GO
ALTER TABLE [dbo].[user_log] WITH CHECK ADD FOREIGN KEY([wrkgrp_scan_lst_id])
REFERENCES [dbo].[wrkgrp_scan_lst] ([wrkgrp_scan_lst_id])
GO
CREATE NONCLUSTERED INDEX [idx0001] ON [dbo].[user_log]
(
[upsrt_dttm] ASC
)
INCLUDE ( [cnsmr_id],
[rslt_Cd],
[wrkgrp_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx0002] ON [dbo].[user_log]
(
[upsrt_dttm] ASC
)
INCLUDE ( [cnsmr_id],
[rslt_Cd],
[wrkgrp_id],
[cnsmr_accnt_ar_log_crt_usr_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx0003] ON [dbo].[user_log]
(
[cnsmr_accnt_ar_log_crt_usr_id] ASC
)
INCLUDE ( [cnsmr_id],
[rslt_Cd]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [sheet_001] ON [dbo].[user_log]
(
[cnsmr_id] ASC,
[upsrt_usr_id] ASC,
[actn_cd] ASC,
[rslt_Cd] ASC
)
INCLUDE ( [user_id],
[cnsmr_accnt_ar_mssg_txt],
[upsrt_dttm]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx5] ON [dbo].[user_log]
((
[cnsmr_id] ASC
)
INCLUDE ( [actn_cd],
[cnsmr_accnt_id],
[rslt_Cd],
[scan_lst_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ie1] ON [dbo].[user_log]
(
[cnsmr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ie2] ON [dbo].[user_log]
[cnsmr_accnt_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ie3] ON [dbo].[user_log]
(
[actn_cd] ASC,
[cnsmr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ie4] ON [dbo].[user_log]
(
[rslt_Cd] ASC,
[cnsmr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ie5] ON [dbo].[user_log]
(
[audit_lst_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NCI_idx] ON [dbo].[user_log]
(
[rslt_Cd] ASC
)
INCLUDE ( [cnsmr_id],
[cnsmr_accnt_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
February 19, 2016 at 8:21 am
Never mind. This is taking too long. (and sorry, posted this before I saw your post with the table info so my column names match your original post instead of the real table)
Try the following and let us know if it works any better.
DECLARE @Dt1 DATETIME, @dt2 DATETIME;
SET @Dt1 = '2/11/2016';
SET @dt2 = '2/12/2016';
WITH cteParms AS
(
SELECT StartDT = @Dt1
,EndDT = @dt2
)
SELECT u.username, u.firstname, u.lastname
FROM dbo.Users u--Make sure you ALWAYS use 2 part naming
CROSS APPLY cteParms p
WHERE u.UpdateDt >= p.StartDT
AND u.UpdateDt < p.EndDT;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2016 at 8:22 am
Loner (2/19/2016)
It is not I don't want to provide the information, but I can't post my company info on the website. I am trying to provide the info with a twist version.BTW how do I post the execution plan?
It's just table names and index names, I don't see how that could compromise security unless they already have some glaring holes. But if that really is an issue, then use a find and replace utility to replace all column and table names with fake names before posting. Do make sure to test the resulting code though - if you make a mistake and it doesn't run, then it will not help us at all.
For the execution plan, right-click in the plan area, click "Save Execution Plan As". Note that the plan will contain the query (well, the start of it if it's very long) and table, column, and index names. If you have SQL Sentry Plan Explorer, you can use the Anonymize function to remove the query from the plan and replace all table, column, and index names with placeholder names. (Note that Plan Explorer is a free download)
If you do post a plan, then please post not only the fast and slow original plans, but also the slow plan that uses the OPTION (RECOMPILE) hint.
EDIT: And while I was typing this, you apparently got permission and posted the table. Makes my post a bit obsolete, but I am still happy.
Still interested in seeing the execution plans, though
February 19, 2016 at 8:33 am
I attached two execution plan - one with parameter, one without.
February 19, 2016 at 8:34 am
The only two indexes that I see that come close to supporting the particular query you posted (provided that I've interpreted the differences between your original post and your CREATE TABLE correctly) are these...
CREATE NONCLUSTERED INDEX [idx0001] ON [dbo].[user_log]
(
[upsrt_dttm] ASC
)
INCLUDE ( [cnsmr_id],
[rslt_Cd],
[wrkgrp_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx0002] ON [dbo].[user_log]
(
[upsrt_dttm] ASC
)
INCLUDE ( [cnsmr_id],
[rslt_Cd],
[wrkgrp_id],
[cnsmr_accnt_ar_log_crt_usr_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Except for the upsert date, there's nothing in your original post that matches the table. For example, there are no user first or last names.
It's time to come clean and post the real query that you're having problems with.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2016 at 8:36 am
Here is the execution plan with option recomplile
February 19, 2016 at 8:42 am
Loner (2/19/2016)
this table is created by the software company so I can't change anything.
That's really a shame, because there are some indexes that are completely obsolete and do nothing except take up space, slow down modifications, and confuse the optimizer.
- idx00001 is obsolete, idx00002 is the same with just one additional included column. You can drop idx00001 without losing anything,
- ie1 is obsolete, idx5 is on the same column with only some included columns added. You can drop ie1 without losing anything.
- ie4 and NCI_idx can be replaced by a single index on rslt_Cd, cnsmr_id with cnsmr_account_id as included column.
The above is just from a casual observation of the index list, without ging into a full matrix. And without verifying whether any of these indexes are useful at all.
The best solution to speed up your specific query would be a covering index that indexes the column used in the date range (upsrt_dttm?) and includes all other columns used in the query. But adding indexes to support single queries is often not a good idea because it can hurt elsewhere. And since the query sometimes is fast enough, there may be other options.
EDIT: I see you now posted the execution plans and the actual query. I will take a look.
February 19, 2016 at 8:42 am
This is the real query. The second insert took a long time and the table I posted is actually [cnsmr_accnt_ar_log]
DECLARE @Date1 DATETIME,
@Date2 DATETIME,
@ManagerUserName VARCHAR(128)
SET @Date1 = '02/11/2016'
SET @Date2 = '02/12/2016'
SET @ManagerUserName = 'bstansberry'
CREATE TABLE #TempUser (DebtUserName VARCHAR(128),
DebtCollectorName VARCHAR(100),
ManagerUserName VARCHAR(128),
ManagerName VARCHAR(100))
INSERT INTO #TempUser (DebtUserName,
DebtCollectorName ,
ManagerUserName ,
ManagerName)
SELECT e.login_id, e.[first_name] + ' ' + e.[last_name] ,
e1.login_id, e1.[first_name] + ' ' + e1.[last_name]
FROM [Employee] e
INNER JOIN [Employee] e1 ON e.manager_file_number = e1.file_number
INNER JOIN [dbo].JobTitle j ON e.[job_title_id] = j.[job_title_id]
WHERE e1.current_record = 'Y' AND e.current_record = 'Y' AND e.employee_status = 'Active'
AND j.[job_title_code] IN ('DC','CR', 'DC B')
AND e1.login_id = @ManagerUserName AND e.login_id IS NOT NULL
CREATE TABLE #TempARDetail (userid BIGINT,
usr_usrnm VARCHAR(128),
cnsmr_id BIGINT,
actn_cd SMALLINT,
rslt_cd SMALLINT)
INSERT INTO #TempARDetail (userid ,
usr_usrnm,
cnsmr_id ,
actn_cd ,
rslt_cd)
SELECT ar.upsrt_usr_id, u.usr_usrnm, ar.cnsmr_id , ar.actn_cd, ar.rslt_cd
FROM [cnsmr_accnt_ar_log] ar
INNER JOIN [dbo].[usr] u ON ar.upsrt_usr_id = u.usr_id
INNER JOIN #TempUser tu ON tu.DebtUserName = u.usr_usrnm
WHERE ar.[upsrt_dttm] >= @Date1 AND ar.[upsrt_dttm] <= DATEADD(s, -1, DATEADD(dd, 1, @Date2))
February 19, 2016 at 8:58 am
Thanks for posting the execution plans, table, and query. That makes it a lot easier.
However, I must unfortunately nag you a bit more - the plans you posted are estimated execution plans and we need the actual plans. Especially the WITH RECOMPILE plan might look completely different in the actual plan.
What I see is that the plan with parameters (both with and without RECOMPILE hint) estimate that 145 million rows will match. The plan with the hardcoded values estimates a much lower amount, just over 56 thousand, and hence uses an access method that is optimized for this lower number.
Unfortunately I cannot verify which estimate is more correct, I'd need the actual plans for that. Based on your observed processing speed, I will go with the lower estimate.
One change that I would definitely make if I were you is to change the last query to this:
SET @Date2 = DATEADD(day, 1, @Date2); -- First day no longer included instead of last day still included
INSERT INTO #TempARDetail (userid ,
usr_usrnm,
cnsmr_id ,
actn_cd ,
rslt_cd)
SELECT ar.upsrt_usr_id, u.usr_usrnm, ar.cnsmr_id , ar.actn_cd, ar.rslt_cd
FROM [cnsmr_accnt_ar_log] ar
INNER JOIN [dbo].[usr] u ON ar.upsrt_usr_id = u.usr_id
INNER JOIN #TempUser tu ON tu.DebtUserName = u.usr_usrnm
WHERE ar.[upsrt_dttm] >= @Date1 AND ar.[upsrt_dttm] < @Date2;
Especially when you use the recompile hint, this might help SQL Server come up with a better estimate. Also, perhaps more imortant, this query is more correct - the query you wrote will miss rows that have a datetime value somewhere in the last second of the last day (e.g. 23:59:59.318)
February 19, 2016 at 9:15 am
The "standard" performance problem when clustering on an identity column "by default". Trying to work around the wrong clustered index will generate lots of extra I/O.
You should cluster this table on upsrt_dttm instead.
Yes, unfortunately it will take a lot of effort to implement that change. But that will give you the performance you need without having to individually re-write every query and/or modify covering index(es) that uses upsrt_dttm as the main lookup key value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 19, 2016 at 9:19 am
ScottPletcher (2/19/2016)
You should cluster this table on upsrt_dttm instead.
How can you possibly determine that this should be a good clustering key based on seeing only one single query that uses the table and not knowing anything about updates?
February 19, 2016 at 9:24 am
Hugo Kornelis (2/19/2016)
ScottPletcher (2/19/2016)
You should cluster this table on upsrt_dttm instead.How can you possibly determine that this should be a good clustering key based on seeing only one single query that uses the table and not knowing anything about updates?
Quite true, really the missing index stats, index usage stats and operational stats should be checked first, to be sure to get the absolute best clustering key(s). But I don't think that's likely to happen in this case. Based on the query and indexes shown, upsrt_dttm will definitely be better than identity.
Edit: I've tuned thousands of similar tables over 30 years. Log tables are almost always best clustered on a datetime, and virtually never on an identity value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 19, 2016 at 9:38 am
Here is the actual execution plan with option recompile.
Thanks
February 19, 2016 at 9:39 am
As I said before, the database is created from the software company, I cannot create any index.
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply