Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server Loading (Access 2k3)

    I have an application that has an Access 2k3 front end and a SQL Server 2005 backend. We have converted to this from an Access front end and Access back end in order to overcome some Access file size limitations.

    We get client's data ,usually in text files, that we filter with access and load into the SQL tables using Append queries that we create in Access as needed. The Append queries take up to 30 minutes to load a table with 1.5 million records. It takes longer to do this in SQL Server than it used to take when we were using Access as the back end. Is there any way to speed up the data load in SQL Server?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Loading (Access 2k3)

    There are at least a couple of factors that could be causing the slowness.
    Are the front-end and the sql database on the same server?...the same domain?....How's the network traffic?
    Are you using DAO or ADO?...ODBC, dsn or dsn-less connection?
    As an alternative, have you considered using a stored procedure?
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Loading (Access 2k3)

    I have some using SQL Express and Access on the same workstation. Some are using Access on the workstation and SQL Server on a server in the same domain. Both are using DSN-less connections.

    What are you suggesting regarding a stored procedure?

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Loading (Access 2k3)

    Sorry for the delay in replying, I had to attend to personal concerns.

    There are 2 "conventional" methods which I have used. The following (which is no longer the "recommended" method, but will still work) with sqlserver 2000. This used CharIndex, which is kinda like using the InStr function, to find the position(s) of the column delimiter. Substring will return the value between the delimiters.

    syntax:
    CHARINDEX ( WhatYouAreSearchingFor ,WhatYouAreSearchingIn [ , start_location ] )
    SUBSTRING ( WhatYouAReSearchingIn ,SubString_start_location , LengthOfExpressionReturned )

    --First delimter instance
    select charindex(',',columnname) from test1

    --Second delimter instance
    select charindex(',',columnname,charindex(',',columnname) +1) from test1

    --
    select left(columnname,charindex(',',columnname)-1) as variableA,
    substring(columnname,charindex(',',columnname)+2, charindex(',',columnname,charindex(',',columnname) +1) - charindex(',',columnname) -2) as VariableC,
    right(columnname, len(name)-1-charindex(',',columnname,charindex(',',columnname) +1)) as VariableD
    from test1

    and then Insert Into from here.
    ---

    This however, can be real klutzy, when the number of columns get even a little bit large.

    The new recommended method uses OpenRowSet, found here.

    OpenRowSet method

    However, in suggesting an sproc, I failed to take into account new and improved sql server tools. SSIS (SQL Server Integration Services), which replaces the DTS tool (you can also use BCP (Bulk Copy Program), a command-line utility, but I am not comfortable with it so have never used it)
    With SSIS, using the SSBIDS (SQL Server Business Intelligence Development Studio, a "specialized" instance of VS.NET 2005) you can create a package in which you can import, parse, convert, format any number of formats and is very handy for "only" importing data. This is one of the more simple and straightforward uses of SSBIDS. It is part of the SQLServer 2005 installation disk.

    Introducing Business Intelligence Development Studio

    Create a new project in SSBIDS and rename it to something useful to you. In the Solution Explorer under SSIS packages, the default package (Package.dtsx) is highlighted (open) and you'll find 4 tabs across the top of the designer window. Control Flow should be highlighted. Click and drag the Bulk Insert Task tool from the toolbox and when it is placed, right click and click on Edit. This will let you choose the Task name, Task description, server destination, how the text file is formatted, text file location and quite a few Options/Advanced Options. Once created you can call this package from an sproc or run it from SSBIDS.
    This is very fast becoming my preferred method of populating/loading new data into SQL Server tables.

    Hope this helps.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Loading (Access 2k3)

    Thank you for your suggestions. In the meantime I have been experimenting with pass-through queries and this seems to have solved my problem. I will investigate your suggestions further.

    Tom

Posting Permissions

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