Post reply

Script is filling Tempdb

  • December 11, 2024 at 5:38 pm

    #4496752

    I have query which is filling TEMPDB

    I need suggestion how to modify this query to which can minimize the TEMPDB space to fill

    update #TempShow1

    set Name = case when a2.ID is NULL then a1.name else a2.name end,

    Address = case when a2.ID is NULL then a1.Address else a2.Address end,

    State = case when a2.ID is NULL then Student.dbo.Mark(a1.state) else student.getClean(a2.state) end

    from #TempShow1 t left join #INFO a1

    on t.clientID = a1.clientID

    and a1.Type = 1

    left join #INFO a2

    on t.ID = a2.ID

    and a2.type = 2

  • December 11, 2024 at 5:57 pm

    #4496771

    Nita Reddy wrote:

    I have query which is filling TEMPDB

    I need suggestion how to modify this query to which can minimize the TEMPDB space to fill

    update #TempShow1 set Name = case when a2.ID is NULL then a1.name else a2.name end, Address = case when a2.ID is NULL then a1.Address else a2.Address end, State = case when a2.ID is NULL then Student.dbo.Mark(a1.state) else student.getClean(a2.state) end from #TempShow1 t left join #INFO a1 on t.clientID = a1.clientID and a1.Type = 1 left join #INFO a2 on t.ID = a2.ID and a2.type = 2

    Use a permanent table instead of #TempShow1.

  • December 11, 2024 at 5:59 pm

    #4496773

    Ok thanks I will try

     

  • December 11, 2024 at 6:11 pm

    #4496780

    Phil Parkin wrote:

    Nita Reddy wrote:

    I have query which is filling TEMPDB

    I need suggestion how to modify this query to which can minimize the TEMPDB space to fill

    update #TempShow1 set Name = case when a2.ID is NULL then a1.name else a2.name end, Address = case when a2.ID is NULL then a1.Address else a2.Address end, State = case when a2.ID is NULL then Student.dbo.Mark(a1.state) else student.getClean(a2.state) end from #TempShow1 t left join #INFO a1 on t.clientID = a1.clientID and a1.Type = 1 left join #INFO a2 on t.ID = a2.ID and a2.type = 2

    Use a permanent table instead of #TempShow1.

    Or make tempdb bigger.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy