Geek Answers Handbook

Select a million + records when a huge insert is performed.

I am trying to extract an application log file from a single table. Query request output is quite simple.

select top 200000 * from dbo.transactionlog where rowid>7 and rowid <700000 and Project='AmWINS'

The request time for selection is above 5 minutes. Is it considered for a long time? While making a selection, bulk insert is performed.

[EDIT]

In fact, I had a serious problem with my current production database. Basically, we only have one table (transaction log). the entire application log will be inserted into this table. For a project like AmWINS, based on the counting results, we have about 800K ++ records inserted per day. Insert recording is performed 24 hours a day in a production environment. The user would like to extract data from the table if the user wants to check the transaction logs. Therefore, we need to select entries from the table, if necessary.

I tried to simulate the UAT environment in order to pump it in volume according to the production, which is still growing up to 10 million records. and while I am trying to extract the records, at the same time I am simulating a volume insert so that it looks like in a production environment. It took only 5 minutes to extract 200 thousand records.

During the extraction, I observe on the phyiscal server the SQL server, which reaches a maximum of 95%.

The tables have 13 fields and the identifier is included (rowid) with bigint. rowid is a PC. Indexes are created by date, project, module and RefNumber number. tables are created in rowlock and pagelock. I am using SQL Server 2005.

Hope you guys can give me some professional advice to enlighten me. Thanks.

+4
source share
7 answers

You may be able to use the "Nolock" table tooltip as described here:

MSDN Tips Table

Your SQL will look something like this:

 select top 200000 * from dbo.transactionlog with (no lock) ...

This will provide better performance if you do not care about the full accuracy of the returned data.

+2
source

What do you do with 200,000 lines? Do you use this over the network? Depending on the width of your table, a simple amount of data over the network can be a major part of the time spent.

+1
source

It depends on your hardware. Pulling 200,000 rows when data is inserted requires serious I / O, so if you don't have a 30+ disk system, it will be slow.

Also, is your rowID column indexed? This will help with the selection, but may slow down the volume insert.

0
source

I'm not sure, but doesn't inserts into MS SQL insert the whole table?

0
source

As already mentioned, ck. Indexing is important. So make sure you have the appropriate index. I would not only set the index for rowId, but also for Project. I would also rewrite the where-to clause:

 WHERE Project = 'AmWINS' AND rowid BETWEEN 8 AND 699999

Reason: I assume that the project is more restrictive than rowid, and - correct me if I am wrong - BETWEEN faster than the <and> comparison.

0
source

You can also export this as a local dat or sql file.

0
source

No indexing will help here because it is a SELECT * query, so it most likely scans PK or an awful search in books

And TOP doesn't make sense, because there is no ORDER BY.

Simultaneous insertion is probably misleading, as far as I can tell, if only the table contains only 2 columns, and the main insert blocks the entire table. Using a simple int IDENTITY column, insertion and selection may also not interfere with each other.

Especially if the volume insert is only a few thousand lines (or even 10,000 seconds)

Change TOP and rowid do not mean million plus

-1
source

More articles:


All Articles