Re: faster way to write tables?

On Wed, 20 Feb 2008 04:41:16 -0000, Allen <anugent@xxxxxxxxxxxxxx> wrote:

I'm building a large lookup table by using an ADOCommand, repetetively setting the CommandText (and invoking Execute) in a loop for each row. The CommandText string looks something like:

SELECT INTO out_table (col1, col2, col3) VALUES (x1, x2, x3)

It works, but it groans a bit when there are ~50,000 records; I wouldn't want to use this method for one million records.

Is there something I can do to speed up output, like buffering the command strings to SQL Server?

Allen,

Can you give us a clue on where these new "rows" are coming from?

First for the relatively fast, simple ways...

a) If they are a selection from another table, you can ask SQLserver to do it all for you...
insert into out_table ( select x1, x2, x3 from tableA inner join tableB... where... )


b) If the values are in a text file (Comma/Tab separated or Fixed Column) you should be able to use something like the MS Access SQL...

insert into [PortfolioClass]
select * from
[Text;HDR=Yes;Database=D:\Temp\;].[PortfolioClass#txt];

Note the mangled syntax - that loads a file "D:\Temp\PortfolioClass.txt"


If your program has constructed these values itself - say in a dynamic array of records - then it is a lot more fiddly than it should be to get them into the database /quickly/!

But without getting into any details of record sets, temp tables and the like, SQLserver does have an extension which will let you insert multiple records in one round-trip to the server...

insert into StatusTable ( xEntityId, xActualExportStatus )
select 45, 9
union select 46, 9
union select 412, 9
union select 413, 9
union select 414, 9
union select 416, 9
union select 417, 9
union select 418, 9
union select 419, 9
union select 420, 9
union select 421, 9
union select 422, 9
union select 423, 9
union select 424, 9
union select 425, 9
union select 426, 9
union select 427, 9
union select 428, 9
union select 429, 9
union select 430, 9
union select 431, 9
union select 432, 9
union select 433, 9
union select 434, 9
.....

While this may look distinctly "kludgy", it does work; it does have reasonable performance; and it is very simple to program :)

And SQLserver will accept much longer commands than you would expect. I regularly issue commands of up to 30K characters when building the driver tables to construct database subsets.

HTH

--
Paul Scott
Information Management Systems
Macclesfield, UK.
.

Relevant Pages

  • Re: SQL-Abfrage (OT=SQLServer)
    ... (select top 5 kontaktid from kontakte where partner_nr=11000 order by datum_kontakt desc ... Lothar Geyer schrieb: ... Folgende SQL-Abfrage läuft mit einer Access-Datenbank, aber nicht mit dem SQLserver 2000: ...
    (microsoft.public.de.vb.datenbank)
  • SQL-Abfrage (OT=SQLServer)
    ... Folgende SQL-Abfrage läuft mit einer Access-Datenbank, aber nicht mit dem SQLserver 2000: ... Die einzelnen SQL-Statements funktionieren ohne Problem, sobald das UNION dazukommt, bekomme ich die Fehlermeldung "Für die Datentypen text, ntext oder image kann DISTINCT nicht angegeben werden.". ...
    (microsoft.public.de.vb.datenbank)
Quantcast