update range of rows specifed by date range in other table

[kaht]

I have a table (VPOFF) that has a row for each 15 minute increment an office is open. In another table (VPVAC) are rows that define the start and stop time for employee vacations. In one procedure I'm inserting some bulk vacation rows, and I need to increment the SFULL column by 1 in the VPOFF table for each 15 minute time increment covered by the vacation rows inserted. My first instinct was to write something roughly like:

[COLOR=blue]update[/color]
   VPOFF
[COLOR=blue]set[/color]
   SFULL = SFULL + 1
[COLOR=blue]where[/color]
   VACDATE >= ([COLOR=blue]select[/color] VACSTART [COLOR=blue]from[/color] VPVAC [COLOR=blue]where[/color] [COLOR=blue]timestamp[/color] = [i](the [COLOR=blue]timestamp[/color] [COLOR=blue]from[/color] the [COLOR=blue]bulk[/color] [COLOR=blue]insert[/color])[/i])
   and VACDATE < ([COLOR=blue]select[/color] VACSTOP [COLOR=blue]from[/color] VPVAC [COLOR=blue]where[/color] [COLOR=blue]timestamp[/color] = [i](the [COLOR=blue]timestamp[/color] [COLOR=blue]from[/color] the [COLOR=blue]bulk[/color] [COLOR=blue]insert[/color])[/i])

I understand why that doesn't work. The update doesn't know how to match up the VACSTART and VACSTOP fields since each subquery returns multiple values. I know that Uncle Rico would suggest using a cursor to loop through each of the vacation rows I just inserted and update VPOFF individually, but my RICOMETER tells me that's not a good idea. So, I took a bathroom break, because naturally all problems can be solved in the bathroom and wrote this when I got back to my desk:

[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #a (vacdate [COLOR=blue]smalldatetime[/color], sfull [COLOR=blue]tinyint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 09:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 10:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 11:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 12:00'[/color], 0

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 08:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 09:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 10:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 11:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-02 12:00'[/color], 0

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 08:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 09:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 10:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 11:00'[/color], 0
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] #a [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 12:00'[/color], 0

[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] (vacstart [COLOR=blue]smalldatetime[/color], vacstop [COLOR=blue]smalldatetime[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]select[/color] [COLOR=red]'2007-01-01 08:00'[/color], [COLOR=red]'2007-01-01 12:00'[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]select[/color] [COLOR=red]'2007-01-03 10:00'[/color], [COLOR=red]'2007-01-03 11:00'[/color]

[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]off[/color]

[COLOR=blue]update[/color]
   #a
[COLOR=blue]set[/color]
   sfull = sfull + 1
[COLOR=blue]where[/color]
   #a.vacdate in (
      [COLOR=blue]select[/color]
         a.vacdate
      [COLOR=blue]from[/color]
         #a [COLOR=blue]as[/color] a
         [COLOR=blue]inner[/color] [COLOR=blue]join[/color]
            @b [COLOR=blue]as[/color] b
         [COLOR=blue]on[/color]
            a.vacdate >= b.vacstart
            and a.vacdate < b.vacstop
   )
      
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #a
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #a

The last select statement shows all the correct rows modified, but the code doesn't run real fast (possibly something to do with using temp tables?). So, is there a better way to achieve this? #a is is a mock-up of my VPOFF table and @b is a mock-up of my VPVAC table. Like I said, the data returned at the end is correct in my example, but if I'm doing this the long way I'd like to know.

I need to wash myself clean of the Rico practices....

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[kaht]

Oh, and for the record, I inserted rows into #a in 1 hour increments instead of 15 mins on purpose. Figured it would be silly to go to that detail for this small example.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[ESquared]

do you have appropriate indexes?
what does the query execution plan tell you about how the joins are being performed?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[gmmastros]

I think the following is functionally equivalent to your query and will probably execute faster. Give it a shot and let us know how you make out.

[COLOR=blue]update[/color] #a
[COLOR=blue]set[/color]    sfull = sfull + 1
[COLOR=blue]from[/color]   #a [COLOR=blue]as[/color] a
       [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b [COLOR=blue]as[/color] b
         [COLOR=blue]on[/color]
            a.vacdate >= b.vacstart
            and a.vacdate < b.vacstop

-George

"the screen with the little boxes in the window." - Moron

 

[kaht]

Thanks George, I'd never used a [blue]from[/blue] on an update before, always been

update tablename set column = blah where foo = bar

I wondered how I'd perform a join since w/o a [blue]from[/blue] so I came up with the "in" workaround. Your solution was over 2x as fast, and less lines of code.

Thanks!

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

Cool. Glad I could help.

Now answer ESquared's question. What does the execution plan look like? Are indexes getting used?

-George

"the screen with the little boxes in the window." - Moron

 

[kaht]

I'm passing an array of dates that have to be bulk inserted into the SP via a string that I cut down and insert into a temp table. And when I try to run the execution plan on the stored procedure it gives me this error:

Server: Msg 208, Level 16, State 1, Procedure insertReserveWeeks, Line 26
Invalid object name '#tempResWeeks'.

Although, the stored procedure will run to completion. Is there a way to show the execution plan when using temp tables?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

Yeah... uh.... I dunno. Can you use a table variable instead? Of course, that may affect performance. How big is the array of dates?

-George

"the screen with the little boxes in the window." - Moron

 

[kaht]

How big is the array of dates?

Not big, at most it will be 15 different days.

I'll go try a table variable real quick.



Table variable takes about the same time to run, and allowed me to run the execution plan. Everything that hits a physical table is using a clustered index seek and takes less than 3% cost. The biggest hog of the whole SP is in an insert statement above the one I posted about. It references that same temp table I mentioned, and shows a 14% cost to the proc. Would it be worth throwing an index on a temporary table if it's only 15 rows long?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

What columns do you have in the temp table?

I'm thinking that it might be better to use a table variable instead? Since table variables are stored in memory, they are usually faster. Most people don't realize this, but you CAN put an index on a table variable, but only if you can create a primary key (unique data).

Ex:

Declare @Temp Table(Data smallDatetime [!]Primary Key[/!])

Insert Into @Temp Values('20020101')
Insert Into @Temp Values('20030101')
Insert Into @Temp Values('20040101')
Insert Into @Temp Values('20050101')
Insert Into @Temp Values('20060101')

You can even have composite keys.

Declare @Temp 
Table   (Data smallDatetime, 
        OtherData Int, 
        NotIndexed varchar(10) 
        [!]Primary Key(Data, OtherData)[/!])

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

>> Would it be worth throwing an index on a temporary table if it's only 15 rows long?

I don't know. But I do know that it would be worth your time to find out.

-George

"the screen with the little boxes in the window." - Moron

 

[kaht]

here's the temp table definition:

[COLOR=blue]declare[/color] @tempResWeeks [COLOR=blue]table[/color] (
   idcol [COLOR=blue]int[/color] [COLOR=blue]identity[/color] (0, 10) not null,
   resWeeks [COLOR=blue]smalldatetime[/color] not null [COLOR=blue]default[/color] 0
)

Not much goes in it at all....

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

just for giggles, try...

declare @tempResWeeks table (
   idcol int identity (0, 10) not null,
   resWeeks smalldatetime not null default 0 [!]Primary Key[/!]
)

-George

"the screen with the little boxes in the window." - Moron

 

[kaht]

I already did, didn't seem to speed it up - but... it's not really running unreasonably slow as it is. This bulk insert is something that each user will perform only once a year, so the implications aren't too dire....

Thanks again for all the help George

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson