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.
| Author |
Topic |
|
gasMeetFoot
Starting Member
4 Posts |
 Posted - 2007-07-27 : 09:22:40
|
| (Apologies for the poor grammar of the subject; it got truncated.)I have a stored procedure that I want to return about 20 columns from a join of about 5 tables. I also want to return distinct on one of the 20 columns. So, I insert into a table I declare within the SP, then select * from that and select distinct from it as well.The problem is that the two-minute runtime of the SP is passing my IIS timeout, so I am trying to tune it down. The execution plan shows 89% of time is spent on a compute scalar operation on a text column. Huh? That doesn't make a lot of sense to me. I am not running any aggregate functions, and certainly a distinct or max() would make no sense in the context of a blob column. The Argument is: DEFINE: ([tablename].[columnname]=[tablename].[columnname] ), where the table/column name pair are the same on both sides.Does anyone have any insight? I have Googled around, but have not turned up an explanation why SQL server would perform such an operation. My search of this forum turned up 3 threads that mentioned compute scalar and text, and none were really relevant. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-27 : 09:26:51
|
| Can't really help you much without your table definitions and you code. I'm sure if you provide brief but specific details we can help optimize your code.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
gasMeetFoot
Starting Member
4 Posts |
Posted - 2007-07-27 : 09:42:02
|
| Sure. [CODE]CREATE PROCEDURE dbo.Report_TaskCompletionSummaryDetail_josh @ReadyDateStart datetime, @ReadyDateEnd datetime, @DueDateStart datetime, @DueDateEnd datetime, @GroupDueStart datetime, @GroupDueEnd datetime, @ActualDueStart datetime, @ActualDueEnd datetime, @CompleteStart datetime, @CompleteEnd datetime, @GroupIdentifier varchar(255), @TaskIdentifier varchar(255), @JeopardyCode int, @Queue int, @Manager int, @TaskType int, @TaskClass int, @IncludeSupressed bit AS DECLARE @GroupId intDECLARE @Groups TABLE (GroupId int)DECLARE @Queues TABLE (QueueId int)DECLARE @Result TABLE( Id int , Priority int , Ready datetime , Identifier varchar(255) , Multiplier float , Due datetime , SourceSystemKey varchar(255) , XmlAttributes text --the column the execution plan is complaining about , QueueId int , QueueName varchar(40) , QueueDescription varchar(255) , TaskTypeId int , TaskTypeName varchar(40) , TaskTypeEditUrl varchar(255) , TaskClassId int , TaskClassName varchar(40) , Flags int , Flagged bit , Sleeping bit , JeopardyCodeId int , JeopardyCodeName varchar(10) , JeopardyCodeDescription varchar(255) , Wake datetime , Age int , CommentAge int , Inserted datetime , Archived datetime , GroupId int , ParentId int , GroupIdentifier varchar(255) , Created datetime , GroupAge int , ActualDue datetime , DesiredDue datetime , GroupCountDown int , GroupJeopardyCodeId int , GroupJeopardyCodeName varchar(10) , GroupJeopardyCodeDescription varchar(255) , ArchivedBy varchar(40))-- population of the @group and @queue tables happens here; commented because no hit in exec planinsert into @Result( --the full column list from above)SELECT *FROM UI_TaskArchive_v --view where the 5 joins happenWHERE (@ReadyDateStart IS NULL OR Ready >= @ReadyDateStart) AND (@ReadyDateEnd IS NULL OR Ready <= @ReadyDateEnd) AND (@DueDateStart IS NULL OR Due >= @DueDateStart) AND (@DueDateEnd IS NULL OR Due <= @DueDateEnd) AND (@GroupDueStart IS NULL OR DesiredDue >= @GroupDueStart) AND (@GroupDueEnd IS NULL OR DesiredDue <= @GroupDueEnd) AND (@ActualDueStart IS NULL OR ActualDue >= @ActualDueStart) AND (@ActualDueEnd IS NULL OR ActualDue <= @ActualDueEnd) AND (@CompleteStart IS NULL OR Archived >= @CompleteStart) AND (@CompleteEnd IS NULL OR Archived < dateadd(d, 1, @CompleteEnd)) AND (@GroupIdentifier IS NULL OR GroupId IN (SELECT GroupId FROM @Groups)) AND (@TaskIdentifier IS NULL OR Identifier LIKE '%' + @TaskIdentifier + '%') AND (@JeopardyCode IS NULL OR GroupJeopardyCodeId = @JeopardyCode) AND (@Queue IS NULL OR QueueId = @Queue) AND (@TaskType IS NULL OR TaskTypeId = @TaskType) AND (@TaskClass IS NULL OR TaskClassId = @TaskClass) AND (@Manager IS NULL OR QueueId IN (SELECT QueueId FROM @Queues)) AND ((@IncludeSupressed = 0 AND Sleeping = 0 AND Ready < GetDate()) OR @IncludeSupressed = 1)SELECT *FROM @ResultSELECT DISTINCT TaskClassIdFROM @ResultGO[/CODE]The view itself is fairly quick, and the execution plan is complaining about the insert step. I can post the view if needed. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-27 : 10:00:14
|
| My general advice: get rid of as many OR's in there as you can.For date range parameters, create @Start and @End variables for each. If no lower range is specified, set @Start = 1/1/1900 or something like that. If no upper range is specified, set @End = 1/1/2100 or something like that. Then, instead of all those NULL checks, you simply write DateColumn BETWEEN @Start and @End. This should really help your execution plan.You also have a few pieces of code like this:WHERE @GroupIndentifier IS NULL or GroupID IN (Select GroupID FROM @Groups)That code should probably be optimized as well, but I cannot logically make any sense of it. Perhaps add all Groups to @Group if @GroupIdentifier is null, and then simply JOIN to to it? This eliminates an OR and an IN() from the WHERE clause, both of which tend to cause performance issues.The overall concept: prepare as much as you can BEFORE the SELECT so that your WHERE clause is as simple as possible, using variables to help you out. This all also depends on your View, and how it is written and how the underlying tables are indexed. But what I've explained so far is a good start. Good date range criteria without the OR and NULL checks alone should really help, especially if your underlying date columns are indexed.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
gasMeetFoot
Starting Member
4 Posts |
Posted - 2007-07-27 : 10:20:31
|
| Thanks for the advice. The overall query could probably use some tuning. The @groups table eliminates a join by figuring out some parent-child relationships between orders that are in a separate table, and gives me a list of PKs from there that are FKs in the main table I am selecting off of. The ORs should not be a huge deal, because they are all null params coming in (at least in the context of the query we're talking about right now).However, the execution plan shows that on my insert into @Result, a compute scalar step is costing me 89% of the total processing time. The where clause is only 7% of my total execution time. If I can figure out _why_ that step happens in the insert, I can hopefully bring that back down, either by a change to the setup of the @Results table or... something else.The original query that I inherited from another developer had two of the exact same where clause; one for the select * and one for the select distinct. I am trying to shortcut that but selecting to memory so that the distinct can just be decided on a much smaller set. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-07-27 : 11:04:12
|
| "...a compute scalar step"....is there any data (type) conversions taking place...do your variables match the paired columns? |
|
|
|
gasMeetFoot
Starting Member
4 Posts |
Posted - 2007-07-27 : 13:10:19
|
| Actually, I think that is exactly what it is. I noticed afterward that there is a difference in how I am creating it and what is being read. Since the difference is on the column from the first post that compute scalar was complaining about, I am 99% sure that is what happened. Our DBA rebuilt the table a few nights ago, so I think the mixup came from there.Thanks, all! |
|
|
|
|
|
|
|
|
Compute scalar step on a Text column?