What's faster than a For Loop?

[sico]

I'm sure this question has been asked before but i searched this forum and found nothing so i'll ask it again.

What's faster than a For Loop?

I've heard that you can put a For Loop in a For Loop to speed things up, but i dont know if this is true or how to do it if it is true.

I'm asking because I've got a Form App that searches an Excel file for data and then displays the data to the user and there is currently over 20,000 cells to search.

I originaly tried a timer to search one by one and found out the hard way that timers run at a maximum speed of about 60hz, so i've created a For Loop, but this is running at about 1,000hz, so this takes 20 or so seconds to find the data.

[stanav]

Show us your code and we'll see if it can be optimized further....

[dday9]

When I run a for loop 1 to 10 million:

Dim r As Random = New Random()
        Dim t As Double

        System.Console.WriteLine("For loop 1 to 10,000,000 ")
        Dim sb1 As New stringbuilder
        t = Timer
        For i = 1 To 10000000
            sb1.Append(Format(r.NextDouble * 100.0, "###0.00 "))
        Next
        System.Console.WriteLine("Seconds elapsed: " & (Timer - t).ToString("#######0.00"))
        System.Console.WriteLine("Length of string builder: " & sb1.Length.ToString())
        System.Console.WriteLine()

Seconds elapsed = 11.66
Length of the string builder = 59001184

When I reverse it(10 mil to 1):

Dim r As Random = New Random()
        Dim t As Double

        System.Console.WriteLine("For loop 10,000,000 to 1 ")
        Dim sb1 As New stringbuilder
        t = Timer
        For i = 10000000 To 1 Step -1
            sb1.Append(Format(r.NextDouble * 100.0, "###0.00 "))
        Next
        System.Console.WriteLine("Seconds elapsed: " & (Timer - t).ToString("#######0.00"))
        System.Console.WriteLine("Length of string builder: " & sb1.Length.ToString())
        System.Console.WriteLine()

        System.Console.WriteLine("Press any key to end the program ")
        System.Console.ReadKey()

I get:
Seconds elapsed = 11.72
Length of the string builder = 59002380

When I try a while statement:

Dim i As Integer = 1
        Dim r As Random = New Random()
        Dim t As Double

        System.Console.WriteLine("For loop 10,000,000 to 1 ")
        Dim sb1 As New stringbuilder
        t = Timer
        While i < 10000000
            sb1.Append(Format(r.NextDouble * 100.0, "###0.00 "))
            i += 1
        End While
        System.Console.WriteLine("Seconds elapsed: " & (Timer - t).ToString("#######0.00"))
        System.Console.WriteLine("Length of string builder: " & sb1.Length.ToString())
        System.Console.WriteLine()

        System.Console.WriteLine("Press any key to end the program ")
        System.Console.ReadKey()

I get:
Seconds elapsed = 11.56
Length of the string builder = 59001859

If I reverse the while statement:

Dim i As Integer = 10000000
        Dim r As Random = New Random()
        Dim t As Double

        System.Console.WriteLine("For loop 10,000,000 to 1 ")
        Dim sb1 As New stringbuilder
        t = Timer
        While i > 1
            sb1.Append(Format(r.NextDouble * 100.0, "###0.00 "))
            i -= 1
        End While
        System.Console.WriteLine("Seconds elapsed: " & (Timer - t).ToString("#######0.00"))
        System.Console.WriteLine("Length of string builder: " & sb1.Length.ToString())
        System.Console.WriteLine()

        System.Console.WriteLine("Press any key to end the program ")
        System.Console.ReadKey()

I get:
Seconds elapsed = 13.81
Length of the string builder = 59001457

You could also use a do until loop as well. A few things to consider: I'm using a slower processor and this uses stringbuilder to build some strings. I don't think your problem lies in the loop, I think it has to do with using Excel. I think that sql would be a bit faster. Also, is it 20k cells or 20k rows?

[Niya]

dday9 has a point. I would port that data to some kind of database like SQL Server and query against that instead. Database engines are made with exactly that in mind. Searching through it would seem instant.

[Shaggy Hiker]

I would say that it has to do with Excel, as well. Can't you query the spreadsheet into a datatable and search that? That should be faster, as well, but if you can query against the spreadsheet, then you could probably write a query that performed the search. Whether or not that query would be faster I can't say. Excel isn't really a database, though the database engine might not care.

[sico]

The code is somehing like:
R = 0
N = 0
For i as Integer = 1 to 999
R +=1
If Textbox1.text = Excel.cells(R,1).text then
N +=1
Excel.cells(N, 30) = R
Else if...
It then repeats the same for (R, 2) to (R, 20)
Endif
Next
This is ceating a list of all instances of a particular piece of data, that way i can cycle through the list of matches in no time at all, but the initial search takes roughly 20 seconds.

I was going to use a system with "cells(R, C)" and have it increase R when C = 20, but if 2 cells on one row match then they wil both get added to the list.

The other problem is that with this i have to type "andy" to get "andy" and i want to allow it to show "andy" if i type "an", i know a way to set this up, but that would mean searching letter by letter, meaning he 20,000 gets multiplied by the number of letters your searching for, minus any boxs with less letter than the search itself. Which would be a far bigger number.

I could move all the data from excel to Form1 when it loads, but that would still run at the same rate, untill all data has been sent across. i've noticed that it's slow because of excel, as i can get me.text from 1 to 1000 as soon as the form loads with a basic For Loop.

[Shaggy Hiker]

It's that Cells thing that is killing you. I seem to remember that navigating through cells is particularly slow in Excel via .NET, though I may be wrong about that. In any case, it is still the .Cells thing that is killing you.

You need to look into using ADO.NET to query the spreadsheet as a database and get rid of any of the Excel references for this. They've never been fast.

[techgnome]

Isn't there some kind of search functionality built-in? seems to me that's where one should start from.... *shrug* I could also be off my rocker... it's been known to happen.

-tg

[dday9]

Isn't there some kind of search functionality built-in

Control + F in excel :P

[dunfiddlin]

Superman? Oh no, that's a speeding bullet, isn't it?

[sico]

This software isnt for me, its for people who dont know alot about computers. I'm happy using the excel "filters" to find what i want.

If ForLoop.speed > superman.speed then
Computer = kaboom
End if

Thanks for the advice, i'll see what i can do.