Filter Big data with limit result in VB.NET and SQL

I struggle with implementing a big data search form of up to 500,000 records based on many fields, as text boxes, on the text change event.

I've tried two solutions with no satisfactory result.
First one, on text change: select top 1000 with filtering then, bind datatable to datasource.

The result was very slow.

Second one, retrieve all data then apply filtering on datatable:

dt.DefaultView.RowFilter = filter

The filtering is very fast, but retrieving data on load of the search form take a period of time in addition to if the user clear text the grid will contains 500,000 records.

I need to limit data to 1000. I tried 'take' that led to another undesirable points because of the loss of reference due to
CopyToDataTable. How can I proceed?

Well, first of all, 500,000 rows is a small table these days.

With VB or even MS Access, I often hit a table with a little over 10 million rows in SQL server, and the response time is like 1/10th of a second.

So, it not clear why your query of data would be slow.

However, without question you would never pull 500,000 rows and then try to filter the data.

I mean, when you use the web to search, do you download the WHOLE internet into a browser, and THEN have the user hit Ctrl-F to search the results? Of course not!

When you use a bank machine, does the machine download every account and then ask you for the account number? Of course not!

When you launch and use an accounting package, does it download every company and then you use say Ctrl-F to find the one company? Of course not!

So, be it 2000 rows, or 10 million, your search is not going to result in that many rows, and thus you filter at the query pull time based in the criteria entered by the user.

So, think of any software you have ever used in the last 50+ years. In every case, a simple prompt of some type of search is used, and then you do a database pull. You don't pull 100,000 invoices from a system and then search.

You prompt the user for the invoice number, and then display that invoice form. So, speed of the database will not matter if you have 10 rows, or 10 million rows. That invoice form is going to display and launch instant.

Think of what would occur if you have 10 users on the system. If each user pulls 500,000 rows (and not done one bit of useful work), you already created a database server load of 5 million rows!

A datagrid is good for a few 100 rows tops. Anything more, and you doing this all wrong.

So, build a search form, allow the user to enter some criteria and THEN pull that data into the data grid.

You don't mention what kind of data you looking to search for.

Say I want to search a database to "select" a city. Any city in the world. So, that database will have about 150,000 rows. (one for each city on planet earth).

Yet, I don't encounter a delay of even 1/1000th of a second!

So, this example:

So, above is a example of some of the concepts you need to consider here.

So, you might for example want to filter hotels.

So, you might say have this:

So, no matter how you approach this, you need to ask and have some kind of filter and criteria before you execute that query, and only pull the data based on that query.

You also want to ensure that the query used is:

• Parameter safe (SQL injection safe)
• Is what we call "sargable" (means indexing can be used).
• That you have index(s) on the columns being searched.

So, you might want to show a query that you working with, and with some idea(s) on what you want to filter, then I can post some additional code here.

Edit: Text box as you type search

As above shows, 4 super easy clicks and we narrowed down the City, and did not even touch the keyboard!

But, let's hit the 150,000 rows, but search as we type.

So, in this example, to not kill the database server.

So, so we will simple NOT search unless the user types in at least 3 characters. That way, we get a fantastic reduction in the number of records.

So, a simple text box, datagrid view.

This code for the KeyUp event.

Private Sub TextBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyUp

    If TextBox1.Text.Length >= 3 Then
        ' search database
        Dim strSQL As String =
            "SELECT City, state_name, country_name 
            FROM vCities WHERE City like @City + '%'"

        Dim cmdSQL As New SqlCommand(strSQL)
        cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = TextBox1.Text

        DataGridView1.DataSource = MyRstP(cmdSQL, My.Settings.Countries)

        lblCount.Text = "Row count = " & DataGridView1.RowCount

    End If

End Sub

And the result is this:

Note how we really never seem to hit/get more then about 300 records.

So, it is fast, instant, and we still not beating the data server to death. Make sure you have a database index on the column to search (in this example the City column).