Effect on performance on retrieval of image column from a table with half a billion records on remote server

Question

I require to loop through different servers to collect data in a temporary table. The table accessed on these servers have about half a billion records. To retrieve the data from these tables, I filter out rows using a list of values for one of the columns. The query takes about 5-10 mins for each server. However, if I add image column to the select list, the query hangs.

I was wondering if it is acceptable to obtain image column in such a scenario ? Will it hit the performance ?

CREATE TABLE #TempPI(
RowId INT IDENTITY(1,1),
[id]  [char](22)  NOT NULL, 
[name]  [varchar](128)  NOT NULL, 
[time]  [varchar](50)  NOT NULL, 
[url_id] [char](22) NOT NULL
--file IMAGE NULL)

CREATE TABLE #TempBrowser(
[browserid]  [char](22)  NOT NULL)

BULK INSERT #TempBrowser
FROM '\\Shared\BulkLoadDocs\Input.txt' WITH (ROWTERMINATOR = '\n')

INSERT INTO #TempPI
SELECT [id] , [name] , [time] , [url_id]      
FROM Server.Database.DBO.table1 a WITH (NOLOCK)
WHERE  exists (Select * from #TempBrowser b  where a.ID=b.BrowserId)

If I use File column, the query hangs.

Regards, Kanu

Answer 1

You are doing a local join over a huge table, which is tremendously slow. Adding a large makes it even slower, to the point that it times out. You need to redesign your whole logic so that you retrieve only the needed rows from the 1B table, ie. force the filtering to occur remotely. This is not a simple query rewrite, I think you really need to go back to the drawing board and review your whole ETL process, and perhaps use a tool that is actually designed to handle such process, like SSIS (as a side note, SSIS flows that involve a custom W3C common-log-format parser are all to common and many had rolled their own from logparser.exe->csv->rolap cube... but I digress). While you're at it, also stop using deprecated types, like IMAGE, and use the appropriate VARBINARY(MAX).