table with varcahar(max)

Question

table with varcahar(max)

sqlguy80

SSCrazy

Points: 2765
More actions
September 22, 2023 at 5:50 pm

#4292025

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?
Thanks

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

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

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.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 2

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.

ratbak Ten Centuries Points: 1358 More actions · Answer 3

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 1 year, 2 months ago by ratbak.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 4

Thanks for the quick responses on Friday

Yes pkey clustered on logid

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

sqlguy80 SSCrazy Points: 2765 More actions · Answer 5

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

2023-07-31 02:00:15.470

sqlguy80 SSCrazy Points: 2765 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".