All geek questions in one place

Performance issue in single table select query

I have a table below

dbo.UserLogs

-------------------------------------
Id | UserId |Date | Name| P1 | Dirty
-------------------------------------

There may be several entries on userId [even in millions]

I have a clustered index in a Date column and very often query this table in time ranges. The Dirty column is not NULL and can only accept 0 or 1, so I have no indexes in Dirty

I have several million records in this table, and in one specific case in my application, I need to query this table to get all UserId that have at least one record marked dirty.

I tried this query - select a separate (UserId) from UserLogs, where Dirty = 1

I have only 10 million records, and it takes 10 minutes to do this, and I want it to run much faster than that.

[I can query this table in a date column in less than a minute.]

Any comments / suggestions are welcome.

my env 64bit, sybase15.0.3, linux

+3
source share
2 answers

Add an index containing both UserId and Dirty fields. Put UserId before Dirty in the index as it has more unique values.

+1
source

my suggestion would be to reduce the amount of data that needs to be requested when recording archiving to an archive table at suitable time intervals.

You can access all the records if you provide a consolidation view of the current and archived log data, but access to current logs will be significantly reduced.

+2

Source: https://habr.com/ru/post/1737097/

More articles:


All Articles