Database performace help

David Smith

I have a question, do you think its wise, I am looking for speed performance. Lets say I have 300,000 records in the database. Basically I want to Execute the fill below before or load the data before entire application loads. I would have a progress bar that basically letting the user data is being loaded. One thing I did notice once the I have executed fill before, I use linq to pull from the properties below, the queries works fast.

private databaseDataSet.testTable redTable { get; set; }
private databaseDataSet.testTable blueTable { get; set; }
private databaseDataSet.testTable purpleTable { get; set; }
private databaseDataSet.testTable greenTable { get; set; }
private databaseDataSet.testTable yellowTable { get; set; }
private databaseDataSet.testTable brownTable { get; set; }

///

/// Initializes the data.
///

        public void InitializeData()
        {

            this.TableAdapter1.Fill(databaseDataSet.red);
            redTable = databaseDataSet.red;

            this.TableAdapter2.Fill(databaseDataSet.blue);
            blueTable = databaseDataSet.blue;

            this.TableAdapter3.Fill(databaseDataSet.green);
            greenTable = databaseDataSet.green;

            this.TableAdapter4.Fill(databaseDataSet.yellow);
            yellowTable = databaseDataSet.yellow;

            this.TableAdapter5.Fill(databaseDataSet.purple);
            purpleTable = databaseDataSet.purple;

            this.TableAdapter6.Fill(databaseDataSet.brown);
          brownTable = databaseDataSet.brown;

        }

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs14y ago
Yes, Darnell I know your connection is slow. You really need to get a wired connection. I have Verizon FiOS, which is a fiber optic connection, and it is fast. I would have mentioned things like the connection but I assumed that was already considered.

It is good to hear that you are finally using SQL Server instead of Access.

I don't know about the technical details of views. There are at least two reasons I can think of that a view would provide better performance. I think that a view is executed in the server before sending data to the workstation, which would require less data across the network. I know that stored procedures work like that so I might have views confused with stored procedures. Another possible explanation is when you create a view the database system can analyze the requirements and decide what is the best way to do it. Of course I am not sure of any of that.
0
David Smith14y ago
Sam I will looking into the indexes as far as speed goes. What I notice just few moments ago. I just created a view , a view seems to work much faster then the table adapters for somereason. I wonder why.

Vuples all along the reason why the database was taking long was because of a network issue, I was also on vpn. As soon as I connected to the iternet using an ethernet cable, the database was working as fast as ever. So yea when working wireless, you will not get your best performance. At least in my case, I don't. I get alot of timeout errors when working wireless.
0
David Smith14y ago
Hi sam, the project you were talking about was learning curve for me coming out of college, I am much better database architect now then two years ago. Since then I have been on at least up to 6 differennt database application developments ot task using Infragistics control etc..... I learn alot from you whether you believe or not. We are no longer using Access, I am using SQL Server 2008, and one of my task now I am using MySql which I hate so much lolololol. SQL Server is much better to me when comes to editing Editor purposes and much more. But yeah my expertis when it comes to designing databases has went up tremendously.
0
Sam Hobbs14y ago
If you do not have indexes that the query can use then the most effective way to improve performance is to create indexes.

If the database is the one I am familiar with, then we have known for a long time that it needs to be redesigned. Database design is something that a professional DBA must be tough about; it can be a difficult job just because it is tough to tell people that the database must be designed in a manner that the experts know is best. You are probably in the position where you are told you must design the database a certain way but it is not the way that database experts would design it so that might be the cause of the current problem.
0
Vulpes14y ago
If you have a single processor then, no matter how many threads are running on it, they can only do the same overall amount of work in a given time; in fact overall work will be slightly lower because of the overhead of creating, managing and switching between threads.

If you have multiple processors or a multi-core machine (commonplace, of course, nowadays), then different threads can be run on different processors or cores, which enables you to do more work in a given time.

So, the point I was trying to make is that the background worker won't speed up the loading of the data on a single processor machine but (because the operating system will be switching between the threads) it will stop your UI from freezing until all the data has been loaded.

On the other hand, if you had a dual core machine, then the data could be loading on the background thread (running on one core) whilst the UI was concurrently loading on the UI thread (running on the other core).

Provided your query is efficient, there's probably little you can do to increase the speed at which the data is pulled in from the database server. Of course, once the data has been loaded into a DataSet in memory then subsequent accesses will be very fast.
0
David Smith14y ago

what do you mean by multiple processors or cores vuples, i just want to make sure i am on the right page.

"This won't actually speed up the loading of the data unless you have multiple processors or cores but it will prevent the UI from freezing in the meantime."
0
David Smith14y ago
I know how to use background workers, thats not the problem. whether i am using a background worker or not, I want to know, what will give me speed performance. What I was thinking about doing is just fill everything either on a back ground worker or excuting the fills before the application launch fully, that way it wont take long to query or retrieve data.

What would you do, and how to speed performance.
0
Vulpes14y ago
You could try using a BackgroundWorker to load the data. There's actually an example which uses a ProgressBar in the MSDN docs for this class:

http://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker.aspx

This won't actually speed up the loading of the data unless you have multiple processors or cores but it will prevent the UI from freezing in the meantime.
0