Adding record to SQL Server is very very slow!

[tfierens]

Hi all,

my title might not be quite fair on SQL, but here are the facts:

Before we use to use record set, and we were inserting what we call criteria information i.e name, surname, etc... Using recordset in vb was inserting around 40 recs/sec. I have gone using the 'Insert Into' and the execute method from the ADO connection. I am now getting around 90 recs/seconds. This is quite a good improvement.

But when I thought about it, my problem did not really lie in adding criteria records. This was always decent even when more than 10 millions records in the database.

The problem lies when we insert full text pages in it. This is a major problem. This has practically brought down our application to insert 1 records every 1 minute taking well over 20 hours to process 2000 pages. Ridiculuous I know!!!

Is there a way to insert a full text page using the INSERT INTO Method! I has been giving me a lot of errors!! Any ideas would be appriciated.

Also when trying to add a date to sql server, it is giving me an error about invalid date format. In VB and using record set, you usually put #10/01/2001#, what is it for SQL, I thought it was just a case of putting the # or '. Any info on this would also be appriciated.

The text information is usually info such as invoices, credit notes, statements, etc... So it could also contains characters such as ' since you can have names like O'Leary (Especially if you live in Ireland!).

Any help will as always be greatly appriciated.

Regards,

Thierry

 

[fluteplr]

I can`t help you with the first problem but this is what we do for the O`Leary problem.

The issue as I see it is that people are entering a single quote ['] (ascii 39) when they should be entering an apostrophe [`] ascii 96. In our applications we capture the single quote keystroke and replace it with an apostrophe. When uploading data we do a search and replace fitst.

Date Formats. There are a lot sql server will accept, I usually use 'yyyy-mm-dd hh:mm:ss' This is a nonambiguous format as compared to the one you are using. is 10/1/2001 October 1st or January 10th? (For those of you who have not experienced european dates, some countries put the day first.)

BTW to help other people answer your text problem, what version of ADO and SQL are you using?

 

[tfierens]

Thanks for getting back to me Flutplr,

Sorry about the lack of details!

I'm using SQL Server 7.0 (SP3) and ADO 2.5 (going on 2.6 this week though!).

As the European Date Problem, unfortunately, I'm looking at it as a European, and look at it as a US date problem! I don't think I have ever written an application that is 100% compatible with both!! Any I guess since you are the guys writting it, you're entitled to call it a european problem

As for the quote instead of the apostrophe, you are correct. We use to do that but all our clients though it was weird, so we eventually changed it! Is that the best solution you can think of i.e. replace the ' by something else. We have done this elsewhere and it always has worked fine, but I was hoping for something else.

If anyone else want to have a go at answering my first question, please feel free! i.e How to insert a full text page of data via INSERT INTO!

Thanks!

Thierry