Fast query

[magnitude]

hello all,

I'm currently developing an app for mobile device.
at form load my app execute a query to populate the the dropdown box with a distinct values from 3600 records unfortunately it takes a while for the form to load it looks like its freezing for a few minutes. is there a way to query the 3600 records for distinct value in a fast way.

here is my code.
at form load it will call the functions getpartslist to get the unique values in the [assembly] fields

Private Sub frmBranding_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim c As Integer = 0
        Dim count As Integer = GetPartsList.Count - 1
        cboParts.Items.Clear()
        For c = 0 To count
            cboParts.Items.Add(GetPartsList.Item(c)) ' will loop to the rest of the records and populate the combo box.
        Next
    End Sub

Public Function GetPartsList() As List(Of String)
        Dim parts As New List(Of String)
        Dim rs As New SqlCeDataAdapter("SELECT DISTINCT([ASSEMBLY]) as [PartName] FROM Parts", DBconnExt)
        Dim dt As New DataSet
        Dim c As Integer = 0
        rs.Fill(dt, "Ass_List")

        
        If dt.Tables("Ass_List").Rows.Count <> 0 Then
            For c = 0 To dt.Tables("Ass_List").Rows.Count - 1
               parts.Add(dt.Tables("Ass_List").Rows(c).Item("PartName"))
            Next
        End If
            Return parts

    End Function

I'm doing this right? please let me know
thank you.

[Shaggy Hiker]

That shouldn't take minutes. The query is simple, and the number of records isn't all that much. A second or two, at best. Make sure that you have indexes set up correctly on the table, as that will improve the performance of the query, but I just don't think that will be the issue (ASSEMBLY should probably be indexed).

A bigger issue may be the way you are populating the controls. Adding each item individulally might be causing the control to redraw every time, even if you aren't seeing it. Why not set the datasource of the combobox to the datatable directly. That should be considerably faster than what you are doing. Alternatively, there may be a property for the combobox that disables updating during populating. You would set that before starting the update, then clear it when done.

[magnitude]

Thanks shaggy, i did your suggestions it looks like it minimized the waiting time to load, as for the index i created 1 index name that includes 3 fields is this correct? or should i create 1 index for each fields that i need.. and another problem is that. i have a listview item that use to display a mixed value which means that the first column of the listview would be coming from the query and the second colum would be coming from the `RFID reader. this RFID reader capture an identification number and pass it to the select query command and which have to search again the 3600 records to much the scanned ID then i got the few freezing moment again.
but the listview doesnt have a datasource property.

[Grimfort]

To note, the real issue was that you were running that query 3601 times ....
The datasource was not cached, when you call:

Dim count As Integer = GetPartsList.Count - 1

It will run it once, then you just use the number.

Then you call this:

cboParts.Items.Add(GetPartsList.Item(c))

Which means you run the query another 3600 times. Whooops!

Off the top of my head, something like this could do:

cboParts.Items.AddRange(GetPartsList.ToArray)     'might not need the ToArray part

[Shaggy Hiker]

Wow, totally missed that. You're right. The GetPartsList wasn't all that fast anyways, since it was using a datatable (unless the datatable was bound to the control), but calling the table in the loop thrashed the DB pretty severely.

Searching 3600 records is trivial, and should take no noticeable time. If you are getting freezing then something is written wrong. As for indexes, I'm not a real expert on them, but I believe that an index should be built on any field that you will be searching on frequently.

[techgnome]

Correct... index the field... and use the .AddRange method... it will significantly speed things up.

-tg

[magnitude]

thanks for the suggestion guys but i realized the combo box in .net compact doesnt have the member .addrange so im sticking with the datasource for now but still takes like 10 seconds or so. Im afraid our end user might think the app is crashing because it freezes for a few second.

I'm also worried as have more this kind approach coming on the way lol. like feching record directly from internet database( exporting and importing records to internet MYSQL server database will also take a freezing moment). signn.

anymore suggestion guys? or the processor speed also involves on this?


Thanks

[techgnome]

try changing the SQL query ... instead of this:
SELECT DISTINCT([ASSEMBLY]) as [PartName] FROM Parts
try this:
SELECT Assembly as [PartName] FROM Parts group by Assembly

When you use a distinct, it has to get all of the results first before it can sort through it and pull the unique values outs. With a group by, it manages it as it is processing. I've used this a number of times and noticed a difference in query times.

Hmmm.... fetching records directly from the interwebs? that's tricky... 1) you can't guarantee a connection... 2) there's going to be serious lag... Maybe what you need to is a "Please wait" notice while database operations are being handled, so that way at least the user doesn't think the device has locked up and attempts to pull the battery.

-tg

[Shaggy Hiker]

I didn't see the bit about fetching from the interwebs (in fact, I still don't). How about caching? You are filling a datatable, you could serialize that out to an XML file, then deserialize it back into a datatable that will hold the last state while the update is occuring. It might appear more pleasing.

I was counting on a local database. If the connection is out on the cloud, or something like that, time is unpredictable. Therefore, you have to do some kind of wait process while data loads.

[techgnome]

From post #7 - " like feching record directly from internet database" ... I think he is using a local database... in fact, he almost has to as he's using SQLCEClient namespace...
Dim rs As New SqlCeDataAdapter("SELECT DISTINCT([ASSEMBLY]) as [PartName] FROM Parts", DBconnExt)

-tg

[Shaggy Hiker]

I was going to post a link to the lightweight profiler class I posted over in the CodeBank, and suggest that you might use that to narrow down where the bottleneck is. However, it appears that the search feature isn't working quite right, yet, as I appear to have no threads in the .NET CodeBank.

[magnitude]

Thanks for the suggestion guys, I will try those suggestion later on as i dont have the handheld today it is out for a demostration.
in the meanwhile i have attached here the diagram of my project.



-The Central PC DB will act as the main pc that will collect all the records
-each of this site pc is located in different location it has like 3 or more handheld to be connected to it subsequently
-the records should be synchronize in each location and each handheld in that location. so for instance the hh1 changes the record it should be updated in all the handheld involves
-then after all the the sychronization all the changes will be copied to the central pc to export the result.

so thats the connections i need to accomplish, there would a fetching of records via internet db
any suggestions about this guys?
thanks