|
LukeV wrote:
that contains only 764 words (it will likely contain 10X that amount eventually).
Only 7000 words!? I supposed you have much, much more data than this!
Well, load the entire table to memory with a SELECT * (a std::map would be just fine), drop it and rewrite the whole table to disk...
The whole process should take only 2 or 3 seconds this way.
Q261186 - Computer Randomly Plays Classical Music
|
|
|
|
|
Daniel Turini wrote:
Only 7000 words!? I supposed you have much, much more data than this!
It could be more, since the table will grow with time. I wouldn't be suprised to see it around 10-20k but I don't think it will grow any bigger than that...
Daniel Turini wrote:
drop it and rewrite the whole table to disk...
The whole process should take only 2 or 3 seconds this way.
So perhaps what I should do is the get the table at the beginning of the app, update the map and rewrite the table to disk at the end of the program... But if the app crashes, I'll lose everything and I don't know if it would consume too much memory when the table grows...
I'll let you know how it turns out. Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
LukeV wrote:
By the way, maybe it's because I set the tables poorly (I didn't use indexes and such...) maybe the problem's there!
Why should you expect database to be fast if you don't bother to set up indexes? Why database at all, if you don't index your data? Just store everything in a large text file.
You should realize that there's no magic in programming. Search algorithms are deterministic - and you are required to organize data into tables and indexes based your logical dependencies and search criteria.
BTW, are there any reasons you yse dynamic accessors and not static? This will also cost you some ticks.
Vagif Abilov
MCP (Visual C++)
Oslo, Norway
Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros.
Tomasz Sowinski
|
|
|
|
|
Vagif Abilov wrote:
Why should you expect database to be fast if you don't bother to set up indexes? Why database at all, if you don't index your data? Just store everything in a large text file.
I know... It's just that I never had a project that required a huge database so speed wasn't a concern.
Vagif Abilov wrote:
BTW, are there any reasons you yse dynamic accessors and not static? This will also cost you some ticks.
I'll give that a try as well... Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Access isn't the fastest DB, but you should be able to achieve adequate performance for 20k rows of a relatively small table.
First, make sure you used indexes and use the right one. If the column 'word' contains a unique list of words, use the "Indexed - no duplicates" type of index.
Second, check the data type and size you used. It looks like you are using a "Text" data type which should be OK, but check the length of the column. The default is 50. Is this more than you need, if so, use a smaller value. Access always uses varchar data but you can force it to fixed-width by pre-filling the column with non-space data (such as the under-score _).
Third, the select count(*) statement will require a full table-scan unless the "indexed - no duplicates" option is used on the column. If you just need to know if the item is in the table, just do a "SELECT WORD from table where word = 'blablabla'". This would generate better performance if not using a unique index.
Fourth, consider removing the "select" statememt altogether. Just do an UPDATE and if it fails check the SQLCODE and perform an INSERT.
Sixth, if this is a single threaded GUI app you could just open a recordset against the table and disconnect it from the DB.
Seventh, if this is a GUI app (not ASP based) you can just load the whole word list into memory. If the maximum length of a word is 50 bytes and you can have 30K words, then you will need 1.5mb of ram.
Eighth, if you use an in-memory list and you don't want to risk losing updates, add a new table to your db called "Update_History" with an auto-number column and a 'Word' column. Each time the in-memory list is incremented, insert a record into this table. When your app finihes normally, walk through this table and perform the UPDATEs/INSERTs against your original table and then delete the table contents. When you app launches, check this table and if it is not empty just do the updates then.
|
|
|
|
|
What I found out is that it's real slow when I'm debugging the app and like 10X faster when I just run the app (not from the debugger) DOH!
But I'm positive that it used to be slower than that... so the changes you people suggested proved to be worthy. Thanks!
---------------
Concentrating on Ideas
http://www.edovia.com
|
|
|
|
|
Hi all
I have aan XSD file that represents a schema of a set of tables in my DB. (it was generated with the MSDatasetGenerator, but I can't use the code it created, only the xsd.)
I create a dataset and use the ReadXmlSchema method to fill the schema info from the file to the dataset. now I populate this dataset with information from another source (not with any dataadapter, I'm working straight with the table collection, I have another xml file with the rules for that).
Now I need a way to update the data in the DB. the schema is the same as the db, same table names, same columns, just insert all the data to the DB tables.
No hardcoded update command, or insert commands.
I tried using SQLXML but with no success.
any ideas?
thanks in advance
Noam Ben Haim
Web Developer
Intel
noam.ben.chaim@intel.com
|
|
|
|
|
Hello all,
I am trying to connect from ASP.NEt to a MySql server.
I have the MySql Driver 3.51 installed.
I have created a DSN also.
Which set of components can I use to do the connectivity ?
I also has trouble with the connection string
Thanks in advance
Smitha
It's easy to sit there and say you'd like to have more money. And I guess that's what I like about it. It's easy. Just sitting there, rocking back and forth, wanting that money.
|
|
|
|
|
Just as a note, there are _a few_ managed providers (ie like System.Data.SqlClient) for MySql that should work well, and probably a lot faster than an ODBC approach.
I don't remember any links, but search sourceforge.net (or google) or something
Morty
|
|
|
|
|
Thanks Morty
Tweety
It's easy to sit there and say you'd like to have more money. And I guess that's what I like about it. It's easy. Just sitting there, rocking back and forth, wanting that money.
|
|
|
|
|
Hi, I need to install (MICROSOFT EXCEL DRIVER (*.xls) in a computer with win98. Anybody knows what files do I need or when can I get information about it?
My program uses :
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=TRUE;CREATE_DB=\"%s\";DBQ=%s", sDriver, sExcelFile, sExcelFile);
where sDriver must be MICROSOFT EXCEL DRIVER
|
|
|
|
|
Jet 4.0[^] is what you need, I believe.
Paul
Why don't you take a good look at yourself and describe what you see - Led Zeppelin, Misty Mountain Hop
|
|
|
|
|
Ok, thank you.
But if I want to redistribute my app, is there an easier way to do it? I don't want to force to the user to download Jet4.0 , is it not possible to install only excel driver easily?
Thanks.
|
|
|
|
|
José Luis Sogorb wrote:
is it not possible to install only excel driver easily?
That is considered "easily" . It's a 3-ish Mb download and you can even wrap it in your own installer. You probably can find a way to isolate the Excel files and registry entries but it really isn't recommended.
Paul
Why don't you take a good look at yourself and describe what you see - Led Zeppelin, Misty Mountain Hop
|
|
|
|
|
Could someonehelp me to write this stored procedure?
I have two datetime column and ID column(its integer).I pass an ID to stored procedure and want to check if today is between these two date,and return true or false.
Thanks
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
This should work:
<br />
-- create the test table<br />
create table t1(ID int, date1 datetime, date2 datetime)<br />
<br />
-- insert test rows<br />
insert into t1 (ID, date1, date2) values (1, '15 Jan 2001', '20 Feb 2003')<br />
insert into t1 (ID, date1, date2) values (2, '01 Sep 2000', '19 Apr 2001')<br />
<br />
-- drop the proc if it exists<br />
if object_id('sp_CheckDates') is not null drop procedure sp_CheckDates<br />
go<br />
-- create the proc<br />
create procedure sp_CheckDates @ID int<br />
as<br />
declare @Date1 datetime<br />
declare @Date2 datetime<br />
select @Date1 = date1, @Date2 = date2 from t1 where ID = @ID<br />
<br />
if @Date1 is not null and @Date2 is not null begin<br />
if datediff(dd, @Date1, getdate()) > 0 and datediff(dd, @Date2, getdate()) < 0 begin<br />
print 'Bingo!'<br />
return 0<br />
end else begin<br />
raiserror ('Date not in specified range', 16, 1)<br />
return 1<br />
end <br />
end else begin<br />
raiserror('Specified ID does not exist.', 16, 1)<br />
return 1<br />
end<br />
go<br />
<br />
-- test execution<br />
exec sp_CheckDates @ID=1 <br />
|
|
|
|
|
Thanks.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Create Procedure sp_CheckDate (@ID int)
As
DECLARE @Today datetime
SET @Today = GetDate()
Select
Case
When @Today Between DateColumn1 And DateColumn2 Then
CAST(1 As bit)
Else
CAST(0 as bit)
End As Result
From
TableName
Where
IDColumn = @ID
Replace the DateColumn1 , DateColumn2 , IDColumn and TableName with the real names. The procedure will return a row with a single field called Result , which will contain the value.
VBScript:
Function sp_CheckDate(connection, ID)
Dim strSql, rst
On Error Resume Next
strSql = "sp_CheckDate (" & CLng(ID) & ")"
Set rst = connection.Execute(strSql)
If rst.Eof Then
'ID not found, or there was an error
sp_CheckDate = False
Else
sp_CheckDate = CBool(rst.Fields(0).Value)
End If
rst.Close
Set rst = Nothing
End Function
C#:
bool sp_CheckDate(SqlConnection connection, int ID)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "sp_CheckDate";
command.CommandType = CommandType.StoredProcedure;
SqlParameter p = command.Parameters.Add("@ID", DbType.Int32);
p.Value = ID;
try
{
object ret = command.ExecuteScalar();
if (ret is bool)
return (bool)ret;
else
return false;
}
catch (SqlException ex)
{
return false;
}
}
|
|
|
|
|
Thanks.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
I need to take a delimited file and import it into a SQL table. The table is already setup and I have MANUALLY imported the file to populate it and get it going. This file will be updated EVERY night and I want to keep the table updated. How do I automate this? What is the SQL code? Should I drop the table and re-create it nightly?
|
|
|
|
|
In your DTS task (You do have a DTS task, don't you?! ), issue a TRUNCATE TABLE command to delete all rows from the table, and then import the data.
|
|
|
|
|
Reply to the forum, please!
Robby wrote:
i appreciate the information, but is there anyway you can give a code example? i have never created a table from a txt file, only standard SQL INSERT stuff. the file is pipe delimited and column names are on first row...any ideas?
Thanks,
Robby
- Open up SQL Server Enterprise Manager.
- Right-click on the target database, and choose
All Tasks -> Import Data . - For the source, choose
Text File , and select your text file. - Follow the instructions to set up the import.
- In the final screen, select
Scheduled DTS package for later execution , and click on the "... " button to set up the schedule.
|
|
|
|
|
Hi,
We have a SQL database which has gone out to many users and has gone through
an evolved conversion process.
The problem I have is there is a field I need to alter but because it is set
to NOT NULL and has a default value for some users (the problem) sometimes
there is a constraint and sometimes not.
I can check for the constraint using "if exists" but sometimes I do not know
what the constraint is called as its name has been generated by SQL server
and contains a GUID.
Is there a simple clean way I can drop a column and constraints that apply
to it.
I know we are going to have to tidy the overall database up for these users
in the longer term but need a short term quick fix.
We are using MS-SQL server 7 and MS-SQL server 2000.
Thanks
Dave
|
|
|
|
|
Hi!
I develop WIN32 software that uses MySQL database. My software uses MySQL
ODBC driver 3.51 and ADO interface. MDAC 2.7 is installed. My problem is
that while I'm trying to update MySQL record contained char(15) field, I get
an error "Microsoft OLE DB Provider for ODBC Drivers Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available. No
work was done." I don't have such problem with numeric fields. The numeric
fields are updating normally with the same code.
Here is a sample of my code:
try
{
_RecordsetPtr pRecordset;
HRESULT hr;
_bstr_t btmp;
hr = pRecordset->Open(vTable, vConnection,adOpenForwardOnly,
adLockOptimistic, adCmdTableDirect);
hr = pRecordset->AddNew();
...
pRecordset->Fields->GetItem(L"id")->Value=btmp;
...
pRecordset->Fields->GetItem(L"proto")->Value=btmp;
...
pRecordset->Fields->GetItem(L"src_ip")->Value=btmp;
}
The fields "id" and "proto" are numeric, the field "src_ip" is char. And I
get the above error while updating the "src_ip" field. Do you have any
ideas? Can you help me?
Yours sincerely,
Alex Bash
|
|
|
|
|
Hi,
I really need help with a major problem I have encountered. I'm trying to insert information into a SQL Server 2000 database using stored procedures. And each table that has a Primary Key (int) won't return a value using SELECT @@IDENTITY. The error states that ArtistID doesn't except null values and will not insert into table. Here's the source code for the stored procedure:
CREATE PROCEDURE ArtXchangeWeb.procManagerInsertArtist
@ArtistFName text,
@ArtistLName text,
@ArtistBirth text,
@ArtistDeath text,
@ArtistCountry text,
@ArtistNationality text,
@ArtistMovement text,
@ArtistBio text,
@ArtistPhoto varchar(255)
AS
BEGIN
INSERT INTO Artist(ArtistFName, ArtistLName, ArtistBirth, ArtistDeath, ArtistCountry, ArtistNationality,
ArtistMovement, ArtistBio, ArtistPhoto)
VALUES(@ArtistFName, @ArtistLName, @ArtistBirth, @ArtistDeath, @ArtistCountry, @ArtistNationality, @ArtistMovement, @ArtistBio, @ArtistPhoto)
/* Return the ID of the new artist */
SELECT @@IDENTITY
END
GO
If it doesn't give me the previous error it gives me a Input String not the right type error. Here's the code behind .vb:
If IsPostBack Then
'Save the new product to the database
With cmdManagerInsertArtist
.Parameters("@ArtistFName").Value = txtFName.Text()
.Parameters("@ArtistLName").Value = txtLName.Text()
.Parameters("@ArtistBirth").Value = txtBirth.Text()
.Parameters("@ArtistDeath").Value = txtDeath.Text()
.Parameters("@ArtistCountry").Value = ddlCountry.SelectedItem.Text
.Parameters("@ArtistMovement").Value = ddlMovement.SelectedItem.Text
.Parameters("@ArtistNationality").Value = ddlNationality.SelectedItem.Text
.Parameters("@ArtistBio").Value = txtBio.Text()
.Parameters("@ArtistPhoto").Value = txtArtistImg.Text()
cnn.Open()
Session("ArtistID") = .ExecuteScalar
cnn.Close()
'And redirect to the management page
Server.Transfer("ManageArtist.aspx")
End With
If you can help me out that would be greatful. I wouldn't be a programmer if it wasn't for sites like yours and I've searched around for answer to my problem and to no avail I'm still stuck in the same spot. I hope you can help me.
Thanks,
Gregory Foreman
|
|
|
|
|