|
SQL Server 2005
Database sizes 25-30Gb
There are only two sites running the software at the moment.
I've recently joined a very small new company, and taken on several existing software products. I'm very concerned about the sizes of their customers databases. I've checked that the log file has not grown excessivly which was not the case, after that i have looked through all the tables in the database and found that 95% of the storage is in two tables. Checking the storage allocation for both those tables i have found that each has around 25% of the sorage being used for data, and 75% for indexing. This seemed very wrong to me, can any one recomend a way of re indexing the tables so that the storage being used is lower. If its just a case of removing all the indexes and starting again is there any thing i should be aware of.
Thanks in advance for any replies. If more specific information is needed please let me know.
|
|
|
|
|
Sometimes the reality is indexing needs more space than the data itself, so 75% for indexing may not be out of line. You can simply backup the database and restore it (to a different location) so the database table and indexes are re-created. That way you can find out if the indexing really needs that much space or the original database needs compacting. Chances are if the indexing part of the original database needs compacting, the data part also needs compacting. The ratio may change but indexing still needs more space than the data itself.
|
|
|
|
|
this database has been freshly restored from a backup so i guess from what you have said that would rule out the compacting being required.
To give some more of an idea of the issue the tables that are being affected are used to store financial transactions, one table contains 7.7 million rows, the storage cost is 3.9gb for the data, and 16.4gb for the indexing. This amount of data is resulting in very slow processing of even simple select queries, and my thoughts are that to resolve the problem some kind of archiving needs to be implemented.
This is probably a dumb question but do the data types being indexed have a direct affect on the storage space required for the indexing. i.e. would indexing a column of the type guid be significantly more storage intensive than an int column.
Sorry if the question is overly simple, i've worked with sql databases in the past and never seen storage requirements even close to this.
|
|
|
|
|
The data types being indexed does not have impact on the size used to store the indexes. What you described indicates even more indexing is needed for the database table since some simple "select" queries are very slow. You may need to remove some indexes not being used by the program and add some new indexes based on the queries frequently conducted. When the needed index does not exist, "select" queries on such large tables will be very slow. I was once amazed by how much performance gains was brought by adding proper indexes when I was working on a large database table (similar to your case). So it may worth a try to get a close look at the database to find out what you need for it.
|
|
|
|
|
|
thank you, i'll have a thorough read through this asap.
|
|
|
|
|
If the data is forced into two main tables with little else going on I would expect them to be indexed to the hilt in order to get the data back out.
It all depends on the database construction and the associated business rules.
|
|
|
|
|
Hi,
I am looking for a generic database option which will be used to save some admininistration configuration information of a web application. I want to have the database generic and license free. For example, I do not want to use SQL Server or Oracle etc. But customer A can have sql server and Customer B can have Oracle, but this configuration information should be easy enough to be exported to other database systems. I was thinking about XML. But I heard sometimes the file gets too big to handle. Not sure if MS Access or admin site's inbuilt db system will help. If anybody knows any other light weight database system (it can be open source) which is easy enough to be exported to general database systems, please let me know the thoughts.
Thanks,
Vijay
|
|
|
|
|
This is one that you could take a look at: SQL Lite[^]
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
XML is good for configuration. If you are concerned about it growing large, then I doubt it is configuration.
If you need a database, then try to be as database agnostic as you can be so different clients can use different databases -- make it configurable.
|
|
|
|
|
Good Afternoon,
I used a nice SQL insert/update program for years with MSSQL 2000, but was never able to get it past security to make it work with 2005. This weekend, out of frustration, I decided to take a shot at writing one of my own. In doing so, I attempted to list the databases that were on the server. I first tested the command in the Query analyzer and it worked fine, returning all the names of the databases. However, when I tried to use the exact same code in the application, every database name comes up as "master" in the array, so I cannot continue to propagate the tables without a named database. I cannot understand how the identical code can return different results in different places. Here is the code.
Appreciate your assistance....Thank you, Pat
string Sql = "SELECT name FROM sys.databases";
SqlCommand Comm = new SqlCommand(Sql, Conn);
SqlDataAdapter da = new SqlDataAdapter(Comm);
DataSet ds = new DataSet();
da.Fill(ds);
int cnt = ds.Tables[0].Rows.Count;
string[] dbs = new string[cnt];
for(int i = 0; i < cnt; i++)
{
dbs[i] = ds.Tables[0].Rows[0]["name"].ToString();
}
foreach(string s in dbs)
{
databases.Items.Add(s);
}
|
|
|
|
|
Cross-post, answers go here[^] 
I are Troll 
|
|
|
|
|
Hi,
I am working in conservation and I am sent and record various records of various insects to record. I use MS Access 2007 to import the records into. I have seperate tables for seperate years 2007, 2008, 2009 etc...(all tables have the exact same fields, just different records for each different table) What I need to do is take all this data and find out which species was the most recorded in a year or least recorded etc.. I want to do this with a report and a chart of some sort (Pie or bar I think might be best)
I have four fields on the table I wish to use the "Scientific Name" the "Location" the "Quantity" and the "Date"
1) How would I be able to take the Quantity field and find out the total numbers for each species using the Scientific name and creating a report for this? I could have 50 records with the same scientific name, but I would like totals for these. And same for all species recorded
2) Using what I said above, I'd like to create a report and chart on the top highest recorded numbers of 5 different species and another on the lowest recorded species in one table
3) Finally, using what I wanted to do above, I wanted to do this for years, to show how species are doing over years. All the records I have are in different tables with the same fields as I already mentioned, but how would I be able to use all the tables say "Species 2007" "Species 2008" etc.. to create totals for ech seperate species recorded using the "Scientific Name" field to get the species name.
Any help would be appreciated.
Dave
In the end we're all just the same
|
|
|
|
|
1) You should be able to use the query builder to do a group by query. IIRC there's a button with a plus sign that allows you to do that easily.
2) Create a query that sums the number (same as for 1?) and sorts by sum descending and select the top 5 records. Create a similar query but sort by ascending.
3) I think you will need a Union query where each part is a query same as those in 1 and 2 but the source for each part of the union will be the different tables.
Two points. First you might change the data model to have one table with a column to store the year instead of separate tables for each year (you could easily do that with an action query to insert records from the existing tables). That could simplify all your reporting.
Secondly, not used Access for years so there might be better ways.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
|
IMO not using Access is the better way.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Hi Dave,
Thanks for the info it was useful, sorry for late reply
In the end we're all just the same
|
|
|
|
|
HOW TO DEPLOY *.SDF FILE OF SQL COMPACT VERSION
is i need to install sql server on client machine to deploy this file ?
For more help : pranayamr.blogspot.com
|
|
|
|
|
Here:
MSDN[^]
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
|
Hi all
I am using VB 2008.
I am developing a program that writes millions of lines to a database.
The type of database isn't decided yet.
I think I'll be using SQL databases (sdf) that VB provides.
I need to add millions of rows at one time.
Can I do that by using multiple threads?
I mean I'll divide the data in ,say, 6 parts and write each part using a different thread.
My whole purpose is to save time.
As fast as the writing occurs my boss is that happier.
He doesn't want to know how that is being done, he just wants to know that its been done.
I know sqlite can't do this.
Is there any other database which is able to do that.
It must not use any dll.
My boss wants the app to be independent.
Would that result in data loss?
even if the rows are written out of order, no problem.
-- Modified Sunday, May 16, 2010 7:27 AM
|
|
|
|
|
It's hard to find any database engine that does not use .dll files. SQLite can be linked to your program so that a .dll file is not needed. However, you mentioned that it's not sutible for your project.
You can use multiple threads to write to an SQL Server database, but the actual performance of the writing operation may not be better than single-threaded programs. The speed is limited by the performance of the hard drive and whether your database is setup to run in distributted environment. If your database resides on a single hard drive, I doubt that you will see any performance improvement by using multi-threading.
You can do a lot more on high-end database products. The question is whether or not your boss is willing to spend a lot of money on high-end database products?
|
|
|
|
|
thanks for answering
the reason that I don't want to use sqlite are:
1. multi-thread writing is not supported.
2. it requires a sqlite.dll file in the exe folder to work.
If you think that performance wouldn't differ much using multiple threads then
i can very well use a single thread.
I'll carry out the tests.
If you know a method so that my app can become a two file app ie one exe and one database file
then please tell me.
in the above post ANDY_L_J has pointed out that .sdf databases need 7 dlls.
if that's that then using sqlite is a better option as it requires only 1 dll file.
|
|
|
|
|
I am afraid I don't have a suggestion to your case. Since you are using VB, the need for dll seems to be unavoidable.
Note that in the case of SQLite, C/C++ programs can link the whole SQLite into the program so you end up with just one exe file with no need for any dll files. That meets your "no dll" requirement but it's not using VB. Too bad.
|
|
|
|
|
Do you need a database at all?
is your app going to have multiple users, sharing the data?
If you don't need the existing data to generate new data, you could (each) create a simple file, then perform a "bulk insert".
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Hullo Luc
I need a database as I need to perform some operations that only a database can do effectively.
no its not a multiple user program.
I have a large quantity of data that I want to write to the database
as fast as possible.
I thought of dividing the data into ,say, 5 parts and use 5 threads to put these 5 parts
so that my work is completed in 1/5th time.
Am I correct in thinking so?
If I do that would that result in data loss?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
