June 4, 2012 at 9:56 am
I have a stored procedure which runs anywhere from 1/2 minute to 4 hours (during nightly processing):
UPDATE tableA
SET tableA.Other_Flag_50=isnull(Staging.other_flag_50,0)
FROM tableA INNER JOIN
(select acct_nbr, appl_code, Other_Flag_50 from tableB)Staging
ON tableA.lnhist_acct_nbr = Staging.acct_nbr
AND tableA.lnhist_appl_code = Staging.appl_code
I ran Blocking reports in Profiler for 2 nights in a row, first at 10 minutes interval then at 5 minutes. The stored procedure never shows up as being blocked (but it blocks other queries).
Any ideas on optimizing this? Would creating a view with the join help (acct_nbr, appl_code, Other_Flag_50 from tableB)? Thanks!!
June 4, 2012 at 10:05 am
Marius.D (6/4/2012)
I have a stored procedure which runs anywhere from 1/2 minute to 4 hours (during nightly processing):UPDATE tableA
SET tableA.Other_Flag_50=isnull(Staging.other_flag_50,0)
FROM tableA INNER JOIN
(select acct_nbr, appl_code, Other_Flag_50 from tableB)Staging
ON tableA.lnhist_acct_nbr = Staging.acct_nbr
AND tableA.lnhist_appl_code = Staging.appl_code
I ran Blocking reports in Profiler for 2 nights in a row, first at 10 minutes interval then at 5 minutes. The stored procedure never shows up as being blocked (but it blocks other queries).
Any ideas on optimizing this? Would creating a view with the join help (acct_nbr, appl_code, Other_Flag_50 from tableB)? Thanks!!
That's near impossible for us to do without DDL and sample data. Please read the article in my signature for the proper way to post a question like this.
First thing I see, though, is a needless derived table. Not sure what the plan looks like on this, but there is no need to have that in there.
Jared
CE - Microsoft
June 4, 2012 at 10:16 am
In addition to Jared's sharp analysis, you are updating every row, even if the values are the same.
if there was an index on lnhist_acct_nbr,lnhist_appl_code,Other_Flag_50 my sample code below might be quicker, but the better question is this:
if TableA's flag always comes form another table (TableB , why store it in TableA at all?, why not use a view to join the two tables together to get that status, and get rid of this update process completely?
thinking outside of the box and current logic might speed things up for you.
UPDATE myTarget
SET myTarget.Other_Flag_50 = ISNULL(Staging.other_flag_50, 0)
FROM tableA myTarget
INNER JOIN tableB Staging
ON myTarget.lnhist_acct_nbr = Staging.acct_nbr
AND myTarget.lnhist_appl_code = Staging.appl_code
WHERE myTarget.Other_Flag_50 <> ISNULL(Staging.other_flag_50, 0)
Lowell
June 4, 2012 at 10:17 am
A view won't help. Views are nothing more than saved select statements.
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 10:28 am
Sorry for posting like that, I was only looking for some advice, direction, not a 100% solution. Both tables, A and B, have hundreds of columns. I cannot provide sample data because it contains sensitive information like account numbers, etc. TableA has 1.7 million rows, 2 non-clustered indexes on applcode and TranCat_OtherFlag. TableB has obnly 28 thousand rows, 1 clustered index (datetime) and 4 non-clustered, none of them on the columns that pertain to the query. I have created and dropped other indexes on tableA, the larger table, with no effect. So let me re-phrase my questions, again, I am only looking for an opinion, like "I would start by trying this" or "your assumption is incorrect".
Here are my thoughts/ questions: 1) since I ran blocking reports with teh smallest interval set to 5 minutes is it possible that something else is blocking teh query repeatedly, yet, it doesn't show up on the reports because it comes and goes quicker than the 5 minutes? and 2) will creating a view (with the join) help? Or, it wouldn't make a difference at run time, since the view is just another query? Thanks!
June 4, 2012 at 10:37 am
Thanks for your reply. I need to talk to someone who understands the business logic, because I don't (just started here, a couple months ago). I will try out your solution and I will let you know. Thanks!
June 4, 2012 at 10:39 am
Marius.D (6/4/2012)
Sorry for posting like that, I was only looking for some advice, direction, not a 100% solution. Both tables, A and B, have hundreds of columns. I cannot provide sample data because it contains sensitive information like account numbers, etc. TableA has 1.7 million rows, 2 non-clustered indexes on applcode and TranCat_OtherFlag. TableB has obnly 28 thousand rows, 1 clustered index (datetime) and 4 non-clustered, none of them on the columns that pertain to the query. I have created and dropped other indexes on tableA, the larger table, with no effect. So let me re-phrase my questions, again, I am only looking for an opinion, like "I would start by trying this" or "your assumption is incorrect".Here are my thoughts/ questions: 1) since I ran blocking reports with teh smallest interval set to 5 minutes is it possible that something else is blocking teh query repeatedly, yet, it doesn't show up on the reports because it comes and goes quicker than the 5 minutes? and 2) will creating a view (with the join) help? Or, it wouldn't make a difference at run time, since the view is just another query? Thanks!
1. Sample data does not have to be a sample of the actual data, but can be made up.
2. Can you at lease post the DDL for columns involved in the queries?
3. I don't think blocking is your issue, but impossible to tell. Why do think blocking has anything to do with it?
4. No, a view won't help.
For me, I would start by analyzing which indexes should be on the join columns. Why there are none in the staging table on these columns seems strange to me because it "logically" makes sense to me to have these columns indexed, just because of their names. What is the use of the staging table? I assume it gets loaded with data from somewhere else and then is used to update the existing table? Then surely you should be able to have indexes covering the information needed for this. Also, as mentioned earlier, limit the update to only update the data that needs to be updated. Finally, why are there nulls in the staging table? Don't allow them in the first place and you will not have to use the isnull function.
Note: This is all based on very little information and is just a "hunch"
Jared
CE - Microsoft
June 4, 2012 at 10:43 am
Marius.D (6/4/2012)
Sorry for posting like that, I was only looking for some advice, direction, not a 100% solution. Both tables, A and B, have hundreds of columns. I cannot provide sample data because it contains sensitive information like account numbers,
Don't need the data at all. Need the table definitions (just the data types of the relevant columns) and index definitions and the execution plan
I am only looking for an opinion, like "I would start by trying this" or "your assumption is incorrect".
Problem is, without details we can't say. The only thing I can suggest without details is 'check your indexes', which isn't very useful, and to note that it appears you're updating the entire table since there's no where clause. Updating almost 2 million rows won't be quick. Isn't there a filter that can be applied?
2) will creating a view (with the join) help? Or, it wouldn't make a difference at run time, since the view is just another query? Thanks!
Nope, won't help. Views don't make queries faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 11:18 am
tableA:
[Other_Flag_50] [bit] NULL,
[lnhist_acct_nbr] [decimal](12, 0) NULL,
[lnhist_appl_code] [char](2) NULL,
tableB:
[acct_nbr] [decimal](12, 0) NULL,
[appl_code] [char](2) NULL,
[other_flag_50] [bit] NULL,
(the DDL should and is the same in both tables, they are both containing Loan data). tableA has history data more than 1 day), tableB has only 1 day's worth of data, for some reason that escapes me both tables get dropped and recreated daily from a different database, which is more OLTP. This database is used for reporting. Changing tables, even if they would let me, is a major pain, as the Cognos model and a lot of reports would need to be changed, too)
tableA indexes:
NONCLUSTERED INDEX [IX_applcode] ON [dbo].[tableA]
(
[lnhist_appl_code] ASC,
[lnhist_acct_nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
and
NONCLUSTERED INDEX [IX_TranCat_Other] ON [dbo].[tableA]
(
[TranCat] ASC,
[Other_Flag_50] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
indexes on tableB:
NONCLUSTERED INDEX [IDX_ApplAcctNo] ON [dbo].[tableB]
(
[applAcctNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
and
CREATE CLUSTERED INDEX [IDX_FiledatApplAcctNo] ON [dbo].[tableB]
(
[Filedate] ASC,
[applAcctNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
and 3 more, none of them spanning any of the columns in my sp.
I don't know how to post the complete execution plan, but the major cost (79%) is on updating tableA, with all rows being affected. I was looking at blocking because about once every 5-6 days, the sp wil execute in 30-40 seconds, most of teh time though, it takes 2-3-4 hours. I guess that excludes parameter sniffing (since it does execute in 30-40 seconds, too, just like it does every time when run from SSMS). Thanks!!
June 4, 2012 at 11:22 am
In SSMS, in the actual execution plan right click and save as a .sqlplan. Attach that to your post on this thread.
June 4, 2012 at 11:23 am
Another thing that I would try is to update without the ISNULL function. So, you are updating with NULLS. Then... Do a second update on tableA and simply
UPDATE tableA
SET Other_Flag_50 = 0
WHERE Other_Flag_50 IS NULL
Jared
CE - Microsoft
June 4, 2012 at 11:36 am
thanks!
June 4, 2012 at 11:46 am
CREATE NONCLUSTERED INDEX [IDX_ApplAcctNo] ON [dbo].[tableB]
(
[applAcctNo] ASC
)
Since tableB already has that index, I suggest changing its definition to also contain the acct_nbr and the bit flag. Then the index will be a covering index for the UPDATE.
CREATE NONCLUSTERED INDEX [IDX_ApplAcctNo] ON [dbo].[tableB] --<< chg index name if you prefer
(
[applAcctNo] ASC,
[acct_nbr] ASC
)
INCLUDE (other_flag_50)
WITH (<...index_options...>)
ON [filegroup]
UPDATE a
SET a.Other_Flag_50 = ISNULL(Staging.other_flag_50, 0)
FROM tableA a
INNER JOIN [dbo].[tableB] Staging ON
Staging.applAcctNo = a.applAcctNo AND
Staging.acct_nbr = a.acct_nbr
WHERE --added via EDIT; in too big a hurry, left this out originally :-)
a.Other_Flag_50 <> ISNULL(Staging.other_flag_50, 0)
I would not suggest a second, separate UPDATE, since that will force SQL to scan all of tableA again.
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".
June 4, 2012 at 1:56 pm
Maybe I'm not getting this, but tableB does not get updated.
The big question mark continues to be, with or without more indexes, how come the sp runs anywhere between 30s and 4 hours? (and again, this is the sp, not run in SSMS, parameter sniffing excluded). I am thinking the blocking report I ran last time for a 5 minutes interval may noty catch something that blocks it? (I'm just afraid of running an update interval of 10 seconds over night). Does anybody think this is a possibility or should I cross that off?
June 4, 2012 at 1:59 pm
Check what it's waiting for (doesn't have to be lock waits). Also check if there's massive differences in the row counts (unlikely since there are no predicates, but possible)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply