We help IT Professionals succeed at work.

Database program slow or is this normal ?

reefcrazed
reefcrazed asked
on
220 Views
Last Modified: 2010-04-23
I have nearly compeleted several months of work creating a database form, it has multiple tabs and they have criteria for each tab page on what is displayed on the screen. The database is MS SQL with a hefty server and the database is around 36,000 rows of data. The database is brought into a dataset and split into multiple dataviews and the dataviews are filtered as people change the tab buttons. The program seems rather sluggish and it shows to be using 150mb of memory in task manager. Is this a lot or seem the norm with what I am trying to do ? The program takes about 10 seconds to launch and just seems sluggish when it does the filtered, which is just filtering by one field, but having to go through those 36,000 rows. As you can tell I have not been programming long and would just like some performance tips....

The original program was written in Foxpro and seems less sluggish, but we are trying to go to VB just to keep current and updating it will be easier in the long run.
Comment
Watch Question

Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
36,000 rows is alot to hold in memory at one time depending on the size of the record.  I would recommend finding another way to pull your data in chunks or as summaries.  How are you displaying your data?

Commented:
just may be another piece advice - whatever filtering or sorting or grouping you do, try to do it all on database side (in your SQL queries), do as least as possible in your coding.

and another suggestion - check if your application works fine with small database, you can copy your original database, delete most of it, like two thirds and see how's your performance improves - if it's still the same sluggish - must be something in your coding.

good luck,
yurich

Author

Commented:
Well there are about 7 tables and each share the PK, so I display each row as needed. There is a search page that jumps to the proper record after found. There are also buttons on the page for forward, back, first and last record. At one time though, only one record is shown. How can you display in chunks ? I will delete all but maybe 5k records in the morning and see what is going on there. You can understand my concern when I know for a fact that I after a year I still have a lot to learn, but there again my employer does expect some results.
melmersSenior Software Deveolper/Architect

Commented:
Port Your SQL functions to Stored Procedures on the SQL Server.
Use only the fields you really need, not use SELECT * FROM Table.
And at least reduce the data on your tables try to split the tables to some more fine grained tables.

Author

Commented:
Ok I killed all but 2500 records and the load time went to about 2 seconds and the form is less sluggish. I have no idea where to go from here though. I mean I cannot load 2500 records, then the search would no longer work right  ? I also have no idea what is involved on making a "stored procedure", i let the command builder make all of my SQL commands.

Author

Commented:
I went in to VB.net and let the program make the stored procedures. It did nothing for the load time at all, nor the filter speed of the dataviews. I think it is purely the size of the database and the fact that .Net stores an entire copy in the dataset. So is there a way around this, like have it pull only maybe 1,000-2,000 records, like only cache so many, then go back and pull others as needed ?? 150mb of memory is a alot, when the records where down to 2500 it only used 10mb.
melmersSenior Software Deveolper/Architect

Commented:
You can use the SELECT TOP 1000  * FROM Table then you get the first 1000 Rows.
But i think when you have an autoincrement field which is counting for you, you can
insert this in your select Query and reduce the number of records you retrive at once.

Author

Commented:
I tried the SELECT TOP 1000 by editing all my stored procedures and indeed it worked, but now all that is available is 1000 entries, even when I do a search they do not show up. I am not abandoning the idea, but what do I do next. The search form I made basically searches through certain fields in the dataset to find entries based on criteria.

If all of this fails then I may see if certain dates are just not needed. And maybe put a dropdown on the form to show "all data" and force another dataset load.
melmersSenior Software Deveolper/Architect

Commented:
do you have an auto increment field in your table then you can cycle through your table.

for example:
Define this at the Top of your Class
Dim IndexCounter as Integer = 0
Dim CounterStep as Integer = 1000


this is the Query sub which you call everytime you want new records from you db.
To Start at the top of the db set IndexCounter to 0

Dim MyCon as ODBCConnection
MyCon = new ODBCConnection("Your ConnectionString")
MyCon.Open()
Dim myCmd as new ODBCCommand("SELECT field1,field2,field3 from mytable where field1 = '" & testVar1 & "'" AND (ID < " & indexCounter & " AND indexCounter > " indexCounter + CounterStep & ")",mycon)
indexcounter += counterStep

Now you can Execute the SQLCommand and set the result to your dataset and bind it to your form.

Author

Commented:
Nope, no autoincrement field, sorry. I guess to get the time down I will have to get with some people and maybe trim the database down by doing a selective grab on the data, maybe only the last 5 years worth. Then somewhere in the form I could have something to click to load the entire database.
Senior Software Deveolper/Architect
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.