February 3, 2011 at 2:09 pm
hi to all. i have 2 question.I am a newbie. please
Following are my questions
1-what's the best and fastest way for fill datagridview with 5 million record ?
2-can who clearly explain to me <true manage transactions>?(please explain with example)
tanks a lot
February 3, 2011 at 2:16 pm
saeed hg (2/3/2011)
hi to all. i have 2 question.I am a newbie. pleaseFollowing are my questions
1-what's the best and fastest way for fill datagridview with 5 million record ?
2-can who clearly explain to me <true manage transactions>?(please explain with example)
tanks a lot
A datagrid view, who on this green earth would want to scroll through a datagrid view to look at 5 million records - examine what the user really needs, and then supply them with just that, nothing more.
Edited to add:
For a nice tutorial on manged transactions go here:
http://www.slideshare.net/markginnebaugh/sql-server-transaction-management-presentation
February 3, 2011 at 2:48 pm
as bitbucket stated, you need to re-evaluate the requirements...noone will ever need to actually view that many transactions without filtering it for relevancy, so don't go down that rabbit hole....
take a look at this ms article on using datagrids and paging...maybe that is really what you were looking for?
http://msdn.microsoft.com/en-us/library/aa479006.aspx
Lowell
February 4, 2011 at 12:53 am
tanks for answers. my friends please attention, this is only for test and learn . i want to learn working with datareader and fill database with a lot of data (data genaration). please guide.;-)
February 4, 2011 at 10:35 am
Here is some code (Originall posted by Jeff Moden and some what modified by myself)
Note that the code uses a temp table, but you can change that by inputting a table name without the "#" as the first character of the table name, that is substitute "MYHead" for "#Myhead", or any table name you want to use. That said here is some code.
--=============================================================================
-- Author: Jeff Moden
-- Create the test data.
-- Usually takes something less than 12 seconds to generate.
--=============================================================================
--===== Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
GO
--===== Create the table and populate it on the fly.
-- This builds a table with random dates and amounts for 20 years starting
-- in the year 2000.
SELECT TOP (5000000) --This is the number of rows inserted.
SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),
SomeAmount = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)
,SomeDecimal = CAST(RAND(CHECKSUM(NEWID()))*100 AS DECIMAL(8,2))
,SomeInteger = CAST(RAND(CHECKSUM(NEWID()))*100 AS INT)
,SomeVarchar =CAST(RAND(CHECKSUM(NEWID()))*100 AS VARCHAR(50))
INTO #MyHead
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Add a clustered index to the table just to keep it from being a heap
-- and to help with speed a bit by sorting the data.
CREATE CLUSTERED INDEX IX_#MyHead_SomeDateTime
ON #MyHead (SomeDateTime)
;
SELECT * FROM #MyHead --Just to check to make sure it worked
February 7, 2011 at 4:46 am
tanks a lot. dear Lowell , your suggest way was paging but paging only for webbase application(i think), my question is fill datagrid with data in vb.net.
my dear friends , please read carefully my question. my question is <fill datagridview with 5 million record>
for this question:
first- we must have a database with 5milion record; exist 2 way:
1.use query. dear bitbucket-25253 used this way
2.use redgate data generator- i use this way(it's an easist way)
second- show data in datagridview. my problem is this.
i wrote a program with vb.net and tried to show record in datagrid but when i preesed show data button program hanging.
i think we have to sulotion for solve this problem:
1-use timer
2-use Thread
are these true?
how can i use these?
my vb.net source:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim str As String
str = "data source=.;initial catalog=storep;integrated security=sspi"
Dim sqlcon As New SqlConnection(str)
Dim SqlCmd As New SqlCommand
SqlCmd.CommandText = "Select * from table_1"
SqlCmd.CommandType = CommandType.Text
SqlCmd.Connection = sqlcon
Dim DR As SqlDataReader
sqlcon.Open()
Dim i As Integer
i = 0
DR = SqlCmd.ExecuteReader
While (DR.Read())
DataGridView1.Rows.Add(DR.GetString(0), _
DR.GetString(1), _
DR.GetString(2), _
DR.GetString(3), _
DR.GetString(4))
i = i + 1
End While
DR.Close()
End Sub
February 7, 2011 at 7:17 am
Lowell
take a look at this ms article on using datagrids and paging...maybe that is really what you were looking for?
saeed hg -Think of what your select statement Select * from table_1. Think of the data in your table as a huge lake of information, your select query is attempting to push all the information from that lake into a very small diameter pipe to some other location (your program). It will take time, depending upon your I/O capacity. To get a general idea of that length of time, just execute your select statement in SSMS and learn how long under the best conditions that will be. Too long would be my guess, so then look at the article in Lowell's post and transmit the data as it suggests, a page (a small fraction of the total data) at a time.
If this still creates problems - repost and we will attempt to assist you again.
February 7, 2011 at 7:41 am
paging your data requires some sort of key...whether it's the PK/identity of the table, or by using row number.
assuming you have a "Next" button on your application to get the next "Page" of data, you need to build your query code-wise to look something like this:
Dim WhichPage as Integer = 3 '--the third page/group of information
Dim BatchSize as Integer = 25 '--grabbing in groups of 25
Dim cmd as String "Select * from table_1 WHERE ID BETWEEN ({1}* ({0} -1)) AND ({1} * ({0} -1)) + {1}"
cmd = String.Format(cmd,WhichPage,BatchSize )
SqlCmd.CommandText = cmd
here's a clean TSQL example.
declare @WhichPage int
Set @WhichPage = 3
SELECT * FROM (
select row_number() OVER(ORDER BY ID) AS RWNBR, * FROM SYSOBJECTS) MYALIAS
WHERE RWNBR BETWEEN (25 * (@WhichPage -1)) AND (25 * (@WhichPage -1)) + 25
Lowell
February 8, 2011 at 11:27 am
why would you go into reading line per line and insert that into the dataview.?
put the result of your sqlstatement into a dataset
dim myds as dataset = new dataset("sqlstmt","sqlconenction")
then assign the dataset to teh dataviewer control and you all done..
Ps: would take VB.net miliseconds to fill this dataset 😀
Wkr,
Eddy
February 8, 2011 at 12:31 pm
Sjeef (2/8/2011)
why would you go into reading line per line and insert that into the dataview.?put the result of your sqlstatement into a dataset
dim myds as dataset = new dataset("sqlstmt","sqlconenction")
then assign the dataset to the dataviewer control and you all done..
Ps: would take VB.net miliseconds to fill this dataset 😀
Wkr,
Eddy
the time over the wire, plus the rendering of the data presentation in the datagrid is what would kill performance; no conventional desktop can store and then render that amount of data in a timely fasion.
assume that one row of data is 2K characters...say 10 columns columns of various data types, plus a 1000 char description;
@ 5 million rows, isn't that ~ 93 Gig of data? if i'm off by 2^8, that'd still be 93 Meg of data, right?
/*
Totalbits KBytes MBytes GBytes Totalbits KBytes MBytes
100000000000 97656250 95367 93 100000000000 97656250 95367
*/
with myCTE As(select convert(bigint,2000) * convert(bigint,50000000) As Totalbits),
TK As (select myCTE.*,Totalbits / 1024 as KBytes FROM myCTE),
TM As (select TK.*,KBytes / 1024 as MBytes FROM TK)
SELECT TM.*,MBytes / 1024 as GBytes ,*
FROM TM
FROM TM
Lowell
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply