Post reply

Tuning image datatype

  • April 11, 2011 at 4:20 am

    #238349

    I have ERP system and one query is select image datatype column

    and ı want to tune this query can ı do anythink about this.

    Query

    SELECT

    GLCDS.LOGICALREF, GLCDS.MODNR, GLCDS.GRPFILTER, GLCDS.VATRATE, GLCDS.ACCOUNTREF, GLCDS.CENTERREF, GLCDS.LINEEXP, GLCDS.CALCFORMULA, GLCDS.INDEXCODE, GLCDS.CAPIBLOCK_CREATEDBY, GLCDS.CAPIBLOCK_CREADEDDATE, GLCDS.CAPIBLOCK_CREATEDHOUR, GLCDS.CAPIBLOCK_CREATEDMIN, GLCDS.CAPIBLOCK_CREATEDSEC, GLCDS.CAPIBLOCK_MODIFIEDBY, GLCDS.CAPIBLOCK_MODIFIEDDATE, GLCDS.CAPIBLOCK_MODIFIEDHOUR, GLCDS.CAPIBLOCK_MODIFIEDMIN, GLCDS.CAPIBLOCK_MODIFIEDSEC, GLCDS.PREVALUE, GLCDS.PRDIFF, GLCDS.PROJECTREF, GLCDS.BRANCHNR, GLCDS.EFFECTIVECOST, GLCDS.SITEID, GLCDS.RECSTATUS, GLCDS.ORGLOGICREF

    FROM

    LG_002_ACCCODES GLCDS WITH(NOLOCK)

    WHERE

    (GLCDS.MODNR = 3)

    table script

    SELECT

    GLCDS.LOGICALREF, GLCDS.MODNR, GLCDS.GRPFILTER, GLCDS.VATRATE, GLCDS.ACCOUNTREF, GLCDS.CENTERREF, GLCDS.LINEEXP, GLCDS.CALCFORMULA, GLCDS.INDEXCODE, GLCDS.CAPIBLOCK_CREATEDBY, GLCDS.CAPIBLOCK_CREADEDDATE, GLCDS.CAPIBLOCK_CREATEDHOUR, GLCDS.CAPIBLOCK_CREATEDMIN, GLCDS.CAPIBLOCK_CREATEDSEC, GLCDS.CAPIBLOCK_MODIFIEDBY, GLCDS.CAPIBLOCK_MODIFIEDDATE, GLCDS.CAPIBLOCK_MODIFIEDHOUR, GLCDS.CAPIBLOCK_MODIFIEDMIN, GLCDS.CAPIBLOCK_MODIFIEDSEC, GLCDS.PREVALUE, GLCDS.PRDIFF, GLCDS.PROJECTREF, GLCDS.BRANCHNR, GLCDS.EFFECTIVECOST, GLCDS.SITEID, GLCDS.RECSTATUS, GLCDS.ORGLOGICREF

    FROM

    LG_002_ACCCODES GLCDS WITH(NOLOCK)

    WHERE

    (GLCDS.MODNR = 3)

  • April 11, 2011 at 4:21 am

    #1309727

    Table Script....

    CREATE TABLE [dbo].[LG_002_ACCCODES](

    [LOGICALREF] [int] NOT NULL,

    [MODNR] [smallint] NULL,

    [GRPFILTER] [image] NULL,

    [VATRATE] [float] NULL,

    [ACCOUNTREF] [int] NULL,

    [CENTERREF] [int] NULL,

    [LINEEXP] [varchar](31) NULL,

    [CALCFORMULA] [varchar](251) NULL,

    [INDEXCODE] [varchar](25) NULL,

    [CAPIBLOCK_CREATEDBY] [smallint] NULL,

    [CAPIBLOCK_CREADEDDATE] [datetime] NULL,

    [CAPIBLOCK_CREATEDHOUR] [smallint] NULL,

    [CAPIBLOCK_CREATEDMIN] [smallint] NULL,

    [CAPIBLOCK_CREATEDSEC] [smallint] NULL,

    [CAPIBLOCK_MODIFIEDBY] [smallint] NULL,

    [CAPIBLOCK_MODIFIEDDATE] [datetime] NULL,

    [CAPIBLOCK_MODIFIEDHOUR] [smallint] NULL,

    [CAPIBLOCK_MODIFIEDMIN] [smallint] NULL,

    [CAPIBLOCK_MODIFIEDSEC] [smallint] NULL,

    [PREVALUE] [smallint] NULL,

    [PRDIFF] [smallint] NULL,

    [PROJECTREF] [int] NULL,

    [BRANCHNR] [smallint] NULL,

    [RECSTATUS] [smallint] NULL,

    [ORGLOGICREF] [int] NULL,

    [EFFECTIVECOST] [smallint] NULL,

    [SITEID] [smallint] NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  • April 11, 2011 at 6:55 am

    #1309788

    Do you have an index on GLCDS.MODNR ?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 11, 2011 at 6:58 am

    #1309790

    Yes I have

  • April 12, 2011 at 8:26 am

    #1310416

    Any idea???

  • April 12, 2011 at 8:47 am

    #1310433

    Did you check the execution plan to make sure that the index is being used?

    Is there a performance difference when running this statement through SSMS and/or the client application?

    Is there a performance difference when running this statement directly on the server or through the network?

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

  • April 12, 2011 at 12:20 pm

    #1310593

    Sorry... I lost track of this one.

    Is this still a problem? If so, I've run out of ideas based on the current information provided. I believe we need more info according to the 2nd link in my signature line below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 15, 2011 at 3:56 pm

    #1312385

    When i remove image column in query it is use index but when i add image column it dosent use index it use clustered index

  • April 18, 2011 at 1:00 am

    #1312616

    ESAT ERKEC (4/15/2011)

    When i remove image column in query it is use index but when i add image column it dosent use index it use clustered index

    When you remove the image column, its using the Clustered Index... Is it doing a scan or seek?

    The image column cannot be part of a non-clustered index but only part of a clustered index and therefore it's most probably either doing a scan of the clustered index or a seek using your non clustered index joined with a lookup... Not sure what is happening... can you please post the execution plans? Also post also the non-clustered index definition.

    Regards
    Brian Ellul
    ----------------------------------------------------------------------------------
    Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
    - Albert Einstein -

Viewing 9 posts - 1 through 8 (of 8 total)

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