Saturday, 15 June 2013

sql server - Performance Issue in While Clause -


OK Everyone,

In advance, apologies for the length. It's really fun, though.

I have written to a SQL script that I was proud of yesterday because I thought that it is very clever. It is extinguished, performance issues are wasted, and I can not even test it because of it, so it can not be that which I feel sigh

This problem is best illustrated with the example:

column A | Column B | Column C | Column D
  heart | K | 2/1/2013 | 3/1/2013 heart | K | 2/1/2013 | 3/1/2013 heart | K | 1/1/2013 | 3/1/2013 heart | K | 2/1/2013 | 4/1/2013 spade | 4 | 2/1/2013 | 3/1/2013 spade | 3 | 2/1/2013 | 3/1/2013 Club | 4 | 2/1/2013 | 3/1/2013   

With this table, I need to: 1. Start with the first time, then update the row with the data, if in column A match Value, 2. Delete if there was a match, then the second line after the update, and 3. proceed to the next line, if there is no match and the same process starts again.

If there is a match, then the top row update column A: nothing

  • Column B.: If both values ​​are the same, put the value in one, else type 'multiple'.
  • Column C: Keep the first date between two,
  • Column D: place the date between two,

    then I delete the lower line

    My example should result in the following:

    column A | Column B | Column C | Column D
      heart | K | 1/1/2013 | 4/1/2013 spade | Multiple | 2/1/2013 | 3/1/2013 Club | 4 | 2/1/2013 | 3/1/2013   

    To do all this I create two table variables, insert the same data into both, and then update the bicycle through the second (@ScheduleB) In the first table (@ScheduleA) in the search for the match, I was then removed from the row in @ A (because it is similar to B). In the end, when there were no matches, I went to start a process in AA in the front row. At least this is what the code should do - see below.

    Displaying the problem is dangerous I have considered using a cursor, but I do not know what the display will help there.

    Any suggestions? Announced @ScheduleA table (RowNumber integer, period nvarchar (max), program nvarchar (max), ControlAccount Nchar (50), WorkPackage Nchar (50), CAM Nchar (50), EVM Nchar (50), Duration of time, BLStart datetime, BLFinish datetime) declared @ScheduleB table (RowNumber integer, duration nvarchar (Max), program nvarchar (max), ControlAccount Nchar (50), WorkPackage nchar (50), CAM nchar (50) , EVM nchar (50), duration integer, BLStart datetime, BLFinish datetime) Include more than [ROW], Duration, Program, ControlAccount, WorkPackage, CAM, EVM as @ScheduleA ROW_NUMBER () , a Method, from which the Program = @Program and Period = @Period inserted probe @ScheduleB, ROW_NUMBER (greater than the order by workpackage desc) [ROW], period, program, ControlAccount, WorkPackage, CAM, Become a BLFinish from EVM, Duration, BLstent, Scheduled Data, where Program = @ Programs and Duration = @ Announcement @IntIt = 1, declare @JyInte = 2 - Create a loop, another variable which is counted as the last line of B table While @J.L.L.; (Select Max (ROWNUMBER) + 1 @ ScheduleB) - Match table by WorkPackage - If start (if you select WorkPackage from @ScheduleA (where RowNumber = @i) = (@ScheduleB, select workpackage where RowNumber = @ J) Begin updating @ScheduleA --Update Schedule CAM, BLStart, set a table (if necessary) of BLFinish = Episode of CAM - Case = ScheduleA @ScheduleA column B is based on the argument argument, in BLStart = Case - -set values ​​based on logic and column C, BLFinish = case - based on rationality @ schedule A column D value Define where to remove Rowenber = @ I @Schedule A where the rouenumber = @e + 1 set @ j = @ j + 1 - in the newest b end ELSE set in line @ i = @ e + 1 end

    EDIT: To clarify, Column B. is not an integer column, I was just using it as an example because the cards are pretty easy to understand. Since then, I am KK Updated columns to include.

    Based on your needs, I think such a solution will work:

      select [column A], in case when MAX ([column B]) & lt; & Gt; Minimum ([column B]) then 'multiple' ELSE cast (max [[column B]) NVHAR (10)) end, min ([column C]), max ([column D]) by table group [column A ]  Edit  

  • No comments:

    Post a Comment