|
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?
|
|
|
|
|
If all that happens on a single machine, I doubt having multiple threads will really help, but I haven't tried it. I'd rather go for fewer SQL commands, so I am in favor of bulk insert, if that fits the application.
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).
|
|
|
|
|
Jack_18 wrote: I have a large quantity of data that I want to write to the databaseas fast as possible.
A database isn't optimized for bulk logging in realtime. If "as fast as possible" takes five seconds, would that be considered acceptable?
Jack_18 wrote: I thought of dividing the data into ,say, 5 parts and use 5 threads to put these 5 partsso that my work is completed in 1/5th time.
Adding a thread doesn't mean that your work is done in half the time. How many threads can your system run simultaneously?
If you need to log a lot of data, consider dumping it as suggested and have a second proces write it to a database.
I are Troll 
|
|
|
|
|
Have a Google on '.net embedded database'.
The first link offered has several suggestions and the fifth (judging purely from the subject line) might fit your needs.
Good luck!
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
Why do programmers often confuse Halloween and Christmas?
Because 31 Oct = 25 Dec.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
