Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    22

    Unanswered: adding a new record takes longer and longer -- archive? (was "Table help")

    Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
    I don't know a lot about sql server so any help or suggestions would be great

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's not really a lot of records

    And 4,000 a day is not a lot of transactions

    How do you "add" the rows?

    And what the Table DDL look like...post that, and don't forget the indexes, keys, contraints and triggers if you have them
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2004
    Posts
    22

    Here the table info

    Here's the info on the table that's causing the probs. We are updateing this table using an Access front end with odbc linked tables. Any help on how to maintain a table would be great.
    Cheers


    Code:
    CREATE TABLE [dbo].[calls] (
    	[CallID] [int] IDENTITY (1, 1) NOT NULL ,
    	[ContactID] [int] NOT NULL ,
    	[CallDate] [datetime] NULL ,
    	[CallTime] [datetime] NULL ,
    	[Callername] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
    	[Notes] [ntext] COLLATE Latin1_General_CI_AS NULL ,
    	[CallerCompany] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[ActionTakenID] [int] NULL ,
    	[Confirmed] [bit] NULL ,
    	[EmployeesID] [int] NULL ,
    	[ShortMessage] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
    	[CallerNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
    	[Charged] [bit] NULL ,
    	[OperatorID] [int] NULL ,
    	[DDI] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
    	[EmployeeName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    	[OperatorName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[calls] WITH NOCHECK ADD 
    	CONSTRAINT [PK_calls] PRIMARY KEY  CLUSTERED 
    	(
    		[CallID]
    	)  ON [PRIMARY] 
    GO
    
    ALTER TABLE [dbo].[calls] ADD 
    	CONSTRAINT [DF_calls_Confirmed] DEFAULT (0) FOR [Confirmed],
    	CONSTRAINT [DF_calls_Charged] DEFAULT (0) FOR [Charged]
    GO

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My guess is that you're using a bound form to this table. And this is an MDB not an Access Data Project.

    I would make it unbound, use sql to populate the unbound controls, then when they need to do an update or insert, perform them as regular sql calls using the values from the controls.

    As for the table...there's not really much wrong.

    I would do the following though

    1. Combine the CallDate and CallTime Fields. It's redundant.
    2. Whats the largest value in the ntext column. I'd use varchar if possible
    3. A caller name of 255? This has got to be an Access default. Make it shorter. Better yet, make 2 columns. Last Name and First Name. Add a Third for Title, Mr/Mrs/Ms, ect
    4. Bit...I never liked bit...I don't think it's ANSI and does not cross platforms well
    5. varchar(4)...anything less than ...say 15 should be char...
    6. Employee Name, Operator Name...same thing...you should use first and last
    7. And identity as a PK? There's got to be a better one. CallerLastName, CallDate, CallerFirstName perhaps. Just plave a unique index un the id.

    Now None of these things should be impacting your performance...maybe ntext (and why unicode, why not just text?). ALL of my money is on the Access ODBC nound form.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2004
    Posts
    22

    Thanks

    As you guesed the sql database is an bit of a bodged upgrade from a access database, so needs cleaning up, am waiting for my boss to send me on some sort of course though.
    As you also guesed we are using access bound forms, think I might have to bite the bullet a have been considering upgrading to a vb.net front end for a while, but don't think my programing skills are really up to it, is much more work than going to an access project, but may as well go the full hog and dump access.
    Have just dome some testing and it updates like lightning from vb.net.

    Any more general advice would be great (if people have nothing to says will mark of a resolved in a few days)

    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
  •