[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2006-10-19
17
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +2
17 Comments
 
LVL 15

Expert Comment

by:JackOfPH
ID: 17771434
use this query to display the top 300

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

Author Comment

by:Johnny
ID: 17771449
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
ID: 17771451
******** 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Johnny
ID: 17771504
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
ID: 17771508
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
ID: 17771545
maybe you can use a DataReader to display info as it is being loaded.
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17771551
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
ID: 17771616
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
 
LVL 21

Expert Comment

by:K V
ID: 17771642
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
ID: 17771662
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:K V
ID: 17771690
i suggest dont use adapter .. instead directly use reader to read data
0
 
LVL 9

Expert Comment

by:tolgaong
ID: 17771977
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
ID: 17773506
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
ID: 17773550
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 2000 total points
ID: 17773784
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
ID: 17777911
   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
ID: 17778214
sorry for typo, i was patching from various sources.

thanks for the grade and glad it worked out!

NY
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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