European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Languages :: Java :: Speeding up my loop
By: VB guy	Date: 28/07/2003 00:00:00	Points: 125	Status: Answered Quality : Excellent
Hello, experts! In my VB app I am looping through recordset to populate my FlexGrid, and it takes forever(performance is not acceptable for the user). I've noticed that it slows down when it hits the following line at the end of the loop: rs.MoveNext The database is Access97 with several tables linked to Oracle database. This is how I declare my recordset: rs.Open sSql, cnn, adOpenStatic, adLockReadOnly Please let me know if you have any suggestions or need more info. Thanks
By: i2mc	Date: 28/07/2003 03:18:00		Type : Comment
You don't give us any idea as to the size of your database? How many records etc would be helpful.

By: VB guy	Date: 28/07/2003 03:26:00		Type : Comment
Sorry about that. The database size is 28 MB. Recordset returns 73 records and it connects and returns records within several seconds. It's when it gets to the movenext line is where it starts taking time. Thanks

By: VGR	Date: 28/07/2003 03:49:00		Type : Answer
1) are you using a table with an INDEX ? 2) does your DB support cacheing and pre-fetch ? 3) are you using a LIMIT clause to speed up the process ? 4) does your query contain an explicit (and lengthy :D ) JOIN ? 5) given you return 73 records only, do you need to have the whole 28 MB online, or could you split them in "most current data useful for searching", "less current data separated to speed up the DB but I can search on them", "less less current data etc" ? 6) BTW, LOCKing slows down the queries. For a SELECT, you don't need any explicit LOCKing.

By: Anonymous13	Date: 28/07/2003 03:51:00		Type : Assist
instead of move next try rs.position = 0 to start and use global variables to keep track of what the current position is then when you need to use move next instead use rs.position = variable or rs.position = rs.position +=1 this way it doesn't have to go through the entire database to move it, it goes straight to that part.

By: VB guy	Date: 28/07/2003 04:07:00		Type : Comment
VGR, yes, my tables have indexes. Removing locking didn't make any difference. I'm not sure about cacheing and pre-fetch, and I'm not using a LIMIT clause. Here's my SQL statement: SELECT CUSTPROG_CUSTOMER_INTERCHANGE.DIVISION, CUSTPROG_CUSTOMER_INTERCHANGE.SOURCE_CUSTOMER_ID, CUSTPROG_CUSTOMER_MAP.CUSTOMER_ID AS MemberID, Group.CUSTOMER_MAP_ID AS GroupID, Group.CUSTOMER_MAP_DESCRIPTION AS [Group], CUSTOMER.CUSTOMER_MAP_DESCRIPTION AS Member FROM CUSTPROG_CUSTOMER_MAP_DESCRIPTIONS AS [Group] INNER JOIN ((CUSTPROG_CUSTOMER_MAP INNER JOIN CUSTPROG_CUSTOMER_INTERCHANGE ON CUSTPROG_CUSTOMER_MAP.CUSTOMER_ID = CUSTPROG_CUSTOMER_INTERCHANGE.FM_CUSTOMER_ID) INNER JOIN CUSTPROG_CUSTOMER_MAP_DESCRIPTIONS AS CUSTOMER ON CUSTPROG_CUSTOMER_MAP.CUSTOMER_ID = CUSTOMER.CUSTOMER_MAP_ID) ON Group.CUSTOMER_MAP_ID = CUSTPROG_CUSTOMER_MAP.CUSTOMER_MAP_ID Where CUSTPROG_CUSTOMER_INTERCHANGE.DIVISION='CO' And CUSTOMER.CUSTOMER_MAP_DESCRIPTION Like 'SUP%' Anonymous13, it looks like ADO recordset doesn't support position property. Thanks

By: Anonymous13	Date: 28/07/2003 04:18:00		Type : Comment
Me.BindingContext(dataset, "table").Position = try that you can't use recordset sorry forgot about that

By: Anonymous13	Date: 28/07/2003 04:20:00		Type : Comment
if you aren't using a dataset already you can make one easily enough but you will also need a dayta adapter for it you should be able to set that up and use your sql statement from above as the one for it.

By: VB guy	Date: 28/07/2003 04:23:00		Type : Comment
What's 'Me' in Me.BindingContext(dataset, "table").Position ?

By: Anonymous13	Date: 28/07/2003 04:25:00		Type : Comment
It just means it takes it from the current form basically

By: sstth	Date: 28/07/2003 04:31:00		Type : Comment
Sorry, Anonymous13, but binding context is not in the list of my form's properties either and I can't find any info on it in the help file.

By: Anonymous13	Date: 28/07/2003 04:38:00		Type : Comment
did you type it as i gave it to you only changing dataset to the name of the dataset and table to the table name? also do you have any import statements at the very beginning of your code?

By: Anonymous13	Date: 28/07/2003 17:12:00		Type : Comment
I need to know these things it makes a big difference it might help if you post some of your code.

By: VGR	Date: 28/07/2003 17:25:00		Type : Comment
too many JOINs bad query try to separate using a TEMPORARY table, it'll be faster try also to optimize the order in which you reference the tables , this can have "dramatic" impact on JOIN speed

By: Anonymous13	Date: 28/07/2003 17:28:00		Type : Comment
We can help you much better if you reply to our comments

By: Anonymous13	Date: 28/07/2003 18:19:00		Type : Comment
If we have answered your question then accept an answer so that this question can be closed.

By: grg99	Date: 28/07/2003 18:29:00		Type : Comment
Looks like a mighty complex request! Any chance you could factor out or pre-compute any of that? For example, if you could first extract all the customers of type "CO" to another table, or make your first "where" be the most likely condition. It would also help to instrument your code to print out exact run-times, so you can see when you're making headway.

By: VB guy	Date: 29/07/2003 22:43:00		Type : Comment
Sorry I didn't reply yesterday. I had to leave work earlier than usual. I actually was able to improve performance by changing Like operator to the Left function: sSql = sSql + "And Left(CUSTOMER.CUSTOMER_MAP_DESCRIPTION," & Len(frmNewPayment.txtCustomer.Text) & ")=" & "'" & UCase(Left(frmNewPayment.txtCustomer.Text, Len(frmNewPayment.txtCustomer.Text))) & "'" Thank you everyone for your efforts to help

By: Anonymous13	Date: 29/07/2003 22:55:00		Type : Comment
so close the question and either split your points or accept an answer

By: jeremypettit	Date: 30/07/2003 01:59:00		Type : Assist
It shouldn't take seconds to return 73 records on a SELECT query. I'd start with the query and use temp tables like VGR stated. Also, using MS Access and linked tables is going to slow it down too. Thing I would do to get rid of some overhead is not even use a recordset, just use a connection object and store the data into a variant array. Can be done like this.... Dim vArray() as Variant vArray = CN.Execute(strSQL).GetRows Dim i as Integer For i = 0 to Ubound(vArray,2) ' Reference array rows and columns like so ' First index is column/field, and second is the row number ' vArray(2,i) Next i This can speed up you apps a bit, recordsets are huge resource hogs, but time must be trimmed off of the data retrieval.

By: DeepankarJoshi	Date: 31/07/2003 22:38:00		Type : Assist
Before opening your recordset make the CursorLocation to adUseClient. rs.CursorLocation = adUseClient rs.Open sSql, cnn, adOpenStatic, adLockReadOnly By Default it will open a Server Side Curser and in each movenext statement it will fetch data from server.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 169.029000 milliseconds

Why Google AdSense ads ?

New Improved Search!