Latest value for all members at a given point in time

We have a fact table with a current balance record. For this balance rows are added to the table only when the balance has changed. This way we can reduce the volume in the fact table by not saving redundant information for every point in time that we want to analyze.

In SQL it is a simple task to get a view of the lastest fact records for a given point in time. SELECT f1.* FROM factTable f1 WHERE f1.timestamp = (SELECT MAX(f2.timestamp) FROM factTable f2 WHERE f2.timestamp <= givenPointInTime AND f2.balanceKey = f1.balanceKey). In other words, the given point in time directly determines which rows should be fetched from our fact table, one row for each balance record (balanceKey). This row will of course include several dimension attributes, which represent the point in time when the balance changed.

Now, we would like to reproduce this behaviour in our Analysis Services 2005 cube. Using a time dimension, we would like to be able to select a given point in time and have the cube return all rows previous to that time, one row for each balanceKey. Not only the rows matching the given point in time. How can this be done? For the other dimension attributes, they should behave in a normal way, such that for the returned rows, you can slice using any combination of them.

Any help or ideas are appreciated,
Lars

Assuming that you're using AS 2005 Enterprise Edition, you could try the "Last Nonempty Child" aggregation:

http://msdn2.microsoft.com/en-us/library/ms175356.aspx

>>

SQL Server 2005 Books Online

Defining Semiadditive Behavior

Semiadditive measures, which do not uniformly aggregate across all dimensions, are very common in many business scenarios. Every cube that is based on snapshots of balances over time exhibits this problem. You can find these snapshots in applications dealing with securities, account balances, budgeting, human resources, insurance policies and claims, and many other business domains.

...

LastNonEmpty

The member value is evaluated as the value of its last child along the time dimension that contains data.

>>

|||

Thanks Deepak. We are on an Enterprise Edition (pre-SP1) but we do not get the desired behaviour using the "Last Nonempty Child" aggregation. In fact, it doesn't seem to make any difference at all from using SUM. We have created an example containing a relational database with a simple fact table and an analysis services database built upon it with a measure called "Account Balance". If we browse the cube and add the measure and the Account dimension to the results pane, then filter based on the Time dimension and select the date 2005-12-31, Account number 3 should display the balance 400. Currently it does not.

The example (SQL script + XMLA script) can be found at: http://www.intellibis.se/pub/CumulativeExample.zip

Can anyone help us?

Regards,
Lars

sql

Last Two Rows

I have a company table that lists the company name,
fiscal year, and fees. I am trying to compare the last
two years without specifically setting the criteria
because I want the data to drive the comparison between
the two.
Adding to the complexity is the fact that a company can
have two rows with the same year and different fees so I
would need to sum the fees for the year first and then do
the computation to display the percent change from year
to year.
An example of the data would be:
Microsoft 2005 2000000
Microsoft 2004 1750000
Dell 2004 1200000
Dell 2003 1100000
HP 2004 500000
HP 2004 250000
HP 2003 600000
I would need the output to look like this:
Microsoft 14.29%
Dell 9.09%
HP 25.00%
Thanks.How does the percentage calcuates from ?
"daniel" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0c3001c54100$6fa29aa0$a401280a@.phx.gbl...
>I have a company table that lists the company name,
> fiscal year, and fees. I am trying to compare the last
> two years without specifically setting the criteria
> because I want the data to drive the comparison between
> the two.
> Adding to the complexity is the fact that a company can
> have two rows with the same year and different fees so I
> would need to sum the fees for the year first and then do
> the computation to display the percent change from year
> to year.
> An example of the data would be:
> Microsoft 2005 2000000
> Microsoft 2004 1750000
> Dell 2004 1200000
> Dell 2003 1100000
> HP 2004 500000
> HP 2004 250000
> HP 2003 600000
> I would need the output to look like this:
> Microsoft 14.29%
> Dell 9.09%
> HP 25.00%
> Thanks.|||the % change is calculated by subtracting the latest date
fee from last years fee. Then divide the difference by
last years fee.
So in MSFT's case subtract 2000000-1750000 = 250000 and
then divide 250000/1750000.
>--Original Message--
>How does the percentage calcuates from ?
>
>"daniel" <anonymous@.discussions.microsoft.com> schrieb
im Newsbeitrag
>news:0c3001c54100$6fa29aa0$a401280a@.phx.gbl...
I
do
>
>.
>|||Using the following table structure and data:
CREATE TABLE CompanyFees (
Company varchar(50) NOT NULL,
FiscalYear smallint NOT NULL,
Fees money NOT NULL
)
INSERT INTO CompanyFees VALUES ('Microsoft', 2005, 2000000)
INSERT INTO CompanyFees VALUES ('Microsoft', 2004, 1750000)
INSERT INTO CompanyFees VALUES ('Dell', 2004, 1200000)
INSERT INTO CompanyFees VALUES ('Dell', 2003, 1100000)
INSERT INTO CompanyFees VALUES ('HP', 2004, 500000)
INSERT INTO CompanyFees VALUES ('HP', 2004, 250000)
INSERT INTO CompanyFees VALUES ('HP', 2003, 600000)
Use this query:
SELECT a.Company, a.FiscalYear, a.SumOfFees as ThisYearFees,
b.SumOfFees as PreviousYearFees,
(1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
FROM (
SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
FROM CompanyFees GROUP BY Company, FiscalYear
) a INNER JOIN (
SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
FROM CompanyFees GROUP BY Company, FiscalYear
) b ON a.Company=b.Company AND a.FiscalYear=b.FiscalYear+1
Razvan|||What is the primary key? Please help us to help you by including DDL
with future posts.
Try:
SELECT company, yr,
(SELECT (SUM(T.fee)-SUM(fee))
/CAST(SUM(fee) AS REAL)*100
FROM YourTable
WHERE company = T.company
AND yr = T.yr - 1) AS pct
FROM YourTable AS T
WHERE yr =
(SELECT MAX(yr)
FROM YourTable
WHERE company = T.company)
GROUP BY company, yr
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
company varchar(25),
fiscal_year int,
fee int
)
go
insert into t values('Microsoft', 2005, 2000000)
insert into t values('Microsoft', 2004, 1750000)
insert into t values('Dell', 2004, 1200000)
insert into t values('Dell', 2003, 1100000)
insert into t values('HP', 2004, 500000)
insert into t values('HP', 2004, 250000)
insert into t values('HP', 2003, 600000)
insert into t values('YAHOO', 2005, 1600000)
go
select
a.company,
cast(max(((a.sum_fee * 100.00) / b.sum_fee) - 100.00) as decimal(5, 2)) as
percent_change
from
(
select
company,
fiscal_year,
sum(fee) as sum_fee
from
t
where
(select count(distinct c.fiscal_year) from t as c where c.company =
t.company and c.fiscal_year >= t.fiscal_year) <= 2
group by
company,
fiscal_year
) as a
inner join
(
select
company,
fiscal_year,
sum(fee) as sum_fee
from
t
where
(select count(distinct c.fiscal_year) from t as c where c.company =
t.company and c.fiscal_year >= t.fiscal_year) <= 2
group by
company,
fiscal_year
) as b
on a.company = b.company
and a.fiscal_year = b.fiscal_year + 1
group by
a.company
go
drop table t
go
AMB
"daniel" wrote:

> I have a company table that lists the company name,
> fiscal year, and fees. I am trying to compare the last
> two years without specifically setting the criteria
> because I want the data to drive the comparison between
> the two.
> Adding to the complexity is the fact that a company can
> have two rows with the same year and different fees so I
> would need to sum the fees for the year first and then do
> the computation to display the percent change from year
> to year.
> An example of the data would be:
> Microsoft 2005 2000000
> Microsoft 2004 1750000
> Dell 2004 1200000
> Dell 2003 1100000
> HP 2004 500000
> HP 2004 250000
> HP 2003 600000
> I would need the output to look like this:
> Microsoft 14.29%
> Dell 9.09%
> HP 25.00%
> Thanks.
>|||Thanks Razvan. I had to check for divide by zero but
other than that it works perfecty!
>--Original Message--
>Using the following table structure and data:
>CREATE TABLE CompanyFees (
> Company varchar(50) NOT NULL,
> FiscalYear smallint NOT NULL,
> Fees money NOT NULL
> )
>INSERT INTO CompanyFees VALUES ('Microsoft', 2005,
2000000)
>INSERT INTO CompanyFees VALUES ('Microsoft', 2004,
1750000)
>INSERT INTO CompanyFees VALUES ('Dell', 2004,
1200000)
>INSERT INTO CompanyFees VALUES ('Dell', 2003,
1100000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
500000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
250000)
>INSERT INTO CompanyFees VALUES ('HP', 2003,
600000)
>Use this query:
>SELECT a.Company, a.FiscalYear, a.SumOfFees as
ThisYearFees,
> b.SumOfFees as PreviousYearFees,
> (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>FROM (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) a INNER JOIN (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) b ON a.Company=b.Company AND
a.FiscalYear=b.FiscalYear+1
>Razvan
>.
>|||one question though. what does the 1. mean in the
Column: (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>--Original Message--
>Using the following table structure and data:
>CREATE TABLE CompanyFees (
> Company varchar(50) NOT NULL,
> FiscalYear smallint NOT NULL,
> Fees money NOT NULL
> )
>INSERT INTO CompanyFees VALUES ('Microsoft', 2005,
2000000)
>INSERT INTO CompanyFees VALUES ('Microsoft', 2004,
1750000)
>INSERT INTO CompanyFees VALUES ('Dell', 2004,
1200000)
>INSERT INTO CompanyFees VALUES ('Dell', 2003,
1100000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
500000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
250000)
>INSERT INTO CompanyFees VALUES ('HP', 2003,
600000)
>Use this query:
>SELECT a.Company, a.FiscalYear, a.SumOfFees as
ThisYearFees,
> b.SumOfFees as PreviousYearFees,
> (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>FROM (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) a INNER JOIN (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) b ON a.Company=b.Company AND
a.FiscalYear=b.FiscalYear+1
>Razvan
>.
>|||Making sure it is converted to a decimal instead of doing integer division
(which yields an integer, e.g. 45/100 = 0).
http://www.aspfaq.com/2483
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"daniel" <anonymous@.discussions.microsoft.com> wrote in message
news:025501c5410a$07c226d0$a501280a@.phx.gbl...
> one question though. what does the 1. mean in the
> Column: (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
> 2000000)
> 1750000)
> 1200000)
> 1100000)
> 500000)
> 250000)
> 600000)
> ThisYearFees,
> a.FiscalYear=b.FiscalYear+1|||Another approach:
SELECT Company,
100 * ( MAX( CASE seq WHEN 0 THEN Fees END ) /
MAX( CASE seq WHEN 1 THEN Fees END ) - 1 )
FROM ( SELECT c1.Company, c1.FiscalYear, SUM( c1.Fees ),
( SELECT COUNT( DISTINCT c2.FiscalYear )
FROM CompanyFees c2
WHERE c2.Company = c1.Company
AND c2.FiscalYear > c1.FiscalYear )
FROM CompanyFees c1
GROUP BY c1.Company, c1.FiscalYear
) D ( Company, FiscalYear, Fees, seq )
GROUP BY Company ;
Anith

Last record in 10,000,000 rows

Hey gang, I got a table that has about 10,000,000 row and I need a test of the last 10 rows. Isn't the statement: Select bottom 10 from table?Rows in a table don't have any order. You can order the result set any way you want. Take the TOP 10 with the order "upside down" and you are in business!

-PatP|||SELECT TOP 1 * from test order by id desc
OR
select * from TEST a
where 1>(select count(*) from TEST where ID>a.id)
-SS|||Do you have an ADD_ROW_TS or IDENTITY Column?|||Gentlemen, I dont beleive I've been clear on what I'm attempting to do. I have a table with 10,000,000 records. (which happend to reflect 6 months historical data) that I need to confirm it is there. I can do a 'select top 10 from table" to get the first 10 record of this new table. I need to see the last 10 to confirm the month,date,time etc... What I've come up with in the sql anaylser is
select * from dbo.INCOMING_TEMP
where RECORD_NUMBER > 10453700
according to my records the total number of rows at 10453747

It's running but taking all day to get there.|||I'm assuming RECORD_NUMBER is defined like

RECORD_NUMBER int IDENTITY(1,1) NOT NULL

Yes?

SELECT TOP 10 * FROM yourTable99 ORDER BY RECORD_NUMBER DESC

Will get you the official last 10 records...

If that's truly the case...

No?|||Thanks that works fine. I havent been my self since I got the damn cast on my foot. So bear with me. Not I get to compare radomn samples of the data before blowing the original table away.

Last non nulls from a set of rows

I'm in a situation where I can't alter how data is being collected, and it's
an extremely large set of information.
The table all of this data is being dumped into is a transaction style table
with heavy null propogation. The only "real data" out of the table is the
last not null value entered (defined by the record time). For example:
Record 1. 1/3/2006 3:00PM, ID2006, Null, Null, 1006, ABC.
Record 2. 1/3/2006 5:00 PM ID2006, 32, Null, Null, Null
Record 3. 1/3/2006 9:00 PM ID2006, Null, Null, 1007, Null
The result set I would want from the above data is:
ID2006, 32, Null, 1007, ABC
Or in English, The values for ID2006 that are not null in order of most
recent record time.
As an aside, I also can't depend on records being inserted sequentially by
date.
I know I can do this with a series of selects using the TOP keyword, but is
there a more elegant way to deal with this problem? The individual selects
are pretty expensive over such a large amount of data.
Many Thanks!Hi Justin Weinberg,
I don't know how useful this will be but, I thought it might be fun to
play around with it. Go ahead and copy and paste the script below. I
added a second 'ID' record to be sure it was rolling the data up and
grouping correctly.
SET nocount ON
SET ansi_warnings off /* we'll be eliminating NULL values with an
aggregate function. don't need to be reminded */
CREATE TABLE #tbl (
entrydate datetime NOT NULL,
rowid CHAR(6) NOT NULL,
f1 INT NULL,
f15 INT NULL,
f2 INT NULL,
f3 CHAR(4) NULL)
INSERT #tbl
VALUES('1/3/2006 3:00:00.000 PM',
'ID2006',
NULL,
NULL,
1006,
'ABC.')
INSERT #tbl
VALUES('1/3/2006 5:00:00.000 PM',
'ID2006',
32,
NULL,
NULL,
NULL)
INSERT #tbl
VALUES('1/3/2006 9:00:00.000 PM',
'ID2006',
NULL,
NULL,
1007,
NULL)
INSERT #tbl
VALUES('1/3/2006 10:00:00.000 PM',
'ID2008',
NULL,
NULL,
1006,
NULL)
INSERT #tbl
VALUES('1/3/2006 6:00:00.000 PM',
'ID2008',
NULL,
NULL,
NULL,
'ACO.')
INSERT #tbl
VALUES('1/3/2006 11:00:00.000 PM',
'ID2008',
1734,
NULL,
NULL,
NULL)
INSERT #tbl
VALUES('1/3/2006 11:00:00.000 PM',
'ID2008',
NULL,
12,
NULL,
NULL)
SELECT #tbl.rowid,
Max(#tbl.f1) AS maxf1,
Max(#tbl.f15) AS maxf15,
Max(#tbl.f2) AS maxf2,
Max(#tbl.f3) AS maxf3
FROM #tbl
LEFT JOIN (SELECT Max(entrydate) AS ed,
rowid
FROM #tbl
WHERE f1 IS NOT NULL
GROUP BY rowid) AS f1results
ON #tbl.rowid = f1results.rowid
AND #tbl.entrydate = f1results.ed
LEFT JOIN (SELECT Max(entrydate) AS ed,
rowid
FROM #tbl
WHERE f15 IS NOT NULL
GROUP BY rowid) AS f15results
ON #tbl.rowid = f15results.rowid
AND #tbl.entrydate = f15results.ed
LEFT JOIN (SELECT Max(entrydate) AS ed,
rowid
FROM #tbl
WHERE f2 IS NOT NULL
GROUP BY rowid) AS f2results
ON #tbl.rowid = f2results.rowid
AND #tbl.entrydate = f2results.ed
LEFT JOIN (SELECT Max(entrydate) AS ed,
rowid
FROM #tbl
WHERE f3 IS NOT NULL
GROUP BY rowid) AS f3results
ON #tbl.rowid = f3results.rowid
AND #tbl.entrydate = f3results.ed
GROUP BY #tbl.rowid
ORDER BY #tbl.rowid
DROP TABLE #tbl|||Justin,
Here is a solution likely to be faster:
create table T (
d datetime,
id char(6),
a int,
b int,
c int,
x char(3)
)
go
insert into T values('20060103 15:00:00','ID2006',null,null,1006,'ABC')
insert into T values('20060103 17:00:00','ID2006',32,null,null,null)
insert into T values('20060103 21:00:00','ID2006',null,null,1007,NULL)
go
select
id,
ltrim(substring(max(convert(char(17),d,1
12)+str(a)),18,12)) as a,
ltrim(substring(max(convert(char(17),d,1
12)+str(b)),18,12)) as b,
ltrim(substring(max(convert(char(17),d,1
12)+str(c)),18,12)) as c,
ltrim(substring(max(convert(char(17),d,1
12)+x),18,3)) as x
from T
group by id
go
It will be even faster if you can afford to make the following
changes:
create table T (
d datetime,
id char(6),
a int,
b int,
c int,
x char(3),
n as case when a+b+c+case when x is not null then 0 end is null then 1 end
)
go
create clustered index T_ci on T(id,n)
<same query>
Steve Kass
Drew University
Justin Weinberg wrote:

>I'm in a situation where I can't alter how data is being collected, and it'
s
>an extremely large set of information.
>The table all of this data is being dumped into is a transaction style tabl
e
>with heavy null propogation. The only "real data" out of the table is the
>last not null value entered (defined by the record time). For example:
>Record 1. 1/3/2006 3:00PM, ID2006, Null, Null, 1006, ABC.
>Record 2. 1/3/2006 5:00 PM ID2006, 32, Null, Null, Null
>Record 3. 1/3/2006 9:00 PM ID2006, Null, Null, 1007, Null
>The result set I would want from the above data is:
>ID2006, 32, Null, 1007, ABC
>Or in English, The values for ID2006 that are not null in order of most
>recent record time.
>As an aside, I also can't depend on records being inserted sequentially by
>date.
>I know I can do this with a series of selects using the TOP keyword, but is
>there a more elegant way to deal with this problem? The individual selects
>are pretty expensive over such a large amount of data.
>Many Thanks!
>
>
>|||Shorter and easier to maintain, to boot!
Now I know why nobody wants to work on my queries :-)

Last insert id

Hi

I am trying to import several master detail records from files to ms sql
server.
I have orders file and order_items file that has several rows for each
order.
If I insert programmatically these records how can find out which order ID
was the last inserted, so that I can attach the subsesquent row items to a
proper order.

I am quite new to ms sql server. I have used mysql a lot and there I could
use mysql_insert_id to find out the last autoincremented filed number.
I am looking for a similar method for ms sql server 2000.

TIA
George"George Hill" <ghill@.NOSPAM.com> wrote in message
news:LUf8b.5984$ZB4.5409@.reader1.news.jippii.net.. .
> Hi
> I am trying to import several master detail records from files to ms sql
> server.
> I have orders file and order_items file that has several rows for each
> order.
> If I insert programmatically these records how can find out which order ID
> was the last inserted, so that I can attach the subsesquent row items to a
> proper order.
> I am quite new to ms sql server. I have used mysql a lot and there I could
> use mysql_insert_id to find out the last autoincremented filed number.
> I am looking for a similar method for ms sql server 2000.
> TIA
> George

Assuming that you're using an IDENTITY column to generate the IDs, then the
scope_identity() function will give the last ID inserted. There are also
ident_current() and @.@.identity - see Books Online for an explanation - but
scope_identity() is probably the one you want.

Simon

Large table search performance

I have a large table with 300,000 rows and it will grow in the future
The table contains member profile details and it has many columns (about 70)
The stored procedure that do the search use many columns to search the results
That why the ‘Where’ is very long and the all operation take to many time, some time I get Time out exception form the SQL server.
I’ll be happy to get some ideas how to do this search.
I think maybe to divide the table to many tables by State.

Something interesting is that the operation takes long time for the first search and then the second and the third are much faster, and after a few min that I’m not do anything again the first time is very slow

70 columns in a table is probably too many. It is hard to say. But there are few options to consider:

1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table

2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also.

Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations.

The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload.

|||

What type of partition will give me the best performance vertical or horizontal?

In horizontal partition what is the best way to do it:

How mane tables?

Witch column on each table?

The query that do all the problems is the search query and it use 14 columns in the ‘WHERE’ statement, maybe it best to divide the table into 2 table that one will hold the 14 columns for the search query and the second table will hold the rest of the columns is this will give best performance?

Large table search performance

I have a large table with 300,000 rows and it will grow in the future
The table contains member profile details and it has many columns (about 70)
The stored procedure that do the search use many columns to search the results
That why the ‘Where’ is very long and the all operation take to many time, some time I get Time out exception form the SQL server.
I’ll be happy to get some ideas how to do this search.
I think maybe to divide the table to many tables by State.

Something interesting is that the operation takes long time for the first search and then the second and the third are much faster, and after a few min that I’m not do anything again the first time is very slow

70 columns in a table is probably too many. It is hard to say. But there are few options to consider:

1. You can partition the table vertically. For example, split the table vertically into two - one with the most frequently accessed columns and another with the infrequent ones. This will make each table smaller in terms of row size and queries will be faster than accessing a single wide table. This of course complicates the data modifications since you have to issue two DMLs as opposed to one but it depends on your application and nature of the table

2. You can use partitioning (horizontal). But it depends on the choice of your partitioning key whether it is part of every search criteria or at least one of the primary search conditions. You get some manageability benefit also.

Anyway, the approach depends on your needs. You can find whitepapers in MSDN that discuss partitioning for example. Please take a look at that also. You can leave the table as is also and try to optimize the queries by say adding more indexes or creating additional statistics or restricting the search columns. Each has it's pros and cons. For example, adding more indexes will slow down data modification operations.

The reason why the queries are faster after the first time is due to plan caching. Looks like you are generating lot of ad-hoc queries due to variable number of search conditions and this is expected. Of course, the plan might get removed from cache later due to memory pressure or lack of reusability. It is hard to tell without knowing the type of queries and the nature of the workload.

|||

What type of partition will give me the best performance vertical or horizontal?

In horizontal partition what is the best way to do it:

How mane tables?

Witch column on each table?

The query that do all the problems is the search query and it use 14 columns in the ‘WHERE’ statement, maybe it best to divide the table into 2 table that one will hold the 14 columns for the search query and the second table will hold the rest of the columns is this will give best performance?

Large table schema changes

Quick question:

Does SQL do table/schema changes "in place"?

I've got a large table (140+ million rows of very wide
data) that we want to change the schema on -- basically
to remove a number of the unused data elements that we
don't use.

Anyway, does anyone know if SQL will do an in-place
change, or if it will copy the table to a new table, thereby
increasing my space allocation needs? I'd effectively,
temporarily, need space for two tables while the change
is happening if it copies the table first. This is not good as
I do not have enough available space at the moment.

If you've got pointers to specific MS docs regarding
this issue, please let me have 'em.

Thanks in advance."Sgt. Sausage" <nobody@.nowhere.com> wrote in message
news:402d2d58$0$52159$a0465688@.nnrp.fuse.net...
> Quick question:
> Does SQL do table/schema changes "in place"?

It depends.

If you're adding a nullable column generally it'll do it in place.

If you're adding a non-nullable column generally it'll make a copy.

> I've got a large table (140+ million rows of very wide
> data) that we want to change the schema on -- basically
> to remove a number of the unused data elements that we
> don't use.

If you're dropping columns, I believe it will drop them in place.

> Anyway, does anyone know if SQL will do an in-place
> change, or if it will copy the table to a new table, thereby
> increasing my space allocation needs? I'd effectively,
> temporarily, need space for two tables while the change
> is happening if it copies the table first. This is not good as
> I do not have enough available space at the moment.
> If you've got pointers to specific MS docs regarding
> this issue, please let me have 'em.
> Thanks in advance.|||"Sgt. Sausage" <nobody@.nowhere.com> wrote in message
news:402d2d58$0$52159$a0465688@.nnrp.fuse.net...
> Quick question:
> Does SQL do table/schema changes "in place"?
> I've got a large table (140+ million rows of very wide
> data) that we want to change the schema on -- basically
> to remove a number of the unused data elements that we
> don't use.
> Anyway, does anyone know if SQL will do an in-place
> change, or if it will copy the table to a new table, thereby
> increasing my space allocation needs? I'd effectively,
> temporarily, need space for two tables while the change
> is happening if it copies the table first. This is not good as
> I do not have enough available space at the moment.
> If you've got pointers to specific MS docs regarding
> this issue, please let me have 'em.
> Thanks in advance.

You might want to check out this article by Kalen Delaney, which explains
ALTER TABLE in some detail:

http://www.sqlmag.com/Articles/Inde...ArticleID=40538

Simon

Large table management and Partitioned Views

Just trying to get feedback from people on their practical experience with
managing large tables (>250mil rows). Has anyone used Partitioned Views and
how do they work? Any performance advantage? If not partitioned views, is
there anything else one can do in a large enterprise environment to manage
large tables?
Hi,
from my experience, Partitioned Views will actually reduce IO read (physical
read and read-ahead read).
it depends on how you split your table and file group.
Leo
"LC" wrote:

> Just trying to get feedback from people on their practical experience with
> managing large tables (>250mil rows). Has anyone used Partitioned Views and
> how do they work? Any performance advantage? If not partitioned views, is
> there anything else one can do in a large enterprise environment to manage
> large tables?
|||Thanks Leo,
Is the reduction in IO reads due to the fact the tables are split
up/smaller? Are there any other benefits or caveats to moving to this type
of architecture?
"Leo Leong" wrote:
[vbcol=seagreen]
> Hi,
> from my experience, Partitioned Views will actually reduce IO read (physical
> read and read-ahead read).
> it depends on how you split your table and file group.
> Leo
> "LC" wrote:
|||before I implemented it, i did a testing in a test environment.
i split a table into 6 where each table is stored in separate data file.
the partition key for the table is TYPR, let say.
so, when i ran a query with "SET STATISTIC IO ON" to select record based on
TYPE = "A", i found that SQL Server will only read the data from the table
that stores TYPE = "A" only.
of course, when the table is split, data file that stores the child tables
is smaller. so, relatively, it will fasten the process of searching a record.
another benefit of partitioned view is you can do distributed database
environment. that means, those child tables can be stored in > 1 database
servers.
Leo
"LC" wrote:
[vbcol=seagreen]
> Thanks Leo,
> Is the reduction in IO reads due to the fact the tables are split
> up/smaller? Are there any other benefits or caveats to moving to this type
> of architecture?
> "Leo Leong" wrote:

Large table management and Partitioned Views

Just trying to get feedback from people on their practical experience with
managing large tables (>250mil rows). Has anyone used Partitioned Views and
how do they work? Any performance advantage? If not partitioned views, is
there anything else one can do in a large enterprise environment to manage
large tables?Hi,
from my experience, Partitioned Views will actually reduce IO read (physical
read and read-ahead read).
it depends on how you split your table and file group.
Leo
"LC" wrote:
> Just trying to get feedback from people on their practical experience with
> managing large tables (>250mil rows). Has anyone used Partitioned Views and
> how do they work? Any performance advantage? If not partitioned views, is
> there anything else one can do in a large enterprise environment to manage
> large tables?|||Thanks Leo,
Is the reduction in IO reads due to the fact the tables are split
up/smaller? Are there any other benefits or caveats to moving to this type
of architecture?
"Leo Leong" wrote:
> Hi,
> from my experience, Partitioned Views will actually reduce IO read (physical
> read and read-ahead read).
> it depends on how you split your table and file group.
> Leo
> "LC" wrote:
> > Just trying to get feedback from people on their practical experience with
> > managing large tables (>250mil rows). Has anyone used Partitioned Views and
> > how do they work? Any performance advantage? If not partitioned views, is
> > there anything else one can do in a large enterprise environment to manage
> > large tables?|||before I implemented it, i did a testing in a test environment.
i split a table into 6 where each table is stored in separate data file.
the partition key for the table is TYPR, let say.
so, when i ran a query with "SET STATISTIC IO ON" to select record based on
TYPE = "A", i found that SQL Server will only read the data from the table
that stores TYPE = "A" only.
of course, when the table is split, data file that stores the child tables
is smaller. so, relatively, it will fasten the process of searching a record.
another benefit of partitioned view is you can do distributed database
environment. that means, those child tables can be stored in > 1 database
servers.
Leo
"LC" wrote:
> Thanks Leo,
> Is the reduction in IO reads due to the fact the tables are split
> up/smaller? Are there any other benefits or caveats to moving to this type
> of architecture?
> "Leo Leong" wrote:
> > Hi,
> >
> > from my experience, Partitioned Views will actually reduce IO read (physical
> > read and read-ahead read).
> > it depends on how you split your table and file group.
> >
> > Leo
> >
> > "LC" wrote:
> >
> > > Just trying to get feedback from people on their practical experience with
> > > managing large tables (>250mil rows). Has anyone used Partitioned Views and
> > > how do they work? Any performance advantage? If not partitioned views, is
> > > there anything else one can do in a large enterprise environment to manage
> > > large tables?

Large table management and Partitioned Views

Just trying to get feedback from people on their practical experience with
managing large tables (>250mil rows). Has anyone used Partitioned Views and
how do they work? Any performance advantage? If not partitioned views, is
there anything else one can do in a large enterprise environment to manage
large tables?Hi,
from my experience, Partitioned Views will actually reduce IO read (physical
read and read-ahead read).
it depends on how you split your table and file group.
Leo
"LC" wrote:

> Just trying to get feedback from people on their practical experience with
> managing large tables (>250mil rows). Has anyone used Partitioned Views a
nd
> how do they work? Any performance advantage? If not partitioned views, i
s
> there anything else one can do in a large enterprise environment to manage
> large tables?|||Thanks Leo,
Is the reduction in IO reads due to the fact the tables are split
up/smaller? Are there any other benefits or caveats to moving to this type
of architecture?
"Leo Leong" wrote:
[vbcol=seagreen]
> Hi,
> from my experience, Partitioned Views will actually reduce IO read (physic
al
> read and read-ahead read).
> it depends on how you split your table and file group.
> Leo
> "LC" wrote:
>|||before I implemented it, i did a testing in a test environment.
i split a table into 6 where each table is stored in separate data file.
the partition key for the table is TYPR, let say.
so, when i ran a query with "SET STATISTIC IO ON" to select record based on
TYPE = "A", i found that SQL Server will only read the data from the table
that stores TYPE = "A" only.
of course, when the table is split, data file that stores the child tables
is smaller. so, relatively, it will fasten the process of searching a record
.
another benefit of partitioned view is you can do distributed database
environment. that means, those child tables can be stored in > 1 database
servers.
Leo
"LC" wrote:
[vbcol=seagreen]
> Thanks Leo,
> Is the reduction in IO reads due to the fact the tables are split
> up/smaller? Are there any other benefits or caveats to moving to this typ
e
> of architecture?
> "Leo Leong" wrote:
>

Large Table & Merge

I added a large table to our merge pub, 3.4 million rows. Snapshot ran
rather fast, merge agents pushed rows out to 7 subscribers quickly. All
looking ok. Begin testing. Inserts at subscriber not being replicated. Look
at merge agents, change profile to Verbose. They are all displaying 0
inserts, 100 updates, 0 deletes, 0 conflicts.
Run profiler at subscriber and see the following being executed by merge
agent:
exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
add to merge?
Any help much appreciated.
Thanks.
Chris
Yes it is. Basically this is the option to minimize network traffic by using
additional storage at the publisher. The publisher tracks where each row has
come from and has to tag the msmerge_contents table with this info.
However, I think you need to use the conflict viewer to understand what has
happened to your inserts.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E058091C-50EB-4D09-8758-2F6A6E7D9785@.microsoft.com...
> I added a large table to our merge pub, 3.4 million rows. Snapshot ran
> rather fast, merge agents pushed rows out to 7 subscribers quickly. All
> looking ok. Begin testing. Inserts at subscriber not being replicated.
> Look
> at merge agents, change profile to Verbose. They are all displaying 0
> inserts, 100 updates, 0 deletes, 0 conflicts.
> Run profiler at subscriber and see the following being executed by merge
> agent:
> exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
> 0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
> Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
> add to merge?
> Any help much appreciated.
> Thanks.
> Chris

Large table -- split into smaller ones?

Hi All,
I have to import about 26 million rows, each row about 1Kb in size into
our sql server. This data will be READONLY and might be updated every
quarter.
I am yet to import it and do some performance testing.
what should be the best approach to handling this much data...
should I
* have one large table?
OR
*Horizontally partition data into multiple tables?
thanks for your time
GKIt's generally best to implement a single table unless you have a
compelling reason to do otherwise. A single large table with
appropriate indexes often performs quite well without the additional
administrative complexity of partitioning.
The main advantages of horizontal partitioning are related to admin
tasks like data loads, bulk deletes and index creates. For example,
you'll need about 30GB of free space to build a clustered index on your
table but only a fraction of that amount if partitioned. On the other
hand, if you can load data sorted in clustered index sequence, you can
load with the index in place and forego the index create entirely.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have to import about 26 million rows, each row about 1Kb in size
into
> our sql server. This data will be READONLY and might be updated every
> quarter.
> I am yet to import it and do some performance testing.
> what should be the best approach to handling this much data...
> should I
> * have one large table?
> OR
> *Horizontally partition data into multiple tables?
> thanks for your time
> GK
>
>|||Thanks Dan,
I understand your point about administrative overheads, but I guess
they are much reduced if I use Partitioned views in SQL server 2000. Would
you advise using that?
As far as loading the data in clustered index sequene is concerned, are you
advising me to, say load data sorted on the clustered index column(s)?
Excuse me if this question is stupid but how do I load with index in place?
Also I didn't understand why it takes so much space to create index for a
large table and less space if the table were to be split up. Does index
creation need thatmuch space just while building the index and the finally
created index take lesser space?
Thank you for your efforts.
GK
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> It's generally best to implement a single table unless you have a
> compelling reason to do otherwise. A single large table with
> appropriate indexes often performs quite well without the additional
> administrative complexity of partitioning.
> The main advantages of horizontal partitioning are related to admin
> tasks like data loads, bulk deletes and index creates. For example,
> you'll need about 30GB of free space to build a clustered index on your
> table but only a fraction of that amount if partitioned. On the other
> hand, if you can load data sorted in clustered index sequence, you can
> load with the index in place and forego the index create entirely.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> > I have to import about 26 million rows, each row about 1Kb in size
> into
> > our sql server. This data will be READONLY and might be updated every
> > quarter.
> > I am yet to import it and do some performance testing.
> > what should be the best approach to handling this much data...
> > should I
> > * have one large table?
> > OR
> > *Horizontally partition data into multiple tables?
> >
> > thanks for your time
> > GK
> >
> >
> >
>|||Just found this good msdn article on partioning data.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/PartitionsInDW.htm
and it says...(although they are talking about a data warehouse...mine will
not be a data warehouse exactly but it wont be an OLTP either. It'll be a
readonly table with quarterly updates)
<quote>
Query Speed
Query speed should not be considered a reason to partition the data
warehouse relational database. Query performance is similar for partitioned
and non-partitioned fact tables. When the partitioned database is properly
designed, the relational engine will include in a query plan only the
partition(s) necessary to resolve that query. For example, if the database
is partitioned by month and a query is conditioned on January 2000, the
query plan will include only the partition for January 2000. The resulting
query will perform well against the partitioned table, about the same as
against a properly indexed combined table with a clustered index on the
partitioning key.
</quote>
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000. Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned, are
you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index for a
> large table and less space if the table were to be split up. Does index
> creation need thatmuch space just while building the index and the finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on your
> > table but only a fraction of that amount if partitioned. On the other
> > hand, if you can load data sorted in clustered index sequence, you can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in size
> > into
> > > our sql server. This data will be READONLY and might be updated every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
It depends on the problem you are trying to solve. The article you
referenced in your other post does a pretty good job of pointing out the
pros and cons. If your primary concern is query performance, then PVs
aren't the answer. Personally, wouldn't change my design to accommodate
partitioning unless the additional complexity is more than offset by the
benefits of load speed and maintenance. IMHO, your quarterly data load
probably doesn't justify the use of PVs.
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
Just create the table and its clustered index. If you then load (bulk
insert) data in sequence by the clustered index, load performance will
be good and you won't need to create the clustered index afterward.
Non-clustered indexes can be created after the load.
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
The space requirement to build the clustered index is a consideration
with large tables. You need free space of about 120% of the original
table size. This is because the entire table is rebuilt during the
create so space for old and new data is needed plus some sort work
space. After the create, space for the old data is released and the end
result is that slightly more space than the original heap is allocated
to accommodate the non-leaf nodes of the clustered index.
With multiple smaller tables, you can build each clustered index
separately. Consequently, you'll only need free space to accommodate
120% of the largest table in the lot.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I
guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on
your
> > table but only a fraction of that amount if partitioned. On the
other
> > hand, if you can load data sorted in clustered index sequence, you
can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in
size
> > into
> > > our sql server. This data will be READONLY and might be updated
every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||That was very helpful.
Thanks Dan.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OdauywrqDHA.2568@.TK2MSFTNGP09.phx.gbl...
> > I understand your point about administrative overheads, but I guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> It depends on the problem you are trying to solve. The article you
> referenced in your other post does a pretty good job of pointing out the
> pros and cons. If your primary concern is query performance, then PVs
> aren't the answer. Personally, wouldn't change my design to accommodate
> partitioning unless the additional complexity is more than offset by the
> benefits of load speed and maintenance. IMHO, your quarterly data load
> probably doesn't justify the use of PVs.
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> Just create the table and its clustered index. If you then load (bulk
> insert) data in sequence by the clustered index, load performance will
> be good and you won't need to create the clustered index afterward.
> Non-clustered indexes can be created after the load.
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> The space requirement to build the clustered index is a consideration
> with large tables. You need free space of about 120% of the original
> table size. This is because the entire table is rebuilt during the
> create so space for old and new data is needed plus some sort work
> space. After the create, space for the old data is released and the end
> result is that slightly more space than the original heap is allocated
> to accommodate the non-leaf nodes of the clustered index.
> With multiple smaller tables, you can build each clustered index
> separately. Consequently, you'll only need free space to accommodate
> 120% of the largest table in the lot.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Thanks Dan,
> > I understand your point about administrative overheads, but I
> guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> >
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> >
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> >
> > Thank you for your efforts.
> > GK
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > > It's generally best to implement a single table unless you have a
> > > compelling reason to do otherwise. A single large table with
> > > appropriate indexes often performs quite well without the additional
> > > administrative complexity of partitioning.
> > >
> > > The main advantages of horizontal partitioning are related to admin
> > > tasks like data loads, bulk deletes and index creates. For example,
> > > you'll need about 30GB of free space to build a clustered index on
> your
> > > table but only a fraction of that amount if partitioned. On the
> other
> > > hand, if you can load data sorted in clustered index sequence, you
> can
> > > load with the index in place and forego the index create entirely.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > > Hi All,
> > > > I have to import about 26 million rows, each row about 1Kb in
> size
> > > into
> > > > our sql server. This data will be READONLY and might be updated
> every
> > > > quarter.
> > > > I am yet to import it and do some performance testing.
> > > > what should be the best approach to handling this much data...
> > > > should I
> > > > * have one large table?
> > > > OR
> > > > *Horizontally partition data into multiple tables?
> > > >
> > > > thanks for your time
> > > > GK
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Large number of rows.

Hi all,

A select query returns around 1 million rows. The column in the WHERE condition is indexed. This query takes nearly 1 minute for returning the all the records. Is this normal ?

Does the number of records returned affect the performance inspite of the indexing ?

Thanks,

DBLearner

The index simply allows it to find the data it has to retrieve quickly. It then has to:

load that actual record data in from disk. Depending upon how the records are split across datapages this could be a lot of disk access. Then if you have any sorting or grouping/aggregating on the result it has to do this before it can start passing the results back. After that it has to transfer the data that you are retrieving across the link (shared memory if you are running on the server across the network if not). If you are retrieving say 20 bytes per record (quite small: a couple of ints, a bit of text and a real number can be this size or larger easily) then for a million records that is 20 megabytes.|||

The major 'chokepoints' will be the quality of the index vs. the WHERE clause criteria, amount of server memory, other activity, CPU power, and disk 'arrangement'. (Having the TempDb database on a dedicated array or LUN, for example.)

You might benefit from some tuning and optimization and perhaps that will increase the query responsiveness. But you definitely need to get some benchmarks in place.

Here is some information about Performance Audits, Monitoring, and Tuning:

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

Large number of rows issue

Hi ,

There is a table with the following structure

_
Date-Time of Operation | Message | details | Reason | Username | IP | MAC-Address
_

A user can fire query based on some condition on columns. The maximum no of rows that should be returned are 1 billion.

By default the data returned to the view is sorted on Date-Time. Further user can sort the data in the view on Message, details , reason , username , IP , Mac-Address.

Suppose i have a scenerio where the user first gets the 1 billion records, then some records are inserted in the database.Then user sort on IP column. Now for differnt sorting if i again fire the query new data will be obtained. Thus i will get different result.

One way is to maintain the data in my memory. But the number of records are huge,so this is not a good solution. So what should i do to get the disconnected data without actually getting everything in the memory.

Regards,
Sunil

One way would be to have a stored procedure to fire this information into another table, then have a view of that table. Hence you are in control of the "loading" of that data for reporting purposes.|||

Hi,

I had a similar case in my company.

The solution for the case was creating a second instance on a different server which keeps denormalized data partitioned according to a date field. And loading the most recent partition into memory.

Also the query should have to use the date field since it will use that criteria in the index.

The OLTP database server was using 4GB of RAM on the other hand this second instance was using 12 GB of RAM only for maintaining such reporting processes.

Perhaps you may create covering index on the table. But this will increase the size of data.

Eralper

Large number of records in MSMerge_GenHistory

My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
can't get it to go down.
One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.
I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.
What can I do?
Thank You
Steve
One additional thing.
I noticed that most all of the records in msmerge_genhistory have a pubid of
NULL. A few have a with what looks like a valid guid. Are these records the
problem and can I simply delete them?
Thanks
"Steve" wrote:

> My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
> can't get it to go down.
> One of my Merge publications accidentally had Subscription expire in XX days
> set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
> and 1.9 Million records before I caught the error. I have corrected the
> Parameter and have been able to get my msmerge_contents down to 28 K rows. I
> can't get MsMerge_GenHistory to come down.
> I don't want to set every publication that I have to reinitialize. The
> publication that had the expiration days parameter problem is a remote
> subscriber with a laptop in the field and I don't have access to it on a
> daily basis. It is causing me problems with other publications with
> subscribers getting unable to process GenHistory messages. I have all of my
> remote subscribers set with a QueryTimeout of 4000.
> What can I do?
>
> Thank You
> Steve
|||What will happen if I run this with the Reinitialize Subscribers set to
False? I am trying to not reinitialize all of my subscribers.
I have found that most of my genhistory records, the pubid field is NULL.
Are these the problems and can I simply delete these?
I have a second question about a recommended max number of publications. I
have a database with 40 different publications . Each publication has 138
articles. Between all of these articles, there are in the neighborhgood of 1
million total records published. Some publications have multiple subscribers,
most publications have a single subscriber with all columns published and
static row filters. Most of these subscribers are remote users traveling. Is
this too publications? This topology has saved us from a system wide
reinitialize a couple of times when a particular subscriber has trouble or
simply will not sync in a reasonable timeframe (we have most of our
publications set to expire in 20 days).
Thanks for the help!
Steve
"Paul Ibison" wrote:

> Steve,
> have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
> manual cleanup of the metedata.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Large insert, how to switch off transactions?

Hello, I've a database with 8 million rows on my development pc and I've got
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:

> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>

Large FullText tables - slow queries

Hi,

I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.

To query the complete index is fast, for example:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

JOIN Pages ITP ON ITP.PageID = CT.[Key]

JOIN Feeds ITF ON ITP.IPID = ITF.IPID

JOIN Buyers ITB ON ITB.IBID = ITF.IBID

WHERE ITB.ID IN (1342,246)

These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).

One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:

'{ID:1342}' + [Body]

That sounds like a very good idea. I could then change the 2nd query above to be:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT

That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.

If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!

Many thanks for any advice.

Simon

I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||

Hi I am not sure if this can help:

35 Mio. I am looking forward to encounter more soon.

try:

Make sure to use the noiseword concept of FT, get rid of the noice first.

Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.

Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)

Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only

Try to Query also against other fields (not fulltext indexed)

Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!

.

*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!

Of course database needs field AutoId as Clusterd index.

8GB Memory / 64-bit version of SQL

Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)

How many keywords per record do you have, how long are they in average?

How big is the Textfile?

What is the CPU?

Thomas

Large FullText tables - slow queries

Hi,

I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.

To query the complete index is fast, for example:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

JOIN Pages ITP ON ITP.PageID = CT.[Key]

JOIN Feeds ITF ON ITP.IPID = ITF.IPID

JOIN Buyers ITB ON ITB.IBID = ITF.IBID

WHERE ITB.ID IN (1342,246)

These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).

One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:

'{ID:1342}' + [Body]

That sounds like a very good idea. I could then change the 2nd query above to be:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT

That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.

If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!

Many thanks for any advice.

Simon

I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||

Hi I am not sure if this can help:

35 Mio. I am looking forward to encounter more soon.

try:

Make sure to use the noiseword concept of FT, get rid of the noice first.

Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.

Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)

Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only

Try to Query also against other fields (not fulltext indexed)

Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!

.

*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!

Of course database needs field AutoId as Clusterd index.

8GB Memory / 64-bit version of SQL

Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)

How many keywords per record do you have, how long are they in average?

How big is the Textfile?

What is the CPU?

Thomas

Large delete stmt - 2nd question

Publisher has delete stmt executed against transactionally replicated table.
270K rows deleted in 1 stmt.
1 Subscriber connected with T1
How best to replicate all the 270K simpleton delete calls to
sp_MSdel_tablename at subscriber through distr agent? What parameter
adjustments to make?
Changed -CommitBatchSize to 1000, doesn't seem to increase speed much...
Thanks,
Chris
Set HistoryVerboseLevel to 0, CommitBatchSize to 1000 and
CommitBatchThreshold 10000
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DED73E09-DA75-444C-8976-96FF475B9567@.microsoft.com...
> Publisher has delete stmt executed against transactionally replicated
table.
> 270K rows deleted in 1 stmt.
> 1 Subscriber connected with T1
> How best to replicate all the 270K simpleton delete calls to
> sp_MSdel_tablename at subscriber through distr agent? What parameter
> adjustments to make?
> Changed -CommitBatchSize to 1000, doesn't seem to increase speed much...
> Thanks,
> Chris