Johnny
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
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
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 ***********
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 ***********
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)
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)
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
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).tostri ng())
end while
end using
using MySQLCommand as new mysqlcommand(sqlstring, MySQLConnection)
dim MyReader as MySQLDatareader = MySQLCommand.ExecuteReader
while MyReader.Read()
console.writeline(myreader
end while
end using
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
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
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???
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???
ASKER
i am useing the exact code from LordWabbit example in the above post.
thats what im trying to speed up.
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.
-----
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.
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(CreateConnect ionString( strDatabas eName))
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
--------------------------
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(CreateConnect
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Public Sub PopulateGridOneByOne()
Dim DBCon As New FbConnection(CreateConnect ionString( strDatabas eName))
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("Fil eName", Type.GetType("System.Strin g"))
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(newFile Row)
Form1.DataGridView1.DataSo urce = 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...
Dim DBCon As New FbConnection(CreateConnect
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("Fil
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(newFile
Form1.DataGridView1.DataSo
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
thanks for the grade and glad it worked out!
NY
Select top 300 * from YourTable where "Your condition"...