Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.net DataGridView.Fill performance

Posted on 2011-02-11
3
Medium Priority
?
1,017 Views
Last Modified: 2012-05-11
I have a VB.net program that includes a DataGridView.  The binding source pulls data from a table with as many as 250,000 records in the table, depending on how many people are logged in and currently using the program.  The records to display in the dgv for a given user are filtered by the machine name like this:

Me.PCTSNUGZFILTERBindingSource.Filter = String.Format("MACHINE = '" & strMachine & "'")

The fill command is this:

Me.PCT_SNUGZ_FILTERTableAdapter3.Fill(Me.SHOPFLOOR_SNUGZ.PCT_SNUGZ_FILTER)

The user will see anywhere between 6,000 and 30,000 records, depending on how many orders are in the pipeline at that time.

Initially, it was taking up to 45 seconds to load the data and display it.  I tracked the bottleneck down to three processes:  2 SQL stored procedures and the dgv.fill.  Each process took about 15 seconds.

I worked on the SQL stored procs and reduced the time on one of them to less than 1 second and the other to less than 5 seconds.  

However, I don't know how to speed up the dgv.fill process.  I have studied available postings on the internet, but I haven't found anything yet that helps me.  Does anyone know of ways that I can speed up the dgv.fill process?  

T
0
Comment
Question by:thoecherl
[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
  • 2
3 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34874671
You are returning upto 250,000 records to the client and then filter them? You are using loads of memory and transferring a lot of data over network.

Change your stored procedure to take the machine name as a parameter and only return the filtered rows.
0
 

Author Comment

by:thoecherl
ID: 34875288
I think I explained it poorly.  The table used as the data source is called PCT_SNUGZ_FILTER.  It is populated by a stored procedure that uses the machine name as a parameter and pulls data from a view that consists of 3 table joins.  The procedure selects every record currently in production and inserts the values of the record into the PCT_SNUGZ_FILTER table and populates a column called MACHINE with the current users machine name.  When the user logs off, all records in the table with that machine name are deleted.  So if there are 10,000 records in the production pipeline and I am the only one using the program, there will be 10,000 records in the table.  But if 25 of us are logged on, there will be 250,000 records.

When I populate the dgv, however, I only want to see the records with my machine name in the MACHINE column.  I thought about creating a temp table for each logged in user so each table would only have 10,000 records in it, following our example, but I don't know how to then make that table the data source for that user's session.  As I understand it, I select a datasource assigned to the dgv and I can't make it dynamic, depending on the user.

So I can certainly write a stored procedure using the machine name as a parameter and return only the filtered rows, but what do I return them to?

T
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 34883545
> but what do I return them to?

To a datatable. Here is an example

Dim dbadp As New SqlDataAdapter("Storedprocedurename", "connection string")
dbadp.SelectCommand.CommandType = CommandType.StoredProcedure
dbadp.SelectCommand.Parameters.AddWithValue("@machine", "machine name")
Dim dResults As New DataTable
dbadp.Fill(dResults)
dbadp.Dispose()

dgv.DataSource = dResults
dgv.DataBind()
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

610 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