Solved

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

Posted on 2006-10-19
17
307 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Johnny
  • 8
  • 4
  • 2
  • +2
17 Comments
 
LVL 15

Expert Comment

by:JackOfPH
Comment Utility
use this query to display the top 300

Select top 300 * from YourTable where "Your condition"...
0
 

Author Comment

by:Johnny
Comment Utility
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
0
 
LVL 15

Expert Comment

by:JackOfPH
Comment Utility
******** 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 ***********
0
 

Author Comment

by:Johnny
Comment Utility
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)


0
 

Author Comment

by:Johnny
Comment Utility
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
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
maybe you can use a DataReader to display info as it is being loaded.
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
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
0
 

Author Comment

by:Johnny
Comment Utility
heres a reference to the code examples im useing (thx LordWabbit)

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22002221.html

my problem is its really slow to display the info and it waits to display it till all the info is gathered
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
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???
0
 

Author Comment

by:Johnny
Comment Utility
i am useing the exact code from LordWabbit example in the above post.
thats what im trying to speed up.
0
 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
i suggest dont use adapter .. instead directly use reader to read data
0
 
LVL 9

Expert Comment

by:tolgaong
Comment Utility
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.


0
 

Author Comment

by:Johnny
Comment Utility
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
0
 

Author Comment

by:Johnny
Comment Utility
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
0
 
LVL 13

Accepted Solution

by:
newyuppie earned 500 total points
Comment Utility
this
While Result.Read()
            Return Result.Item(RecordName)
        End While

will make the function terminate at the first read item.

im thinking something like this (untested sorry):

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 '%M:\%'", DBCon)
        Dim Result = DBCom.ExecuteReader()
 
        While Result.Read()
Dim newFileRow As DataRow = fileTable.NewRow()

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

datagridview1.datasource = fileTable

        End While
        DBCon.Close()
        DBCon.Dispose()
    End Function
0
 

Author Comment

by:Johnny
Comment Utility
   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...
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
sorry for typo, i was patching from various sources.

thanks for the grade and glad it worked out!

NY
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now