Link to home
Start Free TrialLog in
Avatar of Johnny
JohnnyFlag for United States of America

asked on

How to speed up a dataset display, and show the info as its found

i have a dataset being displayed to the screen i want to speed it up as it finds all the info in the database then it displays the info. this takes a few mins on large data blocks

how can i display the info as it finds it to the datagrid, and is there any way to speed this up some...
optionaly if this is not hard to do how can i also show how meny records/lines its displaying as it displays them, ie

Displaying lines 1 - 300

after some time passes and has the info

Displaying Lines 301 - 500

etc etc

thank you in advance for any code and help you may provide
Avatar of JackOfPH
JackOfPH
Flag of Philippines image

use this query to display the top 300

Select top 300 * from YourTable where "Your condition"...
Avatar of Johnny

ASKER

im talking about a datagrid in speeding it up from reading a firebird db info...how to display it faster and not have to wait for allthe data to be read
******** first time you executed the query ******************
SELECT TOP 300 *
FROM YourTableName
WHERE YourCondition

****** second time you executed the query ********************
SELECT top 200  * from tablenmame where your <condition> not  in  (select top 300 from tablename)

******** third time *******************************

SELECT top 200  * from tablenmame where your <condition> not  in  (select top 500 from tablename)

******** do the repeatition until you reach youre last record ***********
Avatar of Johnny

ASKER

for database select statements to return limted info theres commands of
3) LIMIT x, y (return first y rows starting at offset x)

In Firebird it looks like this :

SELECT FIRST y SKIP x * FROM mytable;

LIMIT x (take first 10 rows) looks like this:

SELECT FIRST x * FROM mytable;

-----------------------
now back to the question,
thats not what im looking for id like to speed up the datagrid displaying the db info... it seams to load the info first then display it..this is really slow with 30k of rows.. it takes a bit to display id liek to know if i can display this info as it gathers the info to the datagrid...

as a seprate not really inportant to this question id like to display the datagrids lines its displaying in chunks.(but this is another question just wanted to stick it in as its another thing i am unsure of how to do)


Avatar of Johnny

ASKER

i was not clear im useing firebird to store the info and get the info passed to the datagrid

the way im doing it now is really slow
maybe you can use a DataReader to display info as it is being loaded.
i dont know what database you use but the code will look something like this probably:

using MySQLCommand as new mysqlcommand(sqlstring, MySQLConnection)
  dim MyReader as MySQLDatareader = MySQLCommand.ExecuteReader()

while MyReader.Read()
   console.writeline(myreader(0).tostring())
end while

end using
Avatar of Johnny

ASKER

heres a reference to the code examples im useing (thx LordWabbit)

https://www.experts-exchange.com/questions/22002221/What-can-i-use-to-make-a-BIG-database-type-program.html

my problem is its really slow to display the info and it waits to display it till all the info is gathered
Avatar of theGhost_k8
Using TOPs will really a bad idea AFAIK, its alway  bettter to query DB and display result.. n not query twice....
again, prob. will be more clear if u post what query u are using... as a lot depends on it too...
well u can use Readers to read it faster...  
I'll suggest to debug the code.. check where it takes to long... well i know... only while ur retriving data from fDB...
or filling DS...
query once will be always faster....
post what query ur using???
Avatar of Johnny

ASKER

i am useing the exact code from LordWabbit example in the above post.
thats what im trying to speed up.
i suggest dont use adapter .. instead directly use reader to read data
Note: Queries are taken from JackOfPH
-----
Here is my way of solution to your problem. (Logic only)

Add a backgroundWorker to your project (or two maybe)
in your form_load event (or where ever yu want)

load some data in to your dataset ( let say first 100 records)
SELECT TOP 300 *
FROM YourTableName
WHERE YourCondition

then bind this dataset to yur datagridview.
this will show the data in the grid.

Right after this execute the background worker
bgWorker.RunAsyncWork() --or similer.I don't have a compiler now to check

in the doWork event of the backgroundworker.
use the next sql you have. --> ex: SELECT top 200  * from tablenmame where your <condition> not  in  (select top 300 from tablename)

Don't forget to use the same dataset so the new data will be added to your current dataset in the background.
pretty simple?

Now, let's see if we can change this to a function.

here is the parameters
dg as datagridview
ds as dataset
sql as string => ex: SELECT TOP xx * FROM YourTableName WHERE YourCondition
interval as integer

by replace'ing xx with the interval you can perform the first query. then you can add the condition to the end of the query "not  in  (select top yy * from tablename)" for the further queries.

if this function succeeds, then you canextend a datagridview and place this code in it, so you can use it anywhere.

If there is a point which is unclear please let me know. I hope the above logic is what you want.


Avatar of Johnny

ASKER

im useing firebird for the databse

-----------------------------------------------------------
useing:
SELECT top 200 * from FILES
sql repies:

Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 12
200
-----------------------------------------------------------

as for the reader code - i tryed to make it read one of the fields/heres my test code:

    Public Function Read_Sql_Record(ByVal RecordName As String)
        Dim DBCon As New FbConnection(CreateConnectionString(strDatabaseName))
        DBCon.Open()

        Dim DBCom As New FbCommand("select * from FILES where FILENAME like '%M:\%'", DBCon)
        Dim Result = DBCom.ExecuteReader()
        'Form1.Label4.Text = Result.FieldCount ' returns how meny fields in table
        'Form1.Label4.Text = Result.Depth ' i think it returns rows count
        'Form1.Label4.Text = Result.RecordsAffected ' i think it returns how meny rows are in quarry
        'Form1.Label4.Text = Result.HasRows '  returns true/false if quarry has rows

        While Result.Read()
            Return Result.Item(RecordName)
        End While
        DBCon.Close()
        DBCon.Dispose()
    End Function

the code that uses the adapter is in LordWabbit link above
im not sure how to get the datagrid populated useing the reader (new to firebird and sql statments via vb.net / have used via php/mysql - but this firebird/vb.net is diffrent) so any help with the code would be grately welsome as im strugling to get a working example to fill the datagrid with a background threed and useing the reader

useing the reader would alow me to display the results in chunks too, and allow for a picture to display the readding and searching progress a bit better too.... just codeing is a diffrent matter.

please bare in mind firebird and background threeding is very new to me, thx

thanks for all the help so far
Avatar of Johnny

ASKER

the post dated Date: 10/20/2006 12:18AM EDT has the code in place of the top statement if thats needed for the code to work right. just to point out as i may not have been clear firebird does not work with limit and top statements for its sql quarries... FIRST y SKIP x is used in there place
ASKER CERTIFIED SOLUTION
Avatar of newyuppie
newyuppie
Flag of Ecuador image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Johnny

ASKER

   Public Sub PopulateGridOneByOne()
        Dim DBCon As New FbConnection(CreateConnectionString(strDatabaseName))
        DBCon.Open()

        Dim fileTable As DataTable = New DataTable("Files")
        Dim IDCol As DataColumn = fileTable.Columns.Add( _
            "FileID", Type.GetType("System.Int32"))
        IDCol.AllowDBNull = False
        IDCol.Unique = True
        IDCol.Autoincrement = True
        fileTable.Columns.Add("FileName", Type.GetType("System.String"))

        Dim DBCom As New FbCommand("select * from FILES where FILENAME like '%%'", DBCon)
        Dim Result = DBCom.ExecuteReader()

        While Result.Read()
            Dim newFileRow As DataRow = fileTable.NewRow()

            newFileRow("FileName") = Result.Item(1).ToString()
            fileTable.Rows.Add(newFileRow)

            Form1.DataGridView1.DataSource = fileTable
            Application.DoEvents()
        End While
        DBCon.Close()
        DBCon.Dispose()
    End Sub

i had to mod a line it was wrong (assume you copied from another project or mistyped)
i also added in the doevents so it displayed while running

works perfectly...can view as they are populating...
thank you ever so much for the help...
sorry for typo, i was patching from various sources.

thanks for the grade and glad it worked out!

NY