Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Mar 2008
    Posts
    14

    Question Unanswered: Server optimization

    Hi All,
    Were I work we have a standalone system that writes information to an event log. Currently this event log is in .mdb (MS Access) format. The problem we have is that the .mdb seems to get very slow to access after 100,000 rows or so, so it needs to be cleared out regularly. We have long discussed using an SQL server to log the events to instead of an .mdb file.

    I have written a VB program to test the two DB formats and i expected MS SQL server 2005 to be faster at reading/writing than the .mdb. Both the server and the .mdb are local to the system (it's a standalone system), so we know it's not network that is making the SQL server slower. So here is my question: does anyone know of any good tips/tricks in the server configuration options to speed it up/generally improve performance?

    The table definitions are the same in both SQL server and the .mdb file:
    Table:event_log_0000_000000
    Module - Text
    Event_date - Text
    Event_Time - Text
    Event - Text
    Record_Number - int, primary key
    I know it would probably be better to have Event_date and Event_Time as datetime types, but I’m not in charge of that decision. The data/table doesn't matter to much i just need to prove that the SQL server is better (and faster) than a .mdb file.

    The VB program uses DAO to access the .mdb DB and ADODB to access the SQL server - this is the only difference to how the DB's are accessed and I don't think it would account for the slowness of the SQL server.

    This is my first post here, so I’ve probably missed out some vital information, so please ask.

    Also sorry if this is the wrong place to post this question, it sort of covers Access/SQL Server 2005/Database programming areas, so wasn't sure.

    Thanks

  2. #2
    Join Date
    Sep 2005
    Posts
    161

    Server optimization

    SQL Server isn't necessarily faster than Access when inserting single records into a small database from one connection. When you reach 100,000 records, or even millions of records, you can count on SQL Server to keep performing without slowdowns. Run your comparison with 100,000 records to prove your point.

    Other than that, it will be hard for people to help you without the source code used to insert the records. There could be any number of issues. Does the code reconnect to the database during before each insert? There is more overhead for a SQL Server connection. Are you using a prepared SQL statement? etc.

    With that said, make sure you have a clustered index on your event log table (probably on the PK). Don't use other indexes unless you need them.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Tell the designer from me: "Good luck querying a date range with that half-assed design."

    As to the problem at hand, how does it seem "slow" On inserts? on record retrieval? I am willing to bet that if you queried the table like this:
    Code:
    select * 
    from table 
    where record_number = 555
    it would be blindingly fast. If you query like
    Code:
    select *
    from table
    where module = 'something'
    It will be agonizingly slow. That's the difference between a table scan, and an index seek.

  4. #4
    Join Date
    Mar 2008
    Posts
    14
    It's not the read speed that is the issue here, as the log is only read at the end of day for a print out/report. It's writing data to the log without blocking the main program thread that is the main issue here(i.e. once we've finsihed processsing a transaction we want to write the details of that to a log, but don't want the system to block whilst writing to the DB in case another user starts using the machine).

    I have changed the code slightly since my first post, so now both are using ADODB to connect to the Databases.

    The code i'm using to write (and time) to the 2 Databases is as follows:
    For the access DB:

    Set MDBDB = New ADODB.Connection
    MDBDB.ConnectionString = strConnMDBDB
    MDBDB.Open
    Set accessRst = New ADODB.Recordset
    With accessRst
    Timer1.Enabled = True
    .Open "SELECT [Event_Date], [Record_Number], [Event_Time], [Event], [Module] FROM event_log_0000_000000;", MDBDB, adOpenForwardOnly, adLockPessimistic
    Do While (j < x)
    DoEvents
    .AddNew
    .Fields("Event_Date").Value = (Date + j)
    .Fields("Record_Number").Value = j
    .Fields("Event_Time").Value = (Time() + j)
    .Fields("Event").Value = "some event"
    .Fields("Module").Value = "test Data!"
    j = j + 1
    Loop
    .UpdateBatch
    .Close
    Timer1.Enabled = False
    End With
    MDBDB.Close


    and for the SQL server:

    Set SQLDB = New ADODB.Connection
    SQLDB.ConnectionString = strConnSQLDB
    SQLDB.Open
    Set rst = New ADODB.Recordset
    With rst
    Timer1.Enabled = True
    .Open "SELECT [Event_Date], [Record_Number], [Event_Time], [Event], [Module] FROM event_log_0000_000000;", SQLDB, adOpenForwardOnly, adLockPessimistic
    Do While (j < x)
    DoEvents
    .AddNew
    .Fields("Event_Date").Value = (Date + j)
    .Fields("Record_Number").Value = j
    .Fields("Event_Time").Value = (Time() + j)
    .Fields("Event").Value ="some event"
    .Fields("Module").Value = "test Data!"
    j = j + 1
    Loop
    .UpdateBatch
    .Close
    Timer1.Enabled = False
    End With
    SQLDB.Close


    I get the impression the best way to get good performance out of this is to have a solid database/table definition underneath it all, but i don't see how that can speed up writing to the db, as there is no searching (except maybe to check that the PK doesn't already exist) for records.

    Another option someone else has looked at was multithreading the main program so there is a dedicated thread that does the writing to the DB, but it was decidedly over complicated to pass data between the 2 threads.

    Would the above be better done if i wrote some code to prepare an SQL INSERT statement instead of using the Recordset.AddNew method?

    Reading data, as I said isn't as much of an issue as the machine would be taken out of service to print off the report at the end of the day/week.

    SQL server is all a bit new to me, so sorry in advance if there are any obvious mistakes or dumb comments in the above. Please ask if there is more information i can provide.

  5. #5
    Join Date
    Mar 2008
    Posts
    14
    Further to my previous post, what i am basically getting at is SQL Server is maybe a bit overkill for this, so are benefits of doing things like restricting access to only one user only at a time (as there will only ever be 1 database user, the application itself) worth it? I'm unsure of what all the options provide and what options can be turned off or turned down without removing the basic functionality that is needed. More server configuration than database design if you see what i mean.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I would not open a cursor to the whole table, but just execute the INSERT statements without the whole recordset getting in the way.

  7. #7
    Join Date
    Sep 2005
    Posts
    161
    I think MCrowley hit the nail on the head. This is a performance problem

    PHP Code:
     .Open "SELECT [Event_Date], [Record_Number], [Event_Time], [Event], [Module] FROM event_log_0000_000000;"SQLDBadOpenForwardOnly
    You are selecting every record in the table before doing an insert. The recordset will not retrieve the entire table because you're using a forward only cursor. But MSSQL will create a temporary table containing the results of the query, which is the entire table. I don't know how MSACCESS handles a cursor, but it can't be efficient.

  8. #8
    Join Date
    Mar 2008
    Posts
    14
    Thankyou for your suggestions.

    I've changed the writing to the SQL database to use a stored procedure that just does an insert statement, instead of that nasty select everything statement. So now I'm using this line in the writing loop and I've done away with the recordset object as i can just execute a SQL command via the execute method of the connection object.

    Code:
    Call SQLDB.Execute("EXEC bassapinfo.dbo.add_event '" & j & "', 'test Module', '" & (Date + j) & "', '" & (Time() + j) & "', 'someString'", , adAsyncExecute)
    adAsyncExecute means it shouldn't block the main thread, it still takes roughly 10 x longer to write the records than the same to the Access database, with the original SQL Select statement as before. I'm guessing the Server is slower at writing because of the overheads of being a server like recording data for usage stats etc...

    Is there a way to turn off features like this so it just provides the same functionality as the .mdb file, literally a database to read and write to, without the other (potentially useful in another application) features that will remain unused here?

    I'm interested in how to get performance via actual configuration of the server software instead of re-designing the database definition to make that more efficient.

  9. #9
    Join Date
    Sep 2005
    Posts
    161
    How many inserts per second are you achieving?

  10. #10
    Join Date
    Mar 2008
    Posts
    14
    OK here are my results:
    (times are in ms)
    MS ACCESS (.mdb):
    Average time to write one record (Synchronously):0.0224
    Average time to write one record (Asynchronously): 0.1406

    SQL SERVER 2005
    Average time to write one record (Synchronously):0.0235
    Average time to write one record (Asynchronously): 0.1705

    Now i say the times are in ms, but that's more down to the VB timer being correct, either way they are all timed in the same way. I'm not sure why the Asynchronous writing is soo much slower, as I thought it wouldn't be. This again may be down to VB and it's implementation of multithreading.

    Both databases were shrunk/compacted before testing and both tables had 0 rows in.
    test done by writing 110,000 rows to each database, 0-100,000 in 5,000 row steps and the last 10,000 in 1,000 row steps. This was to see if the database slowed down all that much after 100,000 rows.
    Tests were repeated 3 times each.


    And here's the code i used to get the numbers:

    For MS ACCESS:

    Code:
    Private Sub Command4_Click()
    'Declare some variables and grab some info from the form.
    Call EnableButtons(theMDBDB, False)
    Dim j, x As Long
    j = Form1.lblMDBRows.Caption
    x = j + CLng(Form1.txtMDBrows.Text)
    MDBWriteTot = MDBWriteTot + CLng(Form1.txtMDBrows.Text)
    Set MDBDB = New ADODB.Connection
    MDBDB.ConnectionString = strConnMDBDB
    MDBDB.Open
    Ms = 0
    
    If (Form1.chkAsyncMDB.Value) Then 'Async Writes
      Dim arMDBCmd() As ADODB.Command
      ReDim arMDBCmd(x - j)
      Timer1.Enabled = True
      Do While (j < x)
        DoEvents
        Set arMDBCmd(x - j) = New ADODB.Command
        arMDBCmd(x - j).CommandText = "INSERT INTO event_log_0000_000000 (Event_Date, Event_Time, [Event], [Module]) VALUES('" & (Date + j) & "', '" & (Time() + j) & "', 'Some Data" & j & "', 'test Module')"
        arMDBCmd(x - j).CommandType = adCmdText
        arMDBCmd(x - j).ActiveConnection = MDBDB
        arMDBCmd(x - j).Execute , , adAsyncExecute + adExecuteNoRecords
        j = j + 1
       Loop
      Timer1.Enabled = False
    Else ' non Async writes
      Dim MDBCmd As ADODB.Command
      Set MDBCmd = New ADODB.Command
      MDBCmd.ActiveConnection = MDBDB
      MDBCmd.CommandType = adCmdText
      Timer1.Enabled = True
      Do While (j < x)
        DoEvents
        MDBCmd.CommandText = "INSERT INTO event_log_0000_000000 (Event_Date, Event_Time, [Event], [Module]) VALUES('" & (Date + j) & "', '" & (Time() + j) & "', 'Some Data" & j & "', 'test Module')"
        MDBCmd.Execute , , adExecuteNoRecords
        j = j + 1
      Loop
      Timer1.Enabled = False
      MDBDB.Close
    End If
    
    'update the form
    Form1.lblMDBWrite.Caption = Ms
    MDBWriteTime = MDBWriteTime + Ms
    MDBWrite = MDBWrite + 1
    Dim temp As Currency
    temp = (MDBWriteTime / MDBWriteTot)
    Form1.lblMDBWriteAv.Caption = temp
    Form1.lblMDBWriteTot.Caption = MDBWriteTot
    Call EnableButtons(theMDBDB, True)
    rowCount (theMDBDB)
    End Sub
    And for the SQL SERVER

    Code:
    Private Sub Command3_Click()
    'Declare some variables and grab some info from the form.
    Call EnableButtons(theSQLDB, False)
    Dim j, x As Long
    j = Form1.lblSQLRows.Caption
    x = j + CLng(Form1.txtSQLrows.Text)
    SQLWriteTot = SQLWriteTot + CLng(Form1.txtSQLrows.Text)
    
    
    Set SQLDB = New ADODB.Connection
    SQLDB.ConnectionString = strConnSQLDB
    SQLDB.Open
    
    Ms = 0
    If (Form1.chkAsyncSQL.Value) Then 'async writes
      Dim arSQLCmd() As ADODB.Command
      ReDim arSQLCmd(x - j)
      Timer1.Enabled = True
      Do While (j < x)
        DoEvents
        Set arSQLCmd(x - j) = New ADODB.Command
        arSQLCmd(x - j).CommandText = "INSERT INTO event_log_0000_000000 (Event_Date, Event_Time, [Event], [Module], Record_Number) VALUES('" & (Date + j) & "', '" & (Time() + j) & "', 'Some Data" & j & "', 'test Module', " & j & ")"
        arSQLCmd(x - j).CommandType = adCmdText
        arSQLCmd(x - j).ActiveConnection = SQLDB
        arSQLCmd(x - j).Execute , , adAsyncExecute + adExecuteNoRecords
        j = j + 1
        Loop
        Timer1.Enabled = False
    Else ' non async writes
      Dim SQLCmd As ADODB.Command
      Set SQLCmd = New ADODB.Command
      SQLCmd.ActiveConnection = SQLDB
      SQLCmd.CommandType = adCmdText
      Timer1.Enabled = True
      Do While (j < x)
        DoEvents
        SQLCmd.CommandText = "INSERT INTO event_log_0000_000000 (Event_Date, Event_Time, [Event], [Module], Record_Number) VALUES('" & (Date + j) & "', '" & (Time() + j) & "', 'Some Data" & j & "', 'test Module', " & j & ")"
        SQLCmd.Execute , , adExecuteNoRecords
        j = j + 1
      Loop
      Timer1.Enabled = False
    SQLDB.Close
    End If
    
    'write the results to the form.
    Form1.lblSQLWrite.Caption = Ms
    SQLWriteTime = SQLWriteTime + Ms
    SQLWrite = SQLWrite + 1
    Dim temp As Currency
    temp = (SQLWriteTime / SQLWriteTot)
    Form1.lblSQLWriteAv.Caption = temp
    Form1.lblSQLwriteTot.Caption = SQLWriteTot
    Call EnableButtons(theSQLDB, True)
    rowCount (theSQLDB)
    
    End Sub
    Last edited by jquartly; 03-07-08 at 06:38.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    AHA!!!!!!

    Never shrink the SQL database. That is a requirement of Access, not SQL Server. When the SQL database needs to grow again, it needs to "format" the new area. SQL 2005 does a quicker job of that than SQL 2000, but it still takes time, and wastes disk I/O.


    EDIT: I still do not see where you are getting a difference of 10 x, though.

  12. #12
    Join Date
    Mar 2008
    Posts
    14
    Interesting, gives me something else to think about.

    I'm going to re-write the tester app in C++, as i feel that will have better timing and give me more options for fine tuning . Also going to reimplement it as one INSERT statement with a UNION ALL on a SELECT to do multiple inserts instead of looping round and round with a new command object each time.

    I'll post the new results when i get them.

    This thread would probably have been better put in a dev thread instead of the server one, as i seem to be getting more into changing my code than anything to do with the server, so apologies for that.

  13. #13
    Join Date
    Mar 2008
    Posts
    14
    After re-writing in VC++ it still appears access to be quicker to write to than SQL Server 2005. Incidently DAO is faster than ADO as well when it comes to writing to access, but ADO makes it easier to change the backend database, without altering the application code too much.

    Thanks to all who had input and help for me.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jquartly
    Incidently DAO is faster than ADO as well when it comes to writing to access, but ADO makes it easier to change the backend database, without altering the application code too much.
    I know this just isn't any help at all but DAO is optimised for JET. MS tried to put DAO out to pasture years ago but so many developers still used it and so there was outcry. DAO > ADO when talking to JET. ADO > DAO in just about every other circumstance. I'm not sure about the "ADO makes it easier to change the backend database" bit. Submit JET DDL and both should be fine.

    Could you post your table DDL please for SQL Server? (your definition in the first post is not complete - just script out the table). Also, can you confirm that the SQL Server database is not growing at any time during your inserts now?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Mar 2008
    Posts
    14
    Quote Originally Posted by pootle flump
    I'm not sure about the "ADO makes it easier to change the backend database" bit.
    The way the (live) application code is written at the moment i'd have to make quite a few changes to the way it works to use something other than .mdb, but once i'd put in the ADO code, it would just be a matter of changing the connection/provider string if we wanted to use a different back end (non Jet).

    Quote Originally Posted by pootle flump
    Could you post your table DDL please for SQL Server? (your definition in the first post is not complete - just script out the table).
    Not sure exactly what you are after from me here, have you got an example of what you are expecting or some instructions on how to get the info from SQL SERVER 2005?

    Quote Originally Posted by pootle flump
    Also, can you confirm that the SQL Server database is not growing at any time during your inserts now?
    I'm not sure how to do this either! How would this behavior be stopped in the live system as eventually the db would need to grow on that or is it just making sure its not growing at the same time as the inserts (i.e can it be setup to grow after an insert?)

    Does it show that i don't know much about SQL SERVER setup yet?

Posting Permissions

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