SQL Server Performance Forum – Threads Archive

granularity

I have tried to calculate the granularity of table with fields from A, B, C â€¦ n in some general formula. Don#%92t if this is right way to do it. This is my test script â€¦ The result is like this the : We have 2 distinct A values. To every distinct A value we 2.5 b values, and every distinct A,B combination contains 1.6 C values. In more general terms we have 2 customers that have placed 2.5 orders with 1.6 product in that order. Can somebody
with better sql know how tells me if simpler way to do this? Or I have a index on (A,B) is good to put in C in the index if only have granularity of 1.6 â€¦ Drop Table tempdb.dbo.granularity CREATE TABLE tempdb.dbo.granularity(
[A] float NOT NULL,
float NOT NULL,
[C] float NOT NULL,
–CONSTRAINT PK_granularity PRIMARY KEY CLUSTERED
–(
— [A] ASC,
— ASC,
— [C] ASC
–)
) truncate table tempdb.dbo.granularity
insert tempdb.dbo.granularity(A,B,C) values (1.0,1.0,1.0)
insert tempdb.dbo.granularity(A,B,C) values (1.0,1.0,2.0)
insert tempdb.dbo.granularity(A,B,C) values (1.0,1.0,3.0)
insert tempdb.dbo.granularity(A,B,C) values (1.0,1.0,4.0)
insert tempdb.dbo.granularity(A,B,C) values (2.0,1.0,1.0)
insert tempdb.dbo.granularity(A,B,C) values (2.0,2.0,1.0)
insert tempdb.dbo.granularity(A,B,C) values (2.0,3.0,1.0)
insert tempdb.dbo.granularity(A,B,C) values (2.0,4.0,1.0)
select count(distinct a) from tempdb.dbo.granularity
Go With granularity_count(_count) as
(
select count(distinct b) from tempdb.dbo.granularity group by a
)
select avg(cast(_count as float)) from granularity_count;
Go With granularity_count(_count) as
(
select count(distinct c) from tempdb.dbo.granularity group by a,b
)
select avg(cast(_count as float)) from granularity_count;

I suggest to use trigger to insert value on column B and C when u insert or update on column A and if youer search criteria is not on Column C then not need to put index on C as it will slow down Insert and Update operation.If you are using all three column in your search criteria then you can set cluster index with combination of all three column.

Just asking whether you have any performance issues with current approach? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Back from along holiday. You have misunderstood me totally. I want to calculate granularity,distribution of values in a table. Thetable sample table no real table itâ€™s the sql that are important. I will give a sample, fake one. I havephone book, and (Surname, First name, Town, Number, â€¦. n) index (Surname, Firstname)
Nilsson Kalle
Nilsson Petra
Johsson Sara
I have 2 uniquevalues domain surname, nilsson and johsson. The surname nilsson have 2 unique Firstname and Johsson surname have 1 unique firstname Sara.
I can tellabout my phone book, this has 2 unique surname names, any every unique surnamehave 1.5 First name). Then I get granularity of the indexâ€¦.
IX(CustomerNumber,OrderNumber, ProductNumber)
I want toknow following for example
Numbers of uniqueCustomers?
How manyOrders have very unique customers I general?
You have1235 customers, very customer places I general 3.7 orders with 1.1 product.
This tellme that this just with a IX(CustomerNumber, OrderNumber) will get very good hitrate. But
if.
I have 1235customers, very customer places I general 3.7 orders with 112.1 product.
I want totake the in the granularity when creating indexesâ€¦
Its a sample table….

]]>