Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Location
    Cologne
    Posts
    4

    Unanswered: OSQL Performance Problem

    System info:
    Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)

    Table info:
    DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
    DocId has a number, all other columns are null. No index

    I am using a stored procedure that updates the values based on the DocId. I have an program that creates a sql script file that should be executed. Approx. 440000 lines.

    Example:
    Using TableName
    Go
    SET NOCOUNT ON
    GO
    exec sp_SPNAME @docId=1, @a = 'blah', @b = 'blah', @c = 'blah', @d = 'blah', @e = 'blah'
    GO
    exec sp_SPNAME @docId=2, @a = 'blah', @b = 'blah', @c = 'blah', @d = 'blah', @e = 'blah'
    GO
    repeats 440K.

    Question: When I execute this script per osql.exe, the update takes more the 24 hours... Any suggestions?

    Thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by lens
    System info:
    Win 2003 Server, SQL Server 2000, test server - no outside access from other users (no other applications running)

    Table info:
    DocId(int), a(nvarchar(10)), b(nvarchar(20)), c(nvarchar(10)), d(nvarchar(20)), e(nvarchar(10))
    DocId has a number, all other columns are null. No index
    INDEX !!!

    Put a clustered index on DocID.

    Code:
    CREATE CLUSTERED INDEX IXc_TableName_DocID ON TableName (DocID)
    GO
    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    May 2002
    Posts
    299
    another route is to bulkcopy/insert all the new data into a table then do a single update against the base table. Index on docid would be desired when you start dml.
    --
    -oj
    http://www.rac4sql.net

  4. #4
    Join Date
    Oct 2005
    Location
    Cologne
    Posts
    4
    Hi hmscott,

    thanks for the reply and the sql. I added the index and, while it is much faster, it still takes more than 8 hours. Maybe this is normal for executing 440,000 statements?

    Thanks,
    Lens

  5. #5
    Join Date
    Oct 2005
    Location
    Cologne
    Posts
    4
    Hi oj,

    thanks to you as well for the reply. I will change my program to make a csv file and see if a bulk update increases the speed.

    Thanks,
    Lens

  6. #6
    Join Date
    Oct 2005
    Location
    Cologne
    Posts
    4
    Hi,

    just a quick status update. I changed my program to create a csv-file. Approx. 440,000 lines imported into temp table; less than 2 minutes. Update into final table, less than two minutes....zoinks.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •