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.

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

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.

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

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

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.

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

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.