table with varcahar(max)

  • Gurus,

    there is a table which is very slow to query , it has only 3 months data, logid is PK, indexed and datecreated is the TS.

    It has a column called error with varchar(max) and saves the error msg which is slowing the table down.

    Will partioning this table improve the performance with varchar(max) ?

    What else can be done to make it faster?


  • How many rows? How large is the average error? max?

    When you say slow to query/slowing the table down, do you mean select queries? Insert queries? Both? You don't have updates, do you?

    Do you ever query on LogID? If not, is there any real use for a primary key here? Is the primary key clustered?

    A more useful clustered index would probably be on the TS column. You could make clustered PK on TS and then LogID so that it serves queries more usefully and still provides a PK.

    Partitioning could improve performance if every query uses the partitioning key (i.e., you only search within the partition period -- which, with so short a range of data, would probably be by month). It could more likely help with maintenance if there really is a lot of data and you use it to archive or truncate partitions for old months.

    Proper indexing may solve the problem. Obviously, preventing/fixing the errors is a priority.

    But if indexing doesn't solve it, and the volume of errors remains high, you may find that a relational database isn't necessarily the best way to store error logs.

  • Thanks for the quick response

    3409177 rows, ofcourse only way we query is using logid ,

    Is it normal to create IX on timestamp, never heard of that ?

    since error is varchar(max) , cannot be indexed.

    so no point partioning since error has varchar(max) or will it help?

    as you said selects, inserts, updates as well happen.


  • Is the primary key on LogID clustered?

    Are inserts slow? Updates? Selects? All? Does slow mean seconds/minutes/hours? For how many rows in a transaction?

    If the transaction rate is high, indexes are wrong, etc., you may have locking/blocking issues.

    I'm hoping/assuming when you say timestamp you're talking about a datetime or datetime2 type, correct? Or is it actually timestamp (rowversion) datatype?

    It's extremely common to query by, and therefore index on, datetime columns.

    Is LogId determined by the application rather than SQL Server? What is the datatype? If not determined by application, how do you know what LogID to query for later?

    What do you update on an error log? I would expect an error log to be an immutable record of the error event.

    I definitely wouldn't partition on error even if I could. There may still be a case for partitioning by timestamp if a datetime/datetime2.

    • This reply was modified 2 months, 1 week ago by  ratbak.
  • Thanks for the quick responses on Friday

    Yes pkey clustered on logid


    Time stamp is datetime , not null , Ok to index on timestamp?

  • Datecreated is this format, may be need to create a new computed column from this ?

    2023-07-31 02:00:15.470

  • only way I can do is select min(logid), createdate from tablename

    once I get logid , I use it, for delete . as i said Only logid is indexed ( pley , clusterd) on the table

  • IMHO, take the time to force the MAX to be out of row and default the error to an empty string to prevent the pointer from being "expansive".  Have max datatypes that might fit in-row creates what I call "Trapped Short Rows" and they will slow everything down except single row lookups.

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

  • sqlguy80 wrote:

    3409177 rows, of course only way we query is using logid ,

    If, as you say, you are querying by a single, unique PK value, the lookup should be quick unless the query is being blocked by something.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

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