 |
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

December 14th, 2005, 01:18 PM
|
Friend of Wrox
|
|
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

December 15th, 2005, 09:59 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

December 16th, 2005, 03:15 PM
|
Authorized User
|
|
Join Date: Dec 2004
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

December 16th, 2005, 03:32 PM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

December 16th, 2005, 04:08 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|

December 19th, 2005, 11:34 AM
|
Friend of Wrox
|
|
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

December 20th, 2005, 08:26 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

December 20th, 2005, 10:54 AM
|
Friend of Wrox
|
|
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
 |