why time delay?

[shivkumar]

I am fetching data from sql server. I am using windows2000 professional
and the database is on the server system running server2003

there are lacks of records in the table, I am adding a particular field in the list box but it is taking too much time. Can we reduce the proccessing time by any mean

i am using the following lines
_________________________

cn.ConnectionString = "Provider=SQLOLEDB;Persist Security
Info=False;User ID=admin1234;Password=sql-server;Initial Catalog=db_cash; Data Source=sql-server"
cn.open

Dim ssql As String
ssql = "Select * from cb_payments_detail order by voucher_no"
Set rs = New ADODB.Recordset
rs.Open ssql, cn, adOpenDynamic, adLockPessimistic
rs.MoveFirst
rs.MoveFirst
Do Until j = 100000
List1.AddItem (rs!voucher_no)
rs.MoveNext
j = j + 1
Loop
rs.Close
__________________________________

regards
shiv

[Shuja Ali]

Why would you retrieve all the fields if you are going to use just 1 field from the Query. I would optimize the query to rertrieve only one field. I would even make this field indexed in the database. That way I don't have to use Order BY in my Select Query. So your code would look like this

Dim ssql As String
ssql = "Select voucher_no from cb_payments_detail order by voucher_no"
Set rs = New ADODB.Recordset
'if i am only going to read the data, i wouldn't open it as Dynamic. I would open it as ForwardOnly
rs.Open ssql, cn, adOPenForwardOnly, adLockPessimistic
While Not rs.EOF
List1.AddItem (rs!voucher_no)
rs.MoveNext
Wend
rs.Close

And Shic, you need to use Code Tags while posting the code. It makes reading through the post easier and people can easily understand the problem/question.

Take a look http://www.codeguru.com/forum/misc.php?do=bbcode#code

[shivkumar]

thanks a lot shuja
again "tusi great ho"



shiv

[JonnyPoet]

Hi Shivkumar
As told please Use CodeTags its easy to be done. Look on the end of my Post In the signature I hve done an example how this works. This CodeTags are necessary because otherwise all spaces and Tabs are lost during Web Presentation. Then your code looks like a sausage difficult to read. So additional also Necessary to use Spaces in your code so its easy to see loops and if then elses
Your code looks then

Dim ssql As String
ssql = "Select voucher_no from cb_payments_detail order by voucher_no"
Set rs = New ADODB.Recordset
'if i am only going to read the data, i wouldn't open it as Dynamic. I would open it as ForwardOnly
rs.Open ssql, cn, adOPenForwardOnly, adLockPessimistic
While Not rs.EOF
	 List1.AddItem (rs!voucher_no)
	 rs.MoveNext
Wend
rs.Close

Or an if statement would look like the following PseudoCode

if Forum =This and PeopleUsesTags then
Txt = "Programmers are Happy"
ProcessVoice Txt
else
	ProzessVoice Explode 
end if

Got me ?

[George1111]

TIP

I find that if you are populating a Grid or a Listbox, the performance is much, much faster by making the Grid or Listbox invisible prior to commencing the populating - then make it visible when you have finished.

Using your example -

Dim ssql As String
ssql = "Select voucher_no from cb_payments_detail order by voucher_no"
Set rs = New ADODB.Recordset
'if i am only going to read the data, i wouldn't open it as Dynamic. I would open it as ForwardOnly
rs.Open ssql, cn, adOPenForwardOnly, adLockPessimistic
List1.visible = False
While Not rs.EOF
List1.AddItem (rs!voucher_no)
rs.MoveNext
Wend
rs.Close
List1.Visible = True

Obviouly if you have 50 records coming into the Listbox, you may not notice a difference.

Try it with a good hearty list of items - 5000 to 10000 items

It is particularly noticable with MsFlexgrid !

[Pinky98]

You would be better off not using the VB loop. These are inherently slow. Better would be to use a data bound control, which are much faster.

But if you are displaying that many options to the user, then I would say there is probably something you should be addressing in your GUI.

[George1111]

Where it gets really interesting is having a Flexgrid showing a search result from a stock file, for example, and you want to highlight a column on the grid in a special colour - eg, a quantity to order column

So first you hide the grid (Grid.Visible = False)
Then you populate the grid with an sql statement using data binding
Then you have to run through the grid making the Order Qty column Yellow, for example
Then you make the Grid visible again (Grid.Visible = True)


The resulting Grid may be 1 item to thousands, depending on the user's search criteria, and the performance is enhaced incredibly by hiding the grid

You don't actually see the grid disappear, which is neat - it just seems to refresh based on the new search criteria