Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 14th, 2005, 01:18 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default They Want It To Be Faster!

So here's the thing, I finished designing the interface and coding the functionality for this database, and now they want it to run faster. Two tables, one for Vendors (500 entries), one for Part Usage (230,000 entries).

Searches for Vendors are fine due to the small table size. Searches for Usages are sloooow. 3 seconds in the best case (very few records returned), 15 seconds in the worst case (all records returned). Here's the SQL for the Usage query:

sql_q = "SELECT DISTINCT [Part Number], [Alt Part Number], [NHA Part No], [NIIN], [PCAGE], [ACAGE], [DOC CODE], [TOC], [SYSTEM], [CABINET] " & _
            "FROM Usage " & _
            "WHERE [Usage]![Part Number] LIKE '*" & srchstr & "*' OR [Usage]![Alt Part Number] LIKE '*" & srchstr & "*' " & _
            "ORDER BY [Part Number];"

That query is installed in qryPartSearch.

LIKE was used instead of Equals because the DB owner wanted to be able to look up info on partial matches. I suspect this query accounts for the largest chunk of processing time. When the form is displayed, it displays in a subform all the vendors associated with the part (PCAGE, ACAGE), this runs fine and fairly quickly. It also has the info common to the part (everything before [SYSTEM] in the above query) displayed up top. However, there are 9 Systems each with 12-16 Cabinets. Each system has a static form with a checkbox for each Cabinet in it. Each of the 9 subforms is a page in a tab control on the main Usage Listing form. Each time the part record is changed it takes 2-5 seconds to refresh the System tabs. Speeding this up would be a big boost to the end user. Here is the code I'm currently using to populate these listings:

    varPartNumber = [Forms]![frmUsageListing]![Part Number]
    varAltPartNumber = [Forms]![frmUsageListing]![Alt Part Number]
    varNHAPartNo = [Forms]![frmUsageListing]![NHA Part No]
    varNIIN = [Forms]![frmUsageListing]!NIIN
    varPCAGE = [Forms]![frmUsageListing]!PCAGE
    varACAGE = [Forms]![frmUsageListing]!ACAGE
    varDOCCODE = [Forms]![frmUsageListing]![DOC CODE]
    varTOC = [Forms]![frmUsageListing]!TOCstr

    sql_sys = "SELECT DISTINCT SYSTEM, CABINET " & _
                "FROM qryPartSearch " & _
                "WHERE [Part Number] = '" & varPartNumber & "' AND IIf(IsNull([Alt Part Number])," & IsNull(varAltPartNumber) & ",[Alt Part Number] = '" & varAltPartNumber & "') AND IIf(IsNull([NHA Part No])," & IsNull(varNHAPartNo) & ",[NHA Part No] = '" & varNHAPartNo & "') AND IIf(IsNull([NIIN])," & IsNull(varNIIN) & ",[NIIN] = '" & varNIIN & "') AND [PCAGE] = '" & varPCAGE & "' AND IIf(IsNull([ACAGE])," & IsNull(varACAGE) & ",[ACAGE] = '" & varACAGE & "') AND [DOC CODE] = '" & varDOCCODE & "' AND IIf(IsNull([TOC])," & (Len(varTOC) = 0) & "," & (Len(varTOC) <> 0) & ");"

    dbs.QueryDefs.Refresh
    For Each qdf In dbs.QueryDefs
        If qdf.Name = "qrySystemSearch" Then
            dbs.QueryDefs.Delete qdf.Name
        End If
    Next qdf

    Set qdfSys = dbs.CreateQueryDef("qrySystemSearch")
    qdfSys.SQL = sql_sys
    qdfSys.ReturnsRecords = True
    dbs.QueryDefs.Refresh

    Dim rs As DAO.Recordset
    Set rs = dbs.OpenRecordset("qrySystemSearch")
    rs.MoveFirst
    If rs.BOF And rs.EOF Then
       'rs is empty
    Else
        For i = 0 To Me.TabCtl28.Pages.Count - 1
            strSearchSys = "[SYSTEM] = '" & Me.TabCtl28.Pages(i).Name & "'"
            rs.FindFirst (strSearchSys)
            If rs.NoMatch Then
                Me.PartNumber.SetFocus
                Me.TabCtl28.Pages(i).Visible = False
            Else
                Me.TabCtl28.Pages(i).Visible = True
                For j = 0 To Me.TabCtl28.Pages(i).Controls(0).Controls.Count - 1
                    If Me.TabCtl28.Pages(i).Controls(0).Controls(j).Contr olType = acCheckBox Then
                        strSearch = strSearchSys & " AND [CABINET] = '" & Me.TabCtl28.Pages(i).Controls(0).Controls(j).Name & "'"
                        rs.FindFirst (strSearch)
                        If rs.NoMatch Then
                            Me.TabCtl28.Pages(i).Controls(0).Controls(j) = False
                        Else
                            Me.TabCtl28.Pages(i).Controls(0).Controls(j) = True
                        End If
                    End If
                Next
            End If
        Next
    End If

    rs.Close

If you know of any way I could make this run faster given the awful and immutable table structure I'm forced to work with, please let me know.

Reply With Quote
  #2 (permalink)  
Old December 15th, 2005, 09:59 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Is this an Access or SQL back end?

If Access, where is the back end and where is the front end?

Is this striclty OLAP, or are other users doing OLTP at the same time?

If it is SQL, write the queries as Views on the server, and then go to them. SQL Server will optimize the queries for faster and more limited data retrieval.



mmcdonal
Reply With Quote
  #3 (permalink)  
Old December 16th, 2005, 03:15 PM
Authorized User
 
Join Date: Dec 2004
Location: Bakersfield, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Consider using a unique ID number column in the parts table. If an Access table, it is date type autonumber, and it would be the primary key. I know that adds more complication to queries and code, but it can significantly improve db performance.

If the actual part number is the primary key, queries and code are slowed bcz the part numbers are (I'm assuming) alphanumeric, long cumbersome strings from various vendors that don't process as quickly as an autonumber data type.
Reply With Quote
  #4 (permalink)  
Old December 16th, 2005, 03:32 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This makes more sense. I overlooked the lack of a PK. Turn the Part Number column into a clustered index, and then create indexes on any foreign key columns as well.


mmcdonal
Reply With Quote
  #5 (permalink)  
Old December 16th, 2005, 04:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

If you established your foreign key constraints through the Relationships window, Access has already indexed your foreign key fields (behind the scenes), and you wouldn't want to duplicate those indexes. The foreign key indexes don't appear in the Indexes dialog, but you you can see them by running Tools -> Analyse -> Documenter on a table.

But what I really wanted to say is don't limit your thinking about performance to query optimization. The most dramatic performance improvement anyone ever shared with me came from removing things like subdatasheet, autocorrect, lookup field, etc. type functionlity from their Access base tables. Be sure all that crap is disabled in your DB. All of it involves unnecessary behind the scenes query processing.

We've had a running discussion on performance issues on this forum that's up to about 5 pages long now at:

http://p2p.wrox.com/topic.asp?TOPIC_ID=5188

Might be worth a look.

HTH,

Bob

Reply With Quote
  #6 (permalink)  
Old December 19th, 2005, 11:34 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mmc: It is an Access back end. I'm not sure what you mean by where is the front/back end.

jking: I had considered that but been told that I cannot change the table structure. I have tried to explain that adding a field would have no effect on previous apps but the structure is entrenched. I may add it anyway and let the performance increase speak for itself at this point. As of now there is no primary key, part numbers are not even close to unique, and considering there's 97 fields there is no way in hell I'm going hunting through it for a good multi-field PK.

mmc: I tried turning the Part Number into a clustered index but saw no performance improvement. I believe this is due to the requirement that partial part number matches are returned as well (forcing the use of LIKE *partno*).

Bob: There are no foreign key constraints. There is not even a way to establish them without massively altering the, excuse me, blatantly idiotic design of the database. Had they come to me 3 months ago and do this up from scratch it would be a complete non-issue, it would run fast and lean and be far FAR easier for me to code. Instead they stuck me with syntactic hell and horridly slow performance. There's only one foreign key I could add from usage.pcage to vendor.cage and it did not improve performance noticably. I will try implementing your other suggestions and those in that thread though, and will let you know if it helps.

Thanks for the help everyone, I'll be back later today with a migraine and further results.

Reply With Quote
  #7 (permalink)  
Old December 20th, 2005, 08:26 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The back and and front end locations are important. A lot of developers make the mistake of splitting the database, and then putting both the back end and front end on a network drive, and then dropping shortcuts to the front end on each users' desktop, instead of copying the front end file to each users' desktop.

All of your users opening the same copy of the front end on the network, or even one user opening the front end on the network will seriously slow down the app.

So, please make sure the database is split, the back end is on a network drive, and that each user has a copy of the front end on their desktop, or a copy of it on their local machine with a shortcut to the local machine copy on their desktop. This should increase performance dramatically.

HTH


mmcdonal
Reply With Quote
  #8 (permalink)  
Old December 20th, 2005, 10:54 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ah, I understand what you mean now mmc. I'm doing all the development on a copy that's 100% on my local system, so while it's not the cause of my current problems I will remember to split them when it is time to deploy it over what's currently being used.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Suggestion for better / faster processing elygp SQL Server 2000 2 February 13th, 2008 10:47 PM
which is faster pegasus51 Ajax 3 December 30th, 2007 08:56 AM
Which one is much faster in gridview, htmltable anshumannidhi BOOK: Beginning Visual Basic 2005 ISBN: 978-0-7645-7401-6 0 October 11th, 2006 04:50 AM
c++ faster than Pascal IvAnR C++ Programming 5 August 6th, 2005 01:08 AM
Any faster? Ben Access VBA 19 March 12th, 2004 07:04 PM



All times are GMT -4. The time now is 02:27 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.