Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question
add comment

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

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

1 Answer

up vote 2 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
add comment

Your Answer

 
discard

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.