Post reply

inserts and relational integrity

  • November 29, 2005 at 8:18 am

    #89977

    I'm looking for feedback on using tsql to load a highly normalized database where the source data is coming from legacy foxpro databases.  Aren't there advantages to using Tsql for this when the data is already clean - names parsed, business rules already applied?  The alternative, which is now being used, is to apply the same code that handles new data coming from our clients -- specifically that means data being converted from foxpro is handled by C# .net code running on the web servers in the same way as fresh data coming from clients.  Note: the foxpro and sql database schemas are not similar at all.

    More details below for those interested.

    We're in the middle of a years long migration from Foxpro to Sql 2000.  We're finding that the huge amount of business logic written in C# is ok for day to day processes, but when converting large client data sets from FoxPro to sql the current rate of 25,000 records per hour won't do.

    Since the "status" of a record coming from FoxPro is already known I believe the conversion logic would be simpler than what the C# must handle for new records from our clients.

    I'm told that each FoxPro record ( not a normalized database ) creates 50 records in the highly normalized sql database.  Eventually we will get to the conversion of our larger clients with 1.5 million records and they need to be converted over a weekend.

    We have one of the "new approach" solutions -- .net C# running on web servers -- tsql is generated on the fly by an object -- virtually no use of stored procedures. Database schema created by a tool ( visio I think ) so extreme normalization with very long field names.  I wasn't involved in the design of this, just working on the conversion piece now. 

    SQL 2005 is being considered now but I'm not sure what it has that would make these conversions any faster.

    Randy

  • December 1, 2005 at 4:11 am

    #606987

    Plz don't cross post - I think this was part of yesterday's forums emails?  Can't find the link at the moment though

  • December 1, 2005 at 4:42 am

    #606996

    DTS/SSIS

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply