Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Timeout problem

  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: Timeout problem

    Ok, I have a WinXP machine running Microsoft SQL 2000 at work. THis is a telecom company and this DB has thousands of CallDataRecords which are uploaded to the db from csv files. I use a stored procedure to bulkly add all the data to the db.

    Now the CSV files are in the same machine and i'm not taking them from a network PC... But still while importing the data to the DB I suddenly get a Timeout error... I chek Task Manager and see the page file has reached abt 1.5 GB... I thought it was a problem with RAM cuz I'm importing abt atleast 10,000 records to it... so I upgrade the RAM and now its got 2GB of RAM... but still no good.. The Timeout problem persists...

    What information have i missed out here? waht do u think is the problem?

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    is the proc using a call to BULK INSERT or is it doing something else?

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    so where exactly is the timeout happening? is it occurring on queries hitting the tables that are being loaded during the load process or is it the load process timing out?

    how are you loading them "bulkly"? Can you post the stored procedure? Is it using bcp or BULK INSERT?

    edit: sniped at 1:30am. damn it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Apr 2008
    Posts
    3
    The time out happens when loadin the files... not dudring queries while it loads...

    i'm not using BULK INSERT
    I suspected this was somin to do with memory before... well after the ram upgrade PC is a little bit more faster even after startin to load the files to DB...

    If I re-index all the tables using DBCC then that buys be some more time before the timeout error... but re-indexing all the databses takes like 2.5 hrs.. and it still dont fix my problem.. just gives me a lil more time

    heres the stored procedure...

    CREATE PROCEDURE [dbo].[rec_insert_@tableName]

    @ServiceKey int,

    @SubscriberType int,

    @CallType char(1),

    @ChargeType char(1),

    @RoamFlag char(1),

    @CallingPartyNumber nvarchar(20),

    @CalledPartyNumber nvarchar(20),

    @RoamAreaNumber nvarchar(4),

    @CallBeginTime nvarchar(14),

    @CallDuration int,

    @Normalfee int,

    @ChargePartyIndicator char(1),

    @CallingVAreaNumber nvarchar(4),

    @CalledVAreaNumber nvarchar(4),

    @TerminationReason int,

    @Normalaccountbalanceaftercall int,

    @ServiceType char(1),

    @OriginalCalledParty nvarchar(20),

    @Discount int,

    @Promotedfeespenincalling int,

    @Promotedbalanceaftercall int,

    @Submsisdn nvarchar(32),

    @BrandID int,

    @CAC nvarchar(14),

    @Payment char(1),

    @FreeChgTime int,

    @GrpNo int,

    @CallingCellID nvarchar(20),

    @CalledCellID nvarchar(20),

    @CallReferenceNumber nvarchar(16),

    @RecFlag nvarchar(16),

    @ForwardFlag char(1),

    @ldaf int,

    @icf int,

    @tf int,

    @ff int,

    @vmu nvarchar(10),

    @fct2 nvarchar(9),

    @fct3 nvarchar(9),

    @wait_dura nvarchar(9),

    @bearer_cap nvarchar(9),

    @Reserve1 nvarchar(9),

    @Reserve2 nvarchar(9),

    @Reserve3 nvarchar(9),

    @Reserve4 nvarchar(9),

    @Reserve5 nvarchar(9),

    @Reserve6 nvarchar(9),

    @Reserve7 nvarchar(9),

    @Reserve8 nvarchar(9),

    @Reserve9 nvarchar(9),

    @Reserve10 nvarchar(9),

    @Reserve11 nvarchar(9),

    @Reserve12 nvarchar(9),

    @Reserve13 nvarchar(9),

    @Reserve14 nvarchar(9),

    @Reserve15 nvarchar(9),

    @Reserve16 nvarchar(9),

    @Reserve17 nvarchar(9),

    @Reserve18 nvarchar(9),

    @Reserve19 nvarchar(9),

    @Reserve20 nvarchar(9),

    @Reserve21 nvarchar(9),

    @Reserve22 nvarchar(9)

    AS

    INSERT INTO @tableName(ServiceKey, SubscriberType, CallType, ChargeType, RoamFlag, CallingPartyNumber, CalledPartyNumber, RoamAreaNumber, CallBeginTime, CallDuration, Normalfee, ChargePartyIndicator, CallingVAreaNumber, CalledVAreaNumber, TerminationReason, Normalaccountbalanceaftercall, ServiceType, OriginalCalledParty, Discount, Promotedfeespenincalling, Promotedbalanceaftercall, Submsisdn, BrandID, CAC, Payment, FreeChgTime, GrpNo, CallingCellID, CalledCellID, CallReferenceNumber, RecFlag, ForwardFlag, ldaf, icf, tf, ff, vmu, fct2, fct3, wait_dura, bearer_cap, Reserve1, Reserve2, Reserve3, Reserve4, Reserve5, Reserve6, Reserve7, Reserve8, Reserve9, Reserve10, Reserve11, Reserve12, Reserve13, Reserve14, Reserve15, Reserve16, Reserve17, Reserve18, Reserve19, Reserve20, Reserve21, Reserve22)

    VALUES(@ServiceKey, @SubscriberType, @CallType, @ChargeType, @RoamFlag, @CallingPartyNumber, @CalledPartyNumber, @RoamAreaNumber, @CallBeginTime, @CallDuration, @Normalfee, @ChargePartyIndicator, @CallingVAreaNumber, @CalledVAreaNumber, @TerminationReason, @Normalaccountbalanceaftercall, @ServiceType, @OriginalCalledParty, @Discount, @Promotedfeespenincalling, @Promotedbalanceaftercall, @Submsisdn, @BrandID, @CAC, @Payment, @FreeChgTime, @GrpNo, @CallingCellID, @CalledCellID, @CallReferenceNumber, @RecFlag, @ForwardFlag, @ldaf, @icf, @tf, @ff, @vmu, @fct2, @fct3, @wait_dura, @bearer_cap, @Reserve1, @Reserve2, @Reserve3, @Reserve4, @Reserve5, @Reserve6, @Reserve7, @Reserve8, @Reserve9, @Reserve10, @Reserve11, @Reserve12, @Reserve13, @Reserve14, @Reserve15, @Reserve16, @Reserve17, @Reserve18, @Reserve19, @Reserve20, @Reserve21, @Reserve22)

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    so is this a connection timeout, a command timeout or a sql timeout.

    Instead of inserting these records one at a time, it would be much faster to use BULK INSERT or the Bulk Copy Program (bcp) to insert them all at once.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Apr 2008
    Posts
    3
    It's a connection time out but we don't know why this should happen because both the csv files and the sql server is in the same machine. We have also set the connection timeout setting to maximum possible.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    If you use BCP or the BULK INSERT T-SQL statement instead of doing the inserts one gat a time, this operation will run a lot faster by order of a few magnitudes.

    Also, why are you using a table variable for this and is there a clustered index on the desitination table.

    A telecom huh? Sprint and Verizon recruiters have been ringing my phone lately.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that proc won't even compile as @tableName is declared nowhere. even if it were declared the proc would be a noop.

    it can't be the whole story.

  9. #9
    Join Date
    Apr 2008
    Posts
    9
    I'm the guy who is programming this. Sick is the guy who is maintaining DB. The stored procedure is actually stored in a text file. So that I don't have to alter my code every time i need to make a change to the stored procedure. The @tableName is not really a variable. What I do is I read the text file into a string and replace the @tableName with whatever the name I wanna give to stored procedure. What actually happens is we need to create a table for each month in the DB so for each table I'm creating a stored procedure. So the @tableName variable will be replaced with, um, let's say rec012008. So the stored procedure name will be rec_insert_rec012008.

  10. #10
    Join Date
    Apr 2008
    Posts
    9
    Here is the code I use to create the table

    CREATE TABLE [dbo].[@tableName] (
    [ServiceKey] [int] NULL ,
    [SubscriberType] [int] NULL ,
    [CallType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ChargeType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RoamFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CallingPartyNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CalledPartyNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RoamAreaNumber] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CallBeginTime] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CallDuration] [int] NULL ,
    [Normalfee] [int] NULL ,
    [ChargePartyIndicator] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CallingVAreaNumber] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CalledVAreaNumber] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TerminationReason] [int] NULL ,
    [Normalaccountbalanceaftercall] [int] NULL ,
    [ServiceType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OriginalCalledParty] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Discount] [int] NULL ,
    [Promotedfeespenincalling] [int] NULL ,
    [Promotedbalanceaftercall] [int] NULL ,
    [Submsisdn] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BrandID] [int] NULL ,
    [CAC] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Payment] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FreeChgTime] [int] NULL ,
    [GrpNo] [int] NULL ,
    [CallingCellID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CalledCellID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CallReferenceNumber] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RecFlag] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ForwardFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ldaf] [int] NULL ,
    [icf] [int] NULL ,
    [tf] [int] NULL ,
    [ff] [int] NULL ,
    [vmu] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fct2] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fct3] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [wait_dura] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [bearer_cap] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve1] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve2] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve3] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve4] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve5] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve6] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve7] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve8] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve9] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve10] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve11] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve12] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve13] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve14] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve15] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve16] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve17] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve18] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve19] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve20] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve21] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reserve22] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

  11. #11
    Join Date
    Apr 2008
    Posts
    9
    And here is the code i use to create the table indexes

    CREATE
    INDEX [IX_CALLED] ON [dbo].[@tableName] ([CalledPartyNumber], [CallBeginTime])

    ON [PRIMARY]

    CREATE
    INDEX [IX_CALLING] ON [dbo].[@tableName] ([CallingPartyNumber], [CallBeginTime])
    ON [PRIMARY]

  12. #12
    Join Date
    Apr 2008
    Posts
    9
    Anything else you might wanna know, just let me know.

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ok, so why are you using this one-row-at-a-time proc and not using BCP or BULK INSERT, since the data you are working with is csv?

    BCP would be much, much, much faster.

  14. #14
    Join Date
    Apr 2008
    Posts
    9
    Bulk insert works fine if I use the Server Manager. But the problem lies within my program. If I use bulk insert in my app, the server throws a file not found error. But if the files are located in the server itself and the app is also located in the server itself it works fine. But we want this app to be able to upload the data remotely.

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    does the client have bcp installed? bcp is a client tool that reads the file locally and sends rows to the server. file does not need to be on the server to use bcp.

    another option is SqlBulkCopy if your client app is written in managed code.

Posting Permissions

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