The most efficient way to get all data from SQL Server table with varchar(max) column.

Question

This question is for SQL Server 2005.

I have a table with 2 columns.

Table_A
    Id Guid (PrimaryKey)
    TextContent varchar(max)

The table contains around 7000 records and textcontent range from 0 - 150K+.

When I do a select statement SELECT Id, TextContent FROM Table_A, it took a very long time around 10 minutes.

Is there a better way to get all data out of the table?

During the main execution I only load certain records only. Example: SELECT Id, TextContent FROM TableA WHERE ID IN (@id0,@id1, @id2, @id3....@id20). This query is not slow but not that very fast neither. I want to see if I can optimize the process by pull the TextContent ahead of run time. It is okay for this process to run in a minute or two but 10 minutes is not acceptable.

Answer 1

Yes

1)don't use SELECT * ever, always list your columns whether 1, 2 or 100 2)try looking into indexes

150k characters ? in that field ? Is that what you are referring to ?

Answer 2

I doubt it. If you want to "get all the data out of the table", then you have to read every byte that is stored within the table, and that may well require a lot of physical disk I/O.

Perhaps what you want is to only retrieve some of the data from the table?

Answer 3

This is the correct way to get the data out of the table, unless you only want 1 row. If all you need is 1 row, just use the correct query.

What sort of network connection are you using? Lets put it this way, you have 7000 records. Each contains on average 100k of data (for ease, if it is more or less than this, that's fine, my point still stands). The total query will return 700 MB of data! Even over an extremely fast connection, that is easily 10 minutes of download time.

Even under a PERFECT 100 Megabit connection, the transfer would take nearly a minute! In addition, you have to get that data off of the physical disk, which is going to take a while in addition.

I would recommend doing some sort of paging in order to take the data in smaller bites.

Answer 4

As many others mentioned, you're fetching a lot of data. First make sure if you need really all rows.

If you do, don't fetch everything at once - use LIMIT instead. This will actually decrease the speed, but if anything fails you'll only have to load a short bit again and don't have to wait another 10 minutes.

SELECT Id, TextContent FROM Table_A LIMIT 0, 30

This query will fetch first 30 entries of your table. With

SELECT Id, TextContent FROM Table_A LIMIT 30, 30

you'll get the next piece.

Maybe you could provide us with a bit more of info, like what you want to do with the data and which programming language you use?

Answer 5

Your Id column is a GUID. Are you using a default? Is it NewID()? I assume it's clustered on the PK.

If you use NewSequentialID() as the default, you'll get fewer page splits, so your data will be spread across fewer physical pages.

With that huge amount of data, that's the only thing I can see that would help the performance.

Answer 6

The GUID is primary key, which is also by default going to be your clustering key no doubt will be causing large fragmentation - but given the nature of the columns, the varchar(max) is going to regularily be off page in the LOB storage and not stored on page unless it fits whilst remaining within the 8060 limit.

So fragmentation is not going to be helped by having a GUID as primary if you also have made it clustered - you can check fragmentation levels using the DMV sys.dm_db_index_physical_stats

I wouldn't think fragmentation is really the problem unless the average amount of data per row is high e.g. regularily above 8k.

If it was,... the fragmentation starts hurting. Worst case is 1 row per page, 7k I/Os which is not ideal, but at 100k average per LOB storage, you could be looking at further 87k I/Os and the order in which the data has been written etc would result in what is supposed to be a sequential scan of the table (and the disk), turning into a massive random I/O fest as the disk heads long stroke back and forth between the page with the row + LOB pointer and the LOB pages. Added to that is the chance te GUID is the clustering key, so it couldn't even scan the data pages without quite a bit of disk head movement.

I also have to agree with Erich that the quantity of data you are trying to shift across the wire will cause quite a delay on an insufficient link and you should look to properly filter your data at the server level through paging or suitable queries.

I know your looking to pre-cache the data, which can work at times - but it is being performed on such a large entity, it tends to indicate something else is wrong and you are fixing the wrong issue.

A.