[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Speedier , cleaner T-SQL code

I've got a table which i want to populate with information from two sources.
All except one column come from one source and i am using a join to determine which rows should be modified with information in the last column.
I've got this procedure which takes too long to run plus i'm using an UPDATE
statment and i shouldn't because it's supposed to be data warehouse.  
I want some code that will let me put a value in the waitlist_position colum without using UPDATE, perhaps somehow using INSERTs instead and if possible execute a bit faster.

DECLARE @number_waitlisted int
DECLARE @rowcount int
DECLARE @filexists int
DECLARE @filecopied int
EXEC @filexists = master..xp_cmdshell "dir d:\inetpub\ftproot\dwfact"
 IF @filexists = 0
   BEGIN
     EXEC @filecopied = master..xp_cmdshell "copy d:\inetpub\ftproot\dwfact d:\mssql\ftpdir\units"
     IF @filecopied = 0
       BEGIN
          CREATE TABLE #bcpunits(bcpoutput varchar(255) NULL)
          CREATE TABLE #units
          (
         SECTION_KEY varchar (15) NOT NULL ,
         STU_KEY int NOT NULL ,
         TERM varchar (5) NOT NULL ,
         .
         .
         FROW_COUNT int NOT NULL,
                  WAITLIST_POSITION varchar (2) NULL
            )
       

       
        INSERT INTO #bcpunits
          EXEC master..xp_cmdshell 'bcp Tempdb..##units in d:\mssql\ftpdir\units -f d:\mssql\ftpdir\units.fmt -Usa -Psss'

           
          DELETE FROM units
          WHERE term IN
          (SELECT term
           FROM#units)

     
         INSERT units
            EXECUTE ( "SELECT
              SECTION_KEY ,
              STU_KEY ,
            TERM  ,  
                      ENRL_WAIT  ,
            .
                      .
            OPNU_UNITS,
            FROW_COUNT,
                      ' '
          FROM ##units" )
           
     
       
          EXEC @filexists = master..xp_cmdshell "dir d:\inetpub\ftproot\waitlist"
          IF @filexists = 0
             BEGIN
               CREATE TABLE #bcpwaitlist(bcpoutput varchar (255) NULL)
               CREATE TABLE ##waitlist
                (
                 numberwaitlisted     char (2) NOT NULL,
                 waitlist_position    char (2) NOT NULL,
                 student_id           char (9) NOT NULL,    
                 pin                  char (4) NOT NULL,
                 name                 char (32) NOT NULL,
                 course_section       char (11) NOT NULL
                )    


              INSERT INTO #bcpwaitlist  
               EXEC master..xp_cmdshell 'bcp Tempdb..##waitlist in d:\mssql\ftpdir\waitlist -f d:\mssql\ftpdir\waitlist.fmt -Usa -Pssssr'
           
              CREATE INDEX idx_temp_units ON  Tempdb..##units (section_key, stu_key)
              CREATE INDEX idx_waitlist ON  Tempdb..##waitlist (course_section, student_id)
         

              SELECT @number_waitlisted = (SELECT COUNT(*) FROM #waitlist)

             

              IF @number_waitlisted > 0
                 BEGIN
                    UPDATE#units
                    SET ##units.waitlist_position =
                   ( select #waitlist.waitlist_position
                     FROM #waitlist, student
                     WHERE #units.stu_key = student.stu_key
                     AND student.stu_id =#waitlist.student_id
                     AND#waitlist.course_section = substring(#units.section_key, 6, 15))
                 END
           END
        END
     .
     .
     .
0
xoxomos
Asked:
xoxomos
1 Solution
 
Jon_RaymondCommented:
I think you shouldn't use tranaction begin and end statements.  The transactions are creating locks on the data being queried.  Especially with bcp (You're locking the entire tables while the data is being copied - of course that is necessay with BCP but you shouldn't specify tranactions because it locks tables for the entire transaction instead of just for the BCP operation).  Also, I don't think you should use so many shell statements - in fact none would be good.  Why not do those operations by calling other stored procedures instead?  But, start with removing the transactions and see if performance improves.
0
 
formulaCommented:
The transactions are all implicit, you are just using begin and end blocks for the "if", which is OK.   Calls to xp_cmdshell are OK to, but if this is a lot of data, BCP may slow things down a bit.  You might make sure you are using a fast BCP mode, i.e. write to a table with no indexes, then create the indexes afterward.  I think you are doing this, but just be sure.

My suggestion is to try to isolate where the slowdown is occurring by commenting out portions of the code.  I would paste in a ISQL window and test  it, commenting out a little at a time.  You could you "showplan" to assist.  Your idea seems correct,  BCP in the two sources of data to temporary tables, then update the destination table with the data from the #temps.  There are some other things in the procedure that I don't quite understand, but they shouldn't affect performance.  Also, in a DW, if your updating a table that originates on a source system, you might want to consider another method.  For example, create a separate table you can join to the original, then allow users to access both via a view.  The users won't know the difference and you'll maintain DW integrity.  Just a thought!!
0
 
xoxomosAuthor Commented:
Thanx, i needed that.  Tried Jon Raymond's suggestion about having multiple procedures and it seems to run noticibly faster.  More stuff i don't understand.  Have to do it some more to make sure i'm not hallicinating it though then i'll try your isolation technique.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now