create partially index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

create partially index

Author

Topic

Goulasch
Starting Member

3 Posts

Posted - 2010-07-23 : 09:46:24

Hello,

I like to create an filtered index to get
"all stored different customer-id´s with their latest stored date (also a col)"
in an log-file

As a result of a query I exspect to get only the latest record (current date) for every customer.

The log-table contains millions of entries for every customer and a
select * where ...
without an index doesn´t make sense (performance reason).

Any idears ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-23 : 10:42:50

Need more information. Table design, query, current indexes at least.

You using SQL 2008?

--
Gail Shaw
SQL Server MVP

Goulasch
Starting Member

3 Posts

Posted - 2010-07-23 : 11:07:32

quote:
[i]You using SQL 2008?

yes

example:
cust-id timestamp............inf1.inf2
--------------------------------------------------
33091 20100723 12:00:56 xyz 12jnun
33090 20100723 12:00:56 juhn cccccc
33091 20100723 10:00:33 plok dfssdfsfs
20333 20100629 16:45:10 pokf asdfadsfdafd
44908 20100501 01:00:19 olkm dsadsdsa
33090 20100710 18:53:59 dasff asfddfas

result:
33091 20100723 12:00:56 xyz 12jnun
33090 20100723 12:00:56 uhn cccccc
20333 20100629 16:45:10 pokf asdfadsfdafd
44908 20100501 01:00:19 olkm dsadsdsa

It´s not neccessary to get back the cust-id in any order (nor the timesstamp)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-23 : 11:27:42

So you want the cust-id along with one (latest?) timestamp and associated inf1 and inf2?

This isn't a filtered index, it's a reasonably trivial query. If my above assumption is correct...

SELECT [cust-id], timestamp, inf1, inf2
FROM
(SELECT [cust-id], timestamp, inf1, inf2, ROW_NUMBER() OVER (Partition By [cust-id] Order By timestamp DESC) as RowNo
FROM someTable) sub
WHERE RowNo = 1

--
Gail Shaw
SQL Server MVP

Goulasch
Starting Member

3 Posts

Posted - 2010-07-23 : 11:38:04

the select-clause will run every 5 sec. from 10 different clients;
the table contain millions of records.

Because of performance issue :
why can´t we create an index with only 1 record per cust-id ?

As an example:
create INDEX log_idx
ON cust-id, timestamp DESC
WHERE RowNo=1
?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-23 : 16:10:27

Because it doesn't satisfy the requirements for a filtered index. RowNo is not a column in the table. It's a derived column.

From BoL:

quote:
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

An index on cust-id, timestamp should help out here.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-07-24 : 02:25:00

"the select-clause will run every 5 sec. from 10 different clients;"

If the query is frequent, and performance (with indexes, carefully tuned) is not fast enough then you could store the Last order details for each Customer (in a new table) using a trigger on your log table. It would be important that the number of queries is much higher than the number of inserts into the log table that cause the trigger to fire

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources