SQL Server Performance

Deleting rows in a table.

Discussion in 'Performance Tuning for DBAs' started by vvkp, Jun 29, 2004.

  1. vvkp New Member

    Hi friends,
    I have one table with 34 columns. We are importing data from a flat file thru Bulkinsert. After that we need to delete some rows which contains 25 columns are ALL ZERO from the table which takes lot of time. The table has no indexes. How to optimize the delete operation. Any idea? In Oracle they have some Function based indexes. Here in sql server I beleive we don't have such type. Any idea to optimize the delete operation? Thanks in advance.
  2. vbkenya New Member

    Before we embark on a better way of deleting the ZERO-value columns, wouldn't it be better to make sure that this kind of data doesn't come into SQL Server in the first place? Cleaning and/or filtering the source data before the import may prove to be cheaper in the long run. DTS may be of help here.



    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  3. vvkp New Member

    I agree, but bulk Insert task does more quickly than data copy in DTS. Almost half of the time. So if I have any way to delete the rows in a better way then I shall go for Bulk Insert only. That is why I am asking, to optimize the delete operation.
  4. satya Moderator

    If the no. of rows required are less than the to be deleted rows then simply use DTS to insert only those rows with the query to the table from this temp table and truncate after the process is finishes.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. vvkp New Member

    can you eloborate a little bit more "...use DTS to insert only those rows with the query to the table from this temp table and truncate after the process is finishes.
    ..." because I am new to this area. How can we query while data is from a flat file.
    thanks,
  6. satya Moderator

    I meant to say import the rows to the temp table and use a query from the temp table to import into original table.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. SQL_Guess New Member

    vvkp: Is there any way you can change the process that creates the file to not add the invalid rows? Is it an export from a DBMS, or a file built by an app that can do this logic for you?

    Panic, Chaos, Disorder ... my work here is done --unknown
  8. vvkp New Member

    No way we can change the process...its a file built by an app...we can not apply the logic at file built stage.
  9. vvkp New Member

    Can anybody help in writing a stored procedure which does the bulkinsert to temp table from a text file with delimited, ANSI filetype, ROW delimiter {CR}{LF}, Text qualifier Double quote {"} , comma column delimiter.I am getting lot of syntax errors because i am new. thanks in advance.
  10. Chappy New Member

    you will need to define a formatfile and pass this to bulk insert. Read about 'bcp' in books online (see start menu). The formatfile for bulk insert is identical to that used by bcp.

  11. vvkp New Member

    [?][?][?]
  12. satya Moderator

    As referred by Chappy review topic BULK INSERT, FORMATFILES topic under SQL server books online for syntax and other information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. vvkp New Member

    if I am doing like that I am getting the following error

    The statement has been terminated.
    OLEDB provider 'STREAM' reported an error. The prodvider didnot give any information about the error.
    OLEDB error trace [OLE/DB Provider 'stream' IRowset::GetNextRows returned 0x80004005: The provider didnot give any information about the error.].

    bulk insert: unexpected end-of-file(EOF) encountered in data file

    Any solution please?


    The following is the code in the task:

    ===================================

    CREATE TABLE #tmp_fct_Sales (
    [Time_DimID] [int] NOT NULL ,
    [Product_DimID] [int] NOT NULL ,
    [GrossSales] [decimal](15, 3) NULL ,
    [Lbs] [decimal](15, 3) NULL ,
    [Cases] [decimal](15, 3) NULL ,
    [LoadHistory_DimID] [int] NOT NULL
    ) ON [PRIMARY]

    GO

    BULK INSERT #tmp_fct_Sales FROM 'C:Test.txt'
    WITH (FORMATFILE = 'C:cp.fmt')
    Go
    =====================================
  14. Chappy New Member

    bcp may think its reached the end of the file prematurely because your format file is incorrect.
    paste the format file you are using, they are quite fiddly to create manually.

    Sometimes it is helpful to export FROM the destination table using bcp, and ask it to create a format file for you. Then if your file differs from this in any way, you can edit the format file.

  15. satya Moderator

  16. vvkp New Member

    Thanks for your help.Here is the format file I am using

    8.0
    34
    1 SQLINT 0 4 "
    " 1 Time_DimID ""
    2 SQLINT 0 4 "
    " 2 Product_DimID ""
    3 SQLDECIMAL 0 19 "
    " 3 GrossSales ""
    4 SQLDECIMAL 0 19 "
    " 4 Lbs ""
    5 SQLDECIMAL 0 19 "
    " 5 Cases ""
    6 SQLINT 0 4 "
    " 6 LoadHistory_DimID ""


    when I import the file thru import wizard it comes like this
    Delimited highlighted, filetype: ANSI, Row delimiter as {CR}{LF} and text qualifier Double Quote{"} and after that when I click next then comma is highlighted as column Delimiter.

    please let me know if any information is missed.
  17. vvkp New Member

    By using the command... bcp "Northwind.[dbo].[Sales]" out "sales.txt" I am exporting data to the sales.txt file and when I try to import data from that file, its throwing error that delimiter is not valid. Can any body guide me with the bcp format of a file which has ...Delimited highlighted, filetype: ANSI, Row delimiter as {CR}{LF} and text qualifier Double Quote{"} and after that when I click next then comma is highlighted as column Delimiter.
  18. ozamora New Member

    the simplest way for me is to dump the resultset of "wanted" rows into another temp table and drop the loading table.

    SELECT * FROM table1
    INTO table2
    WHERE condition_used_in_the_delete_is_not_true

    And work with table2 after that. Plain and simple.

    I use tempdb for all my loading and temp tables.
  19. vvkp New Member

    I appreciate your help...I am asking help to do that only (copying data from flat file to temp table).But till now no luck my friend.
  20. vvkp New Member

    Oops...no body is there to help me regarding the file format?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page