How to improve performance?

[gazal]

Hi Friends

I am using VB 6.0 SP6, MSDE 2000, I have a table to store subscriber's details, right now the table has only 10,000 records and the application is running very slow while fetching, saving and unloading the records. So i created a unique Index, which upto certain extend improved the speed, but the record count will be increasing day by day, in a span of 1 month it can go upto 20-25 thousand records also, that time it will slow down again, so what all methods and measures should i take to make sure that even there are lacks of records the system shouldnt go slow..

Here is my SQL Statement.
gSql = " Select SubId, SubCode, SubName, SubAdd1," _
& " SubTel1, SubTel2, SubCellNo, SubFaxNo, SubEmail," _
& " SubType, SubOrigin, SubTerritory, SubState, SubCity," _
& " SubPinCode, SubTitle, SubGender, SubAge, BulkSubName from SubscriberMaster order by SubCode asc"

Set gRs = New ADODB.Recordset

gRs.Open gSql, cn, adOpenDynamic, adLockOptimistic, adCmdText

If Not gRs.EOF Then
gRs.MoveLast
FillRecs
Else
'Clear Controls
CtlEnaDis Me, "BT"
CtlEnaDis Me, "BCHK"
CtlEnaDis Me, "BOPTB"
End If

Please guide me with ur valuable opinions...

Regards
Gazal

 

[fredericofonseca]

Is SubCode an index?

And what is FillRecs doing (full code please), and why do you position yourself on the last record of the recordset before loading the records?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[VBrit]

I think you are trying to load an entire table into an ADODB recordset object from your code.

What you should do is a bit of caching. Load say 1000 records, then fetch 1000 more when you need to. Also if you are populating say a grid control, it will have the records loaded once in ADO rs, and then again on the screen in the control, this will severely affect performance for that many rows.

Also, you can put your SQL in a stored procedure returning a resultset, where you send in the start point and the number of records to return.

This will make your code run MUCH faster....


Hope this helps

 

[mmilan]

If you're changing inserting so many records, be sure to use UPDATE STATISTICS on the server, as well as using a FILL FACTOR (a low one!) in your indexes, and rebuild frequently...

mmilan

 

[petermeachem]

It's not that many inserts actually. About 1000 a day, or 2 a minute.
Hope he comes back and tells us what he is doing.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[gazal]

Thanks a lot guys for ur responses

Ok Its a simple data entry form with few text boxes, option buttons and combo boxes (No Grids at all)...

And Frederico Fonseca Yes SubCode is the Indexed Column.

Vbrit can u give me some example of how to do it with Stored procedure??? that will be great and how do i selecte X number of records....

And mmilan i didnt get u properly....


Please get back to me with more ideas....

Gazal

 

[petermeachem]

If you are purely adding a record, use something like this

Dim csql As String
csql = "INSERT INTO ChangeLogOrderEntryTable (Computer, RecordChangedBy , RecordChangedAt , OrderEntryId , Fieldname , OldValue , NewValue ) "
csql = csql & " VALUES ( "
csql = csql & "'" & Computer & "' ,"
csql = csql & "'" & RecordChangedBy & "' , '" & Format(RecordChangedAt, "dd mmm yyyy hh:nn") & "' , "
csql = csql & OrderEntryId & " , '" & Fieldname & "' , '" & OldValue & "' , '" & NewValue & "')"

db.Execute csql

You don't need a recordset at all. Look up INSERT INTO in help.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[gazal]

hi peter

its not only the question of inserting records, i need to perform all the basic database operations like Displaying records, Inserting, Editing, Deleting, refreshing etc...

Some help with SP method....

Gazal

 

[mmilan]

UPDATE STATISTICS
=================

SQL Server builds certain statistics based on samples of the data in your database, and then uses what it learns as it decides how to physically conduct your queries (ie. in the Query Optimiser). By updating statistics, you ensure that this information is up to date, and allows the optimiser to take better decisions.

FILL FACTOR.

When you insert records, you write not only to the table itself but also to "index pages" as well. The index page is a group of index records. Let's say you have eight index records per page, and a fill factor of 100% - which is I think the default for fill factor. What happens is that on building your index, each index page will be assigned eight index records - pretty much as you expect...

But what happens if you insert some data into your table which ought to result in the index record being written to a particular index page near the start? Well, they're all full, so the index record gets written to a new index page - which in terms of position in the file is right down and the end! This is called index fragmentation, and has a nasty effect on speed.

If you use a fill factor of say 50% when creating your index, each of the index pages in the above scenario would only get four index records - leaving four spaces. As new table records are added, these blank index records can be utilised rather than having to add to the end all the time, which delays index fragmentation, and keeps things fast...

mmilan

 

[TJRTech]

Let me guess, you used to program in Access, right?

The old approach of getting an entire table (full queries) worth of data, displaying it in a form for updating individual rows, then submitting it all back to update is passe.

You need to use some other metaphor for updating records; say a search form that returns a subset based on some input by the user (lastname, date range, id, etc). Or, you need to make your data hierarchical (say grouped by month, week no, region, salesperson, etc - whatever makes sense for your business), and then employ a drill-down model that allows you to drill into the exact, subset of records you want.

TJR

 

[petermeachem]

If you aren't using a grid, you can only display one record. You need to add some sort of search criteria so that the user can display the record they want, and make sure that is indexed.

If you want the last record, this seems to work
select * from Order_entry_table where orderentryid = (select max(orderentryid) from order_entry_table)

orderentryid is an autonumber

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[gazal]

hi

thanks mmilan for a detailed explaination.

So it freezez down to the conclusion, that if i m dealing with huge amount of data then i mus fetch and disply it batchwise, means fetching may be 1000 records at once. But still no help on how to use Stored Procedure to get data on the form...


to be very honest i m not that satisfied with the solutions....

But thanks for ur quick responses....

Hoping for a more concrete solution...

Regards
Gazal

 

[petermeachem]

You are displaying one record right? Then just fetch one record. Actually, 1000's is a small amount of data for sql.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[gazal]

hi

peter u r absolutely right, i m displaying one record at a time, but at the same time i have navigational buttons on my form for navigating between records, thats the main reason a record set is of great use for me, once only i fetch all the records into the recordset and can easily navigate through it, like grs.movenext, grs.moveprevious etc....

Where as if i select one record only then each time i have to call my data population function to get the next/previous record, not a bad way but i will have to almost rewrite my code...

Any way out???

Gazal

 

[petermeachem]

I'm doing that right now. Rewriting an Access programme in VB that in part displays a single record. The Access one did a next/previous in a recordset, the vb one reads one record and displays it and updates it on Save. You can either go the easy/slow route or the harder/quick one.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[strongm]

It may also be worth looking at adAsyncFetch

 

[JeffTullin]

>> i m displaying one record at a time, ... and can easily navigate through it, like grs.movenext, grs.moveprevious etc...
Where as if i select one record only ..i will have to almost rewrite my code...
<<

Almost.
Write a class that has MoveNext, MovePrevious etc functionality, then you call these functions, and pass something into them that tells them 'where you are now'.

The functions do whatever is needed to get the 'right' record for you.
Is it too much to hope for that SubCode is unique?
If it is, your MoveNext will be based on something like
SELECT TOP 1 <stuff> WHERE subcode > <currentsubcode> ORDER BY SubCode;

...or maybe SubCode +1 if that is valid.

 

[gazal]

thanks jeff for ur suggestion

i have got a Identity Column in my table can easily use that to navigate and fetching...

Thanks anyways

Gazal