2

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

migrated from stackoverflow.com Feb 14 '12 at 14:46

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

3

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

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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