Fast insert and select at the same time?

I have two tables: Account and AccountTransction. Each table contains more
than 20 million records. It one-to-many relationship between account and
AccountTransaction. The new records are constantly loading into each table
through text file by using DTS.
Question:
My team member insists that using cursor to insert record one by one to the
table to avoid affect (lock) the selection on these tables. . There are tons
of indexes on both tables for fast searching. The insertion process is
extremely slow. I recommended batch mode insertion, instead of one by one
using cursor. It is much more faster and efficient in terms of insertion,
but the selection while insertion going on is a little bit slower. What is
your suggestion? How can I achieve the fast insertion and fast selection at
the same time'
Is cursor alway a bad idea in terms of speed and performance?
Thanks a lot,
FlxI would never use a cursor to insert one row of data one by one...
Your colleague is smart to have worries about contention. However... it's
quite easy to do this in a safe manner. My standard technique for manaing a
situation like this is to:
* insert N number of rows per batch through an insert into stmt
* N is tested to ensure
- the insert happens fast enough to have a negligible impact on blocks
for selects
- durtion between batch inserts is long enough to ensure we're not
having a constant impact and quueses aren't growing
- but N is large enough to ensure I can insert enough records fast
enough such that the insert process isn't horrible slow.
I've been able to achieve VERY high insert and select throughput using
techniques like that...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"FLX" <nospam@.hotmail.com> wrote in message
news:e5rEoTiAEHA.3004@.TK2MSFTNGP10.phx.gbl...
> I have two tables: Account and AccountTransction. Each table contains more
> than 20 million records. It one-to-many relationship between account and
> AccountTransaction. The new records are constantly loading into each table
> through text file by using DTS.
> Question:
> My team member insists that using cursor to insert record one by one to
the
> table to avoid affect (lock) the selection on these tables. . There are
tons
> of indexes on both tables for fast searching. The insertion process is
> extremely slow. I recommended batch mode insertion, instead of one by one
> using cursor. It is much more faster and efficient in terms of insertion,
> but the selection while insertion going on is a little bit slower. What is
> your suggestion? How can I achieve the fast insertion and fast selection
at
> the same time'
> Is cursor alway a bad idea in terms of speed and performance?
> Thanks a lot,
> Flx
>
>
>
>
>
>
>
>
>
>
>|||Here's an out-of-the-box idea.
Create new tables, same schema, so you have pairs to tables.
These new tables are for 'todays' data.
Create views to cover the pairs of tables. These are what your application/u
sers look at / use.
The DTS populates the 'today' tables.
Once a day, at some light / quite period, stop the DTS. Copy / Move 'todays'
data into the oringal, large table
Don't index the 'todays' tables, as they will (hopefully) be small enough to
not need them. Or add only essential indexes.
Or only do this for the AccountTransaction table, and use the current method
for the Account table.
If possible, you might want to drop the main table indexes just before you l
oad the data from the 'todays' tables.
It depends, of course, on how quite your quite period will be.