Solved

Speedier , cleaner T-SQL code

Posted on 2000-02-28
3
266 Views
Last Modified: 2012-06-27
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
Comment
Question by:xoxomos
3 Comments
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2567413
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
 
LVL 2

Accepted Solution

by:
formula earned 100 total points
ID: 2568698
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
 

Author Comment

by:xoxomos
ID: 2571441
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
Sql Server group by 10 27
Create snapshot on MSSQL 2012 3 18
MSSQL Import .SQL files - Increase Import Speed 9 38
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question