The system running slow or appearing to hang can be a number of things...
You are in a loop, not allowing other events to be processed.
SQL doesn't use database indexes (or none exist).
Lots of text comparisons in SQL Query
Printable View
The system running slow or appearing to hang can be a number of things...
You are in a loop, not allowing other events to be processed.
SQL doesn't use database indexes (or none exist).
Lots of text comparisons in SQL Query
randem:
i was set all the field to indexes.but the system run after 2-3 time it will getting slow until hang.
Only set indexes on the key fields you will be using...
Using text fields will slow things down (usually noticably - as you will see later in this post), and use more memory/increased file size than the equivalent numeric data type.Quote:
you mean using text fields for numbers will cause the system run very slow?so that the system keep hang? i has change the number to number field,but it seem not much improve.
I notice that in several places you still have "Dim rs As New Recordset", which will be eating away at the memory too (you also do not always set them to Nothing afterwards, such as in CheckValueIsExists)
You also have ".CursorLocation = adUseClient", which is specifically asking for your program to take up more memory - as all of the data returned by queries is copied to your programs memory. Is there a reason for it to be there?
As to your SQL with errors, one major point is the lack of quotes & underscores to separate the lines, you also have several brackets you dont need (like around table/field names), and now that your fields are numeric you should remove the single quotes from around the UserId values.
This is how it should have been:
There is something very bad about this query tho - you are joining based on text (DepartmentName). This is something that should always be avoided if possible, as it is extremely slow.VB Code:
By adding an extra column to the Inventory table for DepartmentID (which I called LocationID), and setting it to the relevant values by using an Update statement, the query becomes shorter as below:..and the time for this to run 'fully' inside Access dropped from over 40 seconds (for the previous version) to about 10 seconds.VB Code:
I haven't looked properly at your code yet, as the memory issues were present in Access too.. most likely due to the sheer size of the data.
There are ways to reduce the data size significantly.. the first is the biggest, which is to add extra conditions to your Where clause to only return a portion of the data instead of all of it (eg: "and Deletedate is null AND [Item] = 'Block T1'").
You can also reduce the database size quite a bit with a little re-design (in the mdb file at least, may or may not affect the data returned by the query). The main way is to store ID numbers in the table instead of the text (so if the Department table contained all "Location" values, you could remove Location and just use the LocationID field). You could also create extra tables - with Status for example (as it has only 3 values). This would also help with data consistency, as people cannot mis-type values by mistake (they would simply select it from a list).
..I have no idea if doing this would work for you tho, as DataGrids apparently have big problems using joined tables.
si_the_geek:
i will try your suggestion....Thanks...will let you know the request:)
Private m_rs As ADODB.Recordset ' Recordset tool
Set m_rs = OpenRecordset("select * from Inventory " & _
"where (Location=(select DepartmentName " & _
"from Department " & _
"where Id =(select DepartmentID from UserDepartment " & _
"where [UserId] = " & gUserId & " " & _
" )) " & _
"or exists(SELECT * FROM UserRoles WHERE [UserId] = " & gUserId & " " _
"and [RoleId] in (select [Id] from [Roles] where [RoleName] = 'admin')))" & _
"and Deletedate is null", adOpenStatic, adLockOptimistic)
Public Function OpenRecordset(ByVal strsql As String, _
Optional ByVal CursorType As CursorTypeEnum = CursorTypeEnum.adOpenForwardOnly, _
Optional ByVal LockType As LockTypeEnum = LockTypeEnum.adLockReadOnly) As Recordset
You we're able to assign a DAO recordset to an ADO recordset???
IIRC there is no DAO, only ADO is used.
The name Recordset is used for objects in each library, but like many coders no library is specified in the declaration - so errors are likely if any other library (not just DAO) is added to the project. :(
I'd redesign the inventory interface, your just showing the same information in the textboxes and the list... also if you can reduce the number of columns and rows (don't return entire table) used by a recordset, such as using a tab control for dates (one tab per month in a year), etc then you can speed up retrieval since less data is returned.
Use INSERT, UPDATE, and DELETE SQL statements instead of the recordset methods... your loading entire table in order to add one record.... your loading entire table in order to update one record... your loading entire table to delete one record.
leinad31:
can you upload the redesign inventory project here??let see how you work with.thanks!
There are many ways to skin a cat... it depends mostly on what the important fields are (eg. what is usually used as criteria when a user searches for a record/records). Here's a sample (I focued on generating the output, did design shortcuts such as select *) that uses the treeview control to breakdown the dates in the database into years-months. I wasn't able to use your db (says its corrupted), just update the connection string and table, field names acordingly to fit your project.
There are samples in the forum on using insert, update and delete, try to do a search. The point I'm trying to make is don't try to work with the entire table... after all the user can view only several of themn at a time, and you can only have one current record at any given time. Best to get rid of the habit early... I saw a project a year ago where the programmer ended up loading the entire database into memory with several recordsets (for tables, joins, reports) that were never closed during program run... you can imagine what happened when we tried to run the app with several years worth of accounting data, took more than an hour for it just to load.
Oh and do the per row information display as one form or container (controls in form, in picturebox, frame). When the user will view/update/delete one record, you now have a reusable container which shows appropriate information based on primary key passed (it queries table returning just one record and updates the controls with values. You cal also pass other arguments to control which buttons (add, update, delete) are show in this form/container like what the message box does (eg. vbYesNo, vbOkCancel).