Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Database question?

  1. #1
    Low Bitrate
    Join Date
    Dec 2003
    Posts
    58

    Database question?

    Okay, here's a little background...
    I'm using an Access database to keep track of the MP3 files in the software I'm writing. To update the database, I'm looping through a list of files and using SQL statements to update or insert the records. As you can imagine, after about 1300 files, it really bogs down and takes forever.

    Is there a way to mass insert this information? Is there a method to pass an array of data at once instead of running an SQL statement for each individual file? I know there has to be a way but I can't find it anywhere on the internet!!

  2. #2
    Maximum Bitrate wi77iam's Avatar
    Join Date
    Jun 2003
    Location
    Chester Springs, PA
    Posts
    605
    Are you doing this with an Access macro or calling the SQL from a program like VB. With a macro, you can use the TransferText function to insert text from a file into a table. In VB, you can loop through a text file list of your music files and use RecordSet.Add to add them as records. Then sort the table after to get it in the right order.
    *******************************
    *******************************

  3. #3
    Low Bitrate
    Join Date
    Dec 2003
    Posts
    58
    I'm calling the SQL from VB. This is the code I'm using...

    Code:
        strsql = "SELECT * FROM PLAYLIST WHERE PATH = '" & Path & "'"
        rst.Open strsql, cnn, adOpenForwardOnly, adLockOptimistic
        If rst.EOF Then
            strsql = "INSERT INTO Playlist (Path, Artist, Album, Track, Genre, TrackNo, [Time]) SELECT '" & Path & "','" & artist & "','" & album & "','" & track & "','" & genre & "','" & trackno & "','" & time & "'"
            ost.Open strsql, cnn, adOpenForwardOnly, adLockOptimistic
            RaiseEvent NewFile(CurFile, NumOfFile, TotalNum)
            Set ost = Nothing
        Else
            strsql = "UPDATE Playlist SET Artist = '" & artist & "', Album = '" & album & "', Track = '" & track & "', Genre = '" & genre & "', TrackNo = '" & trackno & "', [Time] = '" & time & "' WHERE PATH = '" & Path & "'"
            ost.Open strsql, cnn, adOpenForwardOnly, adLockOptimistic
            Set ost = Nothing
        End If
        Set rst = Nothing
    It is inside a FOR loop that goes through all the MP3 files in the directory tree. I'm just trying to find a more efficient method of inserting/updating the database because running 4000+ individual SQL queries is taking forever.

  4. #4
    Newbie
    Join Date
    Feb 2004
    Posts
    14
    Is it necessary to loop through all the records with FOR loop?
    Why cant you use two tables (old and new) and two stored procedures (one for updating, and other for adding)?

  5. #5
    Low Bitrate
    Join Date
    Dec 2003
    Posts
    58
    Because I only have one table and an array with the data I want to insert/update into it.

    As for stored procedures, does a standard Access database have them? I've only seen them when I was working with SQL Server.

  6. #6
    I'm sorry, and you are....? frodobaggins's Avatar
    Join Date
    Jan 2003
    Location
    Ruston, LA
    Posts
    8,846
    jagreen2 PM me, preferably with MSN Messenger handle
    [H]4 Life
    My next generation Front End is right on schedule.
    It will be done sometime in the next generation.
    I'm a lesbian too.
    I am for hire!

  7. #7
    Maximum Bitrate wi77iam's Avatar
    Join Date
    Jun 2003
    Location
    Chester Springs, PA
    Posts
    605
    This will do it using an Access db object. It should add 1000 records in a couple minutes. Use a similar method to find and modify an existing record.
    Search help on recordset and edit for examples.

    Set dbAccess = OpenDatabase(access_db)
    strsql = "SELECT * FROM PLAYLIST"
    Set o_RSValues = dbAccess.OpenRecordset(strsql)
    Do
    o_RSValues.AddNew
    o_RSValues("Artist") = artist
    o_RSValues("Album") = album
    '...etc.
    o_RSValues.Update
    o_RSValues.Close
    Set o_RSValues = Nothing
    Loop 'through filelist or array reading artist, album, etc.
    *******************************
    *******************************

  8. #8
    I'm sorry, and you are....? frodobaggins's Avatar
    Join Date
    Jan 2003
    Location
    Ruston, LA
    Posts
    8,846
    Here is a little project I made to show the differences between
    using rs.addnew...rs.update method and the rs.open strsql method.


    The difference in speed is phenomenal.


    FrodoBaggins


    Note: I made this using Microsoft ActiveX Data Objects 2.0, so everyone should be able to run it.
    Attached Files Attached Files
    [H]4 Life
    My next generation Front End is right on schedule.
    It will be done sometime in the next generation.
    I'm a lesbian too.
    I am for hire!

  9. #9
    I'm sorry, and you are....? frodobaggins's Avatar
    Join Date
    Jan 2003
    Location
    Ruston, LA
    Posts
    8,846
    The above project took:

    30000 Records


    rs.addnew/update method : 3.233 Seconds

    rs.open strsql: 100.088 Seconds


    aproximately 3100% better with rs.addnew/update
    [H]4 Life
    My next generation Front End is right on schedule.
    It will be done sometime in the next generation.
    I'm a lesbian too.
    I am for hire!

  10. #10
    FLAC PatO's Avatar
    Join Date
    Dec 2000
    Location
    Afton MN
    Posts
    1,120
    Perhaps consider modifying the scenario. How often do you have to do this? Once, when the system in initialized (installed on the machine), and perhaps each time you add a song? The former is unavoidable - since you do it when the machine is being installed, speed may be less of a concern. The later scenario seems like a much more common occurance, and could be quite inconvienent.

    Have you thought about managing the files in your program, or perhaps through business rules? For instance, have the program read all file names on the drive, and select all file names from the db. Then compare... That *may* be faster than blindly updating the database. Even better - place all new songs into new folders (not mixing with existing folders), thereby negating the need to insert thousands of strings into the database. Use a sorting algorithim or "Playlist Manager" to order files in memory instead... (what Winamp's playlist does...)
    http://www.jeepmp3.com/
    CarPC Stolen. Starting over.
    Ne1 recognize the avatar?

Page 1 of 2 12 LastLast

Similar Threads

  1. put all info from mp3 cds in a database...
    By Raas in forum Off Topic
    Replies: 13
    Last Post: 09-23-2006, 10:34 AM
  2. Feature request. Browsing database.
    By Mikesz in forum ME Archive
    Replies: 6
    Last Post: 12-02-2003, 08:18 PM
  3. Datalux question
    By ddt in forum LCD/Display
    Replies: 14
    Last Post: 09-26-2002, 11:02 PM
  4. lcd power question
    By jrbless in forum LCD/Display
    Replies: 1
    Last Post: 04-07-2002, 12:27 PM
  5. a database of id3 info
    By etrik in forum Software & Software Development
    Replies: 16
    Last Post: 01-18-2001, 10:45 AM

Bookmarks

Posting Permissions

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