Solved

Speedier , cleaner T-SQL code

Posted on 2000-02-28
3
261 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now