Post reply

Retrieving an Image field takes too much of time

  • December 25, 2011 at 5:36 am

    #99573

    I have a table with the below structure

    [blob_id] [uniqueidentifier] NOT NULL,

    [file_data] [image] NULL,

    [file_path] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

    [bytes] [varchar](10) COLLATE Latin1_General_CI_AS NULL,

    [created_date] [datetime] NULL,

    [Archive_Date] [datetime] NULL

    and I have a query written as below:

    Select B.*,getdate() ArchiveDate from Blobs B,Attachments A,Purchase_Orders P where B.Blob_id =A.blob_id and

    A.Purchase_Order_Id =P.Purchase_Order_id and P.Picked_up_date<=DateAdd(mm,-36,GetDate()) and

    P.is_pickedup=1

    The result set for the above query contains nearly 118543 records.

    Actually this query is to get the above data and insert into an archive table.

    The above requirement through the import wizard takes more than an hour to complete.

    Could someone suggest if theres a better way to get a quicker output.

    Note: When I try to give select B.Blob_id from ...., i get the result set in no time.

    Also the delete from Blobs_Archive (118543 records) takes more than 20 mts.

    Thanks in advance.

    Regards,

    K. Sripriya

  • December 25, 2011 at 5:50 am

    #1425417

    First of all a Caution statement.

    Caution:

    ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    It looks like the images in the database are heavier than 1 MB. It’s recommended to use FILESTREAM instead.

    Per BOL:

    In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

    •Objects that are being stored are, on average, larger than 1 MB.

    •Fast read access is important.

    •You are developing applications that use a middle tier for application logic.

    For More: http://msdn.microsoft.com/en-us/library/bb933993.aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply