Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

[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')

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

share|improve this question

migrated from Feb 14 '12 at 14:46

This question came from our site for professional and enthusiast programmers.

up vote 3 down vote accepted

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).

share|improve this answer
Yes I guess that is the issue. I have to think a work around for this. Cannot help about the image field as it is already present in the database and have to use it. – kanu Feb 13 '12 at 22:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.