Hide a column (value) in a subtotal of a matrix?

Hello,
I am trying to hide (or possible show a calculated value in a subtotal)
a value in a matrix. My dataset returns something in this format.
RowHeader1, RowHeader2, ColumnName, ColumnType, Amount
1, 1, Total, Amount, 100
1, 1, Total2, Amount, 0
1, 1, Variance, Percent, 1.00
1, 2, Total, Amount, 50
1, 2, Total2, Amount, 55
1, 2, Variance, Percent, .10
I have row groups on RowHeader1 and RowHeader2. Also, I have a Column
Group on ColumnName and I have SUM(Fields!Amount.Value) in the Data
cell. The Matrix looks something like this:
Total Total2 Variance
1 1 100 0 100%
2 50 55 10%
TOTAL 150 55 110%
What I would like to do is either have the correct value in the
SubTotal field for the Variance (which I don't think is possible) or
just hide it. I tried to use InScope() as a start but I have been
getting nowhere.
Any help would be greatly appreciated.
Thanks,
AbeIs there a way to know if you are in the Subtotal Row or not?
Thanks|||Apparently you looked already at the InScope function. If you have multiple
row/column groupings you need to make sure that your conditional expression
considers all cases. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abe" <abe@.flonet.com> wrote in message
news:1106156667.460735.143790@.c13g2000cwb.googlegroups.com...
> Is there a way to know if you are in the Subtotal Row or not?
> Thanks
>|||Thanks a lot for your help, I really appreciate it.

Hi I would like to import 1,000.00 price in DB - type??

Hi I would like to import into DB prices in format 1,000.00

I am using type money (mssql2004) but it doesnt let mi import this format.

Any ides. thx rek

Try DECIMAL(10,2)|||

Hi thank you for answer but it -

Error converting data type nvarchar to numeric.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

|||

You'll need to convert the value first:

my_decimal_value = Decimal.Parse(my_nvarchar_value);

|||

thats sucks : I am using

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

UpdateCommand="Update HSRSeason SET Price=@.Price > </asp:SqlDataSource>

Do I have to then work it through aspx.cs?

|||

Use this instead:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

UpdateCommand="Update HSRSeason SET Price=CONVERT(DECIMAL(10,2), @.Price)"> </asp:SqlDataSource>

|||

UpdateCommand="Update HSRSeason SET
Price=CONVERT(DECIMAL(10,2), @.Price)

And same error (it works without ",")

Error converting data type nvarchar to numeric.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Error converting data type nvarchar to numeric.]

|||

I'll bet you have the literal commas in the numbers. Use this instead:

UpdateCommand="Update HSRSeason SET
Price=CONVERT(DECIMAL(10,2), REPLACE(@.Price, ',', ''))

to replace the commas with a zero-length string before parsing.

Hope this helps...

|||

you were right. I am sorry I didnt tell you. Because I will make a person to write it with commas so it will be more secure to input prices.

What do you think about this idea? I will valide it so it will have to have a 00,000.00 format. But not there yet :-)

|||

The REPLACE function above will work regardless of whether there are commas in the string. If they are there, they will be removed, and if not the string will be passed as is.

Let me know if that works.

|||

the replace function (both) works fine as you suggested -

CONVERT(DECIMAL(10,2), REPLACE(@.Price, ',', ''))

I didnt know I can write functions in aspx. What kind of coding is this? It suprised me!

|||The REPLACE() function is actually an SQL function. I'm not sure if it's specific to T-SQL or if it is ANSI standard, but it's a very handy tool.|||lovely thx-for helpingsql

hhmmss Time format in SQL Server - How?

Hi !!

I am having difficulties working with SQL Server SmallDataTime...

I use TransactionDate as smalldatetime.. Now for reporting purpose my client needs

date as ccyymmdd format and
time as hhmmss format

I am able to get date in ccyymmdd using
CONVERT(varchar(8), @.fDate, 112) )

How do I get time in hhmmss format .. its little urgent pls help...

I tried using DATEPART(hh, @.fDate) DATEPART(mm, @.fDate) DATEPART(ss, @.fDate)
how do I make one string of hhmmss..

Is there a better way?How about this:

replace(Convert (varchar(8),GetDate(), 108),':','')

Is it going to work for you?
First, you get hh:mm:ss and then you replace : with empty string.|||

select convert(varchar, getdate(), 108)

hth|||Waow.. Replace convert combo worked perfectly fine..

Thx....|||Another problem I am running into is this

if I write SELECT LEN('2000') I get answer = 4

However I write function for this I get wrong answer... I always get 1

Here is my function.. can anyone tell whats wrong?
CREATE FUNCTION fnc_GetLen (@.str nvarchar)
RETURNS nvarchar(10)
AS
BEGIN

DECLARE @.L nvarchar(10)

SET @.L = LEN(@.str)
RETURN (@.L)
END|||Not so sure what the purpose of this function since it does exactly what LEN does.

Anyway, the problem is you did not give a length on the input string. Try change to this:

CREATE FUNCTION fnc_GetLen (@.str nvarchar(500))

|||I just need to find length.. once I find length I do processing inside that function.. like appending 0's or spaces etc..

I will give it a try.. thanks|||also your return type should be int and not nvarchar.

RETURNS int

hth|||Return nvarchar worked perfectly fine. No Problem at all...

I am using padding function and its giving me nightmare when I use blank space (white space) as padding char. Can you see whats wrong?

CREATE FUNCTION fnc_AddPadding ( @.text nvarchar(30), @.padChar char(1), @.maxLen int, @.padType char(1))
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.resultText nvarchar(50)
SET @.resultText = ''

-- Left Padding
IF @.padType = 'L'
BEGIN
SET @.resultText =RIGHT( REPLICATE ( @.padChar, @.maxLen) + @.text, @.maxLen )
END

-- Right Padding
IF @.padType = 'R'
BEGIN
SET @.resultText = LEFT( @.text + REPLICATE ( @.padChar, @.maxLen) , @.maxLen )
END

-- No Padding
IF @.padType not in ( 'R', 'L')
BEGIN
SET @.resultText = NULL
END

RETURN @.resultText
END

For every other padding char it works ok.. but when I use white space as pad char it is bad...

My Return Type nvarchar(50) or nvarchar(512) and mann..... instead of 5 - 10 white space I end up with 50 or 512 white space...

HH:MM:SS Format

In MS reporting, how would I convert seconds into HH:MMS format?

Hello,

Try this as the expression for your textbox:

=Floor(Fields!TotalSeconds.Value / 3600) & ":"

& Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60 & ":"

& Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60

Hope this helps.

Jarret

|||

Since you get to use VB in reports, try the following:

Code Snippet

Format$(Timeserial(0, 0, seconds), "hh:mm:ss")

Larry

|||Thanks Larry, this worked!|||This method worked also. Thanks Jarret.

Hexadecimal String

Hi,

I want to convert a UNIQUEIDENTIFIER to a VARCHAR, but I don't want to convert it to a varchar with this format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx; I want to convert it to a varchar representing my uniqueindentifier with a hexadecimal number 0xnnnnnnn...
Anybody knows if this is possible, or will I have to do it manipulating each character in the varchar

Thanks,

FedericoDepending on exactly what you want, there are two ways to get there... THe simple answer is to use:SELECT Cast(myGUID AS VARBINARY(20)) -- 20 is overkill, better safe than sorryIf that isn't sufficient for your needs, then a small UDF will allow you as much flexibility as you know how to code!

-PatP

Hexadecimal format .mdf file

How to find the hexadecimal format of .mdf or .ldf file (sql database file)

Quote:

Originally Posted by Avishkaar

How to find the hexadecimal format of .mdf or .ldf file (sql database file)

I'm sorry, I don't understand your question, but this might be better suited in one of the SQL forums?

Heterogeneous queries error

This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C3F654.828ED3B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I've a dynamic query that works on linked server but it keep giving me = this error "Heterogeneous queries require the ANSI_NULLS and = ANSI_WARNINGS options to be set for the connection. This ensures = consistent query semantics. Enable these options and then reissue your = query."
I've set all the ANSI_DEFAULT on but still doesn't work. Can someone = help me out?
Thx.
----= --
CREATE PROCEDURE a_sp_check_record
@.serverDB_name VARCHAR(100),
@.storeID VARCHAR(2),
@.cutoff_date VARCHAR(30)
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ANSI_DEFAULTS ON
DECLARE @.sqlString NVARCHAR(4000)
DECLARE @.recCount INT
-- exist in remote but missing in HQ
SET @.sqlString =3D'SELECT DISTINCT po_number, invoice_number, = received_date, upc ' +'FROM ' + @.serverDB_name + = '.dbo.receive_order_transactions REMO '
+'WHERE received_date>''' + @.cutoff_date + ''' AND '
+ 'store_id=3D' + @.storeID + ' '
+ 'AND NOT EXISTS (SELECT * FROM = [tm341].dbo.receive_order_transactions HQ '
+ 'WHERE HQ.po_number=3DREMO.po_number AND = HQ.invoice_number=3DREMO.invoice_number AND '
+ 'HQ.received_date=3DREMO.received_date AND = HQ.store_id=3DREMO.store_id AND ' + 'HQ.store_id=3D' + @.storeID + ' AND = REMO.store_id=3D' + @.storeID +')'
EXEC SP_EXECUTESQL @.sqlString
IF @.@.rowCOUNT =3D 0
print 'remote have all HQ rcv'
ELSE
print 'remote have missing HQ rcv'
GO
--=_NextPart_000_002E_01C3F654.828ED3B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

I've a dynamic query that works on linked server but = it keep giving me this error "Heterogeneous = queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the = connection. This ensures consistent query semantics. Enable these options and then = reissue your query."

I've set all the ANSI_DEFAULT on but still doesn't = work. Can someone help me out?

Thx.


CREATE PROCEDURE a_sp_check_record
@.serverDB_name VARCHAR(100),@.storeID &= nbsp; VARCHAR(2),@.cutoff_date VARCHAR(30)

AS

SET ANSI_NULLS ONSET ANSI_WARNINGS ONSET = ANSI_DEFAULTS ON
DECLARE @.sqlString NVARCHAR(4000)DECLARE @.recCount INT
-- exist in remote but missing in = HQSET @.sqlString =3D'SELECT DISTINCT po_number, invoice_number, = received_date, upc ' +'FROM ' + @.serverDB_name + '.dbo.receive_order_transactions REMO ' &n= bsp; +'WHERE received_date>''' + @.cutoff_date + ''' AND ' &n= bsp; + 'store_id=3D' + @.storeID + ' ' &n= bsp; + 'AND NOT EXISTS (SELECT * FROM [tm341].dbo.receive_order_transactions HQ ' &n= bsp; + 'WHERE HQ.po_number=3DREMO.po_number AND = HQ.invoice_number=3DREMO.invoice_number AND ' &n= bsp; + &n= bsp; 'HQ.received_date=3DREMO.received_date AND HQ.store_id=3DREMO.store_id = AND ' &n= bsp; + &n= bsp; 'HQ.store_id=3D' + @.storeID + ' AND REMO.store_id=3D' + @.storeID = +')'

EXEC SP_EXECUTESQL @.sqlStringIF = @.@.rowCOUNT =3D 0 print 'remote have all HQ rcv'ELSE = print 'remote have missing HQ rcv'GO

--=_NextPart_000_002E_01C3F654.828ED3B0--You the ANSI_NULLS & ANSI_WARNINGS settings within the stored procedure.
Instead apply these settings while creating the procedure itself.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE usp_test(...
GO
--
Anith

HelpColumnar Report with 3 Groupings?

Hi

I have sales data that needs to be grouped by Company, State and
Date. I would like to format the report as shown in image.

I am using Crystal Reports XI. I am new to Crystal Reports and
would like to find out whether this report is possible using either
columnar format or cross-tab?

Many ThanksPost a sample of your table(s). I can't help without knowing what you have to start with.

I have a feeling you'll need a crosstab|||Hi

I have data in two tables - Sales and Dealers.

Sales
-------
DealerCode
WeekEnding
NumberSold

Dealers
-------
DealerCode
CompanyName
State

I have a command in Crystal as follows:

SELECT d.CompanyName, d.State, s.WeekEnding, s.NumberSold
FROM sales s
LEFT JOIN dealers d
ON s.DealerCode = d.DealerCode
AND s.WeekEnding BETWEEN '2006-04-01' AND '2006-04-30'

I would like to group the data returned by CompanyName, State and Date to get a Total of NumberSold.

I would like to format the report in columns (as in image attachment). I would also like to have a two week total and a week-on-week figure.

If you need any further info, let me know. Thanks for your help!|||Look for cross tab reports in help file

Help: why SQL 2005 is slow?

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C7025D.34339BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with = tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The = machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID = configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, = Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in = varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the = followings:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
No. SQL Statement = Time
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 minutes
----= -- --
2 SELECT COUNT(*) FROM Items 13 = minutes
----= -- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 = hours 50 minutes
----= -- --
4 SELECT COUNT(*) FROM Items 20 = minutes
----= -- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 minutes
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
The first 2 statements were run without primary key in the table. The = statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the = performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such simple = statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such big = table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0008_01C7025D.34339BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

During the weekend, I heard that SQL = Server 2000 is very slow with tables containing rows over millions. So I did some = tests with our new database on SQL Server 2005 Standard Edition. The machine = is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ = P4 CPU. There is no RAID configuration. It has drives: C: with 11GB free space = and D: with 169GB free space. SQL 2005 was installed on D: drive. =

The size of the database is 10GB = containing 47 tables. The main table, Items, contains 96 columns and 30 millions = rows.All columns are in varchar data type, and 2 of them are in varchar(2500). = There is no any index setup in the table neither.

The time consumed for some SQL = statements with this table are the followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb= sp; SQL Statement &nbs= p;  = ; = &= nbsp; Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs= p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * FROM = Items WHERE item_num=3D'10029' 16 minutes---= -- -- 2 SELECT COUNT(*) FROM Items &n= bsp; &nb= sp; 13 minutes---= -- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) = 10 hours 50 minutes---= -- -- 4 SELECT COUNT(*) FROM Items &n= bsp; &nb= sp; 20 minutes---= -- -- 5 SELECT * FROM Items = WHERE item_num=3D'10029' 18 minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run without = primary key in the table. The statements #4 and #5 were run after "rid" was added as = primary key.

This is the first time I work with a = database in such size. But the performance of SQL Server 2005 surprised = me.

Would you please tell me:1. Is such = performance normal with such number of rows? 2. Do I have to do something to = improve the performance with such simple statement when the number of rows>1 = million or >10 millions? 3. Is SQL Server 2005 the right one to handle a = database with such big table, or should I consider DB2 9 or Oracle = 10g?

Thank you

Hongbo
--=_NextPart_000_0008_01C7025D.34339BC0--This is a multi-part message in MIME format.
--=_NextPart_000_003D_01C70287.CDE22960
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try to find out your system having any IO or memory botleneck, if =possible post SHOWPLAN_ALL result as well
vinu
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The =machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, =Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 minutes
=----=-- --
2 SELECT COUNT(*) FROM Items 13 =minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 =hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items 20 =minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. The =statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the =performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such big =table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_003D_01C70287.CDE22960
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try to find out your system having any =IO or memory botleneck, if possible post SHOWPLAN_ALL result as well
vinu
"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that SQL =Server 2000 is very slow with tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and =single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: with =11GB free space and D: with 169GB free space. SQL 2005 was installed on =D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 millions rows.All columns are in varchar data type, and 2 of them are in varchar(2500). There is no any index setup in the table =neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p;  =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * FROM =Items WHERE item_num=3D'10029' 16 =minutes---=-- -- 2 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM Items =WHERE item_num=3D'10029' 18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after "rid" was =added as primary key.

This is the first time I work with a =database in such size. But the performance of SQL Server 2005 surprised =me.

Would you please tell me:1. Is =such performance normal with such number of rows? 2. Do I have to do =something to improve the performance with such simple statement when the number =of rows>1 million or >10 millions? 3. Is SQL Server 2005 the =right one to handle a database with such big table, or should I consider DB2 9 =or Oracle 10g?

Thank you

Hongbo

---

--=_NextPart_000_003D_01C70287.CDE22960--|||Hongbo wrote:
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main table,
> Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ======================================================> No. SQL
> Statement Time
> ========================================= =========> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
> --
> 2 SELECT COUNT(*) FROM Items 13
> minutes
> ----
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
> --
> 4 SELECT COUNT(*) FROM Items 20
> minutes
> ----
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ======================================================> The first 2 statements were run without primary key in the table. The
> statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such big
> table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Do I read this right, you have 30 million rows an no indexes? I would
think about adding a few to help you out. SQL 2005 can handle that with
proper design and hardware.
I believe there is a tremendous amount of paging going on there to
accommodate your full table scans and on a single drive, hence your poor
performance.
--
Ryan Sanders
http://ryanlsanders.blogspot.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C703E5.BD4BE410
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I take =any other measures to improve the performance?
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The =machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, =Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 minutes
=----=-- --
2 SELECT COUNT(*) FROM Items 13 =minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 =hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items 20 =minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. The =statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the =performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such big =table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0015_01C703E5.BD4BE410
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Vinu and Ryan,
Thank you for your =responses.
I know that some measures could improve =the performance.
My question is: Are the results =normal in SQL Server 2005 before I take any other measures to improve the performance?
"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that SQL =Server 2000 is very slow with tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and =single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: with =11GB free space and D: with 169GB free space. SQL 2005 was installed on =D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 millions rows.All columns are in varchar data type, and 2 of them are in varchar(2500). There is no any index setup in the table =neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p;  =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * FROM =Items WHERE item_num=3D'10029' 16 =minutes---=-- -- 2 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM Items =WHERE item_num=3D'10029' 18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after "rid" was =added as primary key.

This is the first time I work with a =database in such size. But the performance of SQL Server 2005 surprised =me.

Would you please tell me:1. Is =such performance normal with such number of rows? 2. Do I have to do =something to improve the performance with such simple statement when the number =of rows>1 million or >10 millions? 3. Is SQL Server 2005 the =right one to handle a database with such big table, or should I consider DB2 9 =or Oracle 10g?

Thank you

Hongbo

---

--=_NextPart_000_0015_01C703E5.BD4BE410--|||This is a multi-part message in MIME format.
--=_NextPart_000_0404_01C706F9.89BE1E30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:ecxxF5ABHHA.5060@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I =take any other measures to improve the performance?
(please refrain from posting in HTML, most newsreaders don't like =it...)
Anyway...that performance seems about right given your setup.
First, if this database is important, get something with RAID, =otherwise a single disk failure will kill you.
Also, move your LOG file to a different physical drive (or set of =drives). This will increase your update/insert/delete statements.
Finally, Step 3... what is RID? Does it match anything in the outside =world? If not, you may prefer to find a natural key. Perhaps item_num.
If you're selecting against item_num, you will want that as at least =one of your indices.
What other indices you will want will depend greatly on how your =normally access your data.
Also, I would hope you're not using select * in production code.
Finally, 96 columns is a fairly wide database. is this normalized? =If not, why not? What's the average width of a road. You mentioned =varchar, but not what the average use is.
Personally, if the item_num is unique and a good candidate key, I'd =drop the rid column you added, make item_num my primary key and then go =from there.
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. The =machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main =table, Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 =minutes
=----=-- --
2 SELECT COUNT(*) FROM Items =13 minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 =hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items =20 minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 =minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. =The statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the =performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such =big table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0404_01C706F9.89BE1E30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
"Hongbo" =wrote in message news:ecxxF5ABHHA.5060=@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,

Thank you for your =responses.

I know that some measures could =improve the performance.

My question is: Are the results =normal in SQL Server 2005 before I take any other measures to improve the performance?


(please refrain from posting in HTML, =most newsreaders don't like it...)

Anyway...that performance seems =about right given your setup.

First, if this database is important, =get something with RAID, otherwise a single disk failure will kill you.

Also, move your LOG file to a =different physical drive (or set of drives). This will increase your =update/insert/delete statements.

Finally, Step 3... what is RID? =Does it match anything in the outside world? If not, you may prefer to =find a natural key. Perhaps item_num.

If you're selecting against item_num, =you will want that as at least one of your indices.

What other indices you will want will =depend greatly on how your normally access your data.


Also, I would hope you're not using =select * in production code.

Finally, 96 columns is a fairly wide database. is this normalized? If not, why not? =What's the average width of a road. You mentioned varchar, but not what the =average use is.

Personally, if the item_num is unique =and a good candidate key, I'd drop the rid column you added, make item_num my =primary key and then go from there.





"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that =SQL Server 2000 is very slow with tables containing rows over millions. So I =did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and =single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: =with 11GB free space and D: with 169GB free space. SQL 2005 was installed =on D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 millions rows.All columns are in varchar data type, and 2 of them are in varchar(2500). There is no any index setup in the table neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p;  =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * =FROM Items WHERE item_num=3D'10029' =16 =minutes---=-- -- 2 SELECT COUNT(*) FROM = =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) FROM = =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM Items =WHERE item_num=3D'10029' =18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after "rid" was =added as primary key.

This is the first time I work with =a database in such size. But the performance of SQL Server 2005 surprised me.

Would you please tell me:1. Is =such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such simple statement when =the number of rows>1 million or >10 millions? 3. Is SQL Server =2005 the right one to handle a database with such big table, or should I =consider DB2 9 or Oracle 10g?

Thank you

Hongbo

---

--=_NextPart_000_0404_01C706F9.89BE1E30--|||This is a multi-part message in MIME format.
--=_NextPart_000_0041_01C70705.B2737350
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi, Greg,
Thank you for your message.
Regardless what kind of measure should be taken to improve the =performance,
would you please tell whether the result I got is normal under the given =circumstance?
Thank you
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in =message news:OM6hzNyBHHA.4844@.TK2MSFTNGP02.phx.gbl...
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:ecxxF5ABHHA.5060@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I =take any other measures to improve the performance?
(please refrain from posting in HTML, most newsreaders don't like =it...)
Anyway...that performance seems about right given your setup.
First, if this database is important, get something with RAID, =otherwise a single disk failure will kill you.
Also, move your LOG file to a different physical drive (or set of =drives). This will increase your update/insert/delete statements.
Finally, Step 3... what is RID? Does it match anything in the =outside world? If not, you may prefer to find a natural key. Perhaps =item_num.
If you're selecting against item_num, you will want that as at least =one of your indices.
What other indices you will want will depend greatly on how your =normally access your data.
Also, I would hope you're not using select * in production code.
Finally, 96 columns is a fairly wide database. is this normalized? =If not, why not? What's the average width of a road. You mentioned =varchar, but not what the average use is.
Personally, if the item_num is unique and a good candidate key, I'd =drop the rid column you added, make item_num my primary key and then go =from there.
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow with =tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main =table, Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the =followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 =minutes
=----=-- --
2 SELECT COUNT(*) FROM Items = 13 minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) =10 hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items = 20 minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 =minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the table. =The statements #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But =the performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with such =simple statement when the number of rows>1 million or >10 millions? 3. Is SQL Server 2005 the right one to handle a database with such =big table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0041_01C70705.B2737350
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi, Greg,
Thank you for your =message.
Regardless what kind of measure should =be taken to improve the performance,
would you please tell whether the =result I got is normal under the given circumstance?
Thank you
"Greg D. Moore (Strider)" wrote in message news:OM6hzNyBHHA.4844=@.TK2MSFTNGP02.phx.gbl...

"Hongbo" =wrote in message news:ecxxF5ABHHA.5060=@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,

Thank you for your =responses.

I know that some measures could =improve the performance.

My question is: Are the =results normal in SQL Server 2005 before I take any other measures to improve the performance?


(please refrain from posting in =HTML, most newsreaders don't like it...)

Anyway...that performance seems =about right given your setup.

First, if this database is =important, get something with RAID, otherwise a single disk failure will kill you.

Also, move your LOG file to a =different physical drive (or set of drives). This will increase your update/insert/delete statements.

Finally, Step 3... what is =RID? Does it match anything in the outside world? If not, you may prefer to =find a natural key. Perhaps item_num.

If you're selecting against =item_num, you will want that as at least one of your indices.

What other indices you will want =will depend greatly on how your normally access your data.


Also, I would hope you're not using =select * in production code.

Finally, 96 columns is a fairly =wide database. is this normalized? If not, why not? =What's the average width of a road. You mentioned varchar, but not what =the average use is.

Personally, if the item_num is =unique and a good candidate key, I'd drop the rid column you added, make item_num =my primary key and then go from there.





"Hongbo" =wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard that =SQL Server 2000 is very slow with tables containing rows over millions. So I =did some tests with our new database on SQL Server 2005 Standard =Edition. The machine is Windows 2003 Standard Server box with 3.5 GB RAM =and single 3.0GHZ P4 CPU. There is no RAID configuration. It has drives: C: =with 11GB free space and D: with 169GB free space. SQL 2005 was =installed on D: drive.

The size of the database is 10GB =containing 47 tables. The main table, Items, contains 96 columns and 30 =millions rows.All columns are in varchar data type, and 2 of them are =in varchar(2500). There is no any index setup in the table neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p;  =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT * =FROM Items WHERE item_num=3D'10029' =16 =minutes---=-- -- 2 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE Items ADD rid INT PRIMARY KEY =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM =Items WHERE item_num=3D'10029' =18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run after ="rid" was added as primary key.

This is the first time I work =with a database in such size. But the performance of SQL Server 2005 surprised me.

Would you please tell me:1. =Is such performance normal with such number of rows? 2. Do I have to =do something to improve the performance with such simple statement =when the number of rows>1 million or >10 millions? 3. Is SQL =Server 2005 the right one to handle a database with such big table, or should =I consider DB2 9 or Oracle 10g?

Thank you

Hongbo

---

--=_NextPart_000_0041_01C70705.B2737350--|||Hongbo wrote:
> Hi Vinu and Ryan,
> Thank you for your responses.
> I know that some measures could improve the performance.
> My question is: Are the results normal in SQL Server 2005 before I take
> any other measures to improve the performance?
> "Hongbo" <hongbo@.goodoffices.com <mailto:hongbo@.goodoffices.com>>
> wrote in message news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main
> table, Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ======================================================> No. SQL
> Statement Time
> ========================================= =========> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
> --
> 2 SELECT COUNT(*) FROM Items
> 13 minutes
> ----
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
> --
> 4 SELECT COUNT(*) FROM Items
> 20 minutes
> ----
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ======================================================> The first 2 statements were run without primary key in the table.
> The statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such
> big table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Yes, without any index set up, this is normal performance. Even if you
move this index-less design over to DB2 or 10g, you will see the same
performance.|||This is a multi-part message in MIME format.
--=_NextPart_000_0610_01C70762.EED7AD90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:%23yeU24yBHHA.4292@.TK2MSFTNGP02.phx.gbl...
Hi, Greg,
Thank you for your message.
Regardless what kind of measure should be taken to improve the =performance,
would you please tell whether the result I got is normal under the =given circumstance?
Quite possibly yes.
Thank you
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in =message news:OM6hzNyBHHA.4844@.TK2MSFTNGP02.phx.gbl...
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:ecxxF5ABHHA.5060@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,
Thank you for your responses.
I know that some measures could improve the performance.
My question is: Are the results normal in SQL Server 2005 before I =take any other measures to improve the performance?
(please refrain from posting in HTML, most newsreaders don't like =it...)
Anyway...that performance seems about right given your setup.
First, if this database is important, get something with RAID, =otherwise a single disk failure will kill you.
Also, move your LOG file to a different physical drive (or set of =drives). This will increase your update/insert/delete statements.
Finally, Step 3... what is RID? Does it match anything in the =outside world? If not, you may prefer to find a natural key. Perhaps =item_num.
If you're selecting against item_num, you will want that as at =least one of your indices.
What other indices you will want will depend greatly on how your =normally access your data.
Also, I would hope you're not using select * in production code.
Finally, 96 columns is a fairly wide database. is this =normalized? If not, why not? What's the average width of a road. You =mentioned varchar, but not what the average use is.
Personally, if the item_num is unique and a good candidate key, =I'd drop the rid column you added, make item_num my primary key and then =go from there.
"Hongbo" <hongbo@.goodoffices.com> wrote in message =news:uKS%23AXoAHHA.3836@.TK2MSFTNGP02.phx.gbl...
Hi,
During the weekend, I heard that SQL Server 2000 is very slow =with tables containing rows over millions. So I did some tests with our new database on SQL Server 2005 Standard Edition. =The machine is Windows 2003 Standard Server box with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID =configuration. It has drives: C: with 11GB free space and D: with 169GB free space. SQL 2005 was installed on D: =drive.
The size of the database is 10GB containing 47 tables. The main =table, Items, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in =varchar(2500). There is no any index setup in the table neither.
The time consumed for some SQL statements with this table are =the followings:
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
No. SQL Statement = Time
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ==3D=3D=3D=3D=3D=3D=3D=3D=3D
1 SELECT * FROM Items WHERE item_num=3D'10029' 16 =minutes
=----=-- --
2 SELECT COUNT(*) FROM Items = 13 minutes
=----=-- --
3 ALTER TABLE Items ADD rid INT PRIMARY KEY IDENTITY(1,1) =10 hours 50 minutes
=----=-- --
4 SELECT COUNT(*) FROM Items = 20 minutes
=----=-- --
5 SELECT * FROM Items WHERE item_num=3D'10029' 18 =minutes
==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D
The first 2 statements were run without primary key in the =table. The statements #4 and #5 were run after "rid" was added as =primary key.
This is the first time I work with a database in such size. But =the performance of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows? 2. Do I have to do something to improve the performance with =such simple statement when the number of rows>1 million or >10 millions? =
3. Is SQL Server 2005 the right one to handle a database with =such big table, or should I consider DB2 9 or Oracle 10g?
Thank you
Hongbo
--=_NextPart_000_0610_01C70762.EED7AD90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
"Hongbo" =wrote in message news:%23yeU24yBHHA.=4292@.TK2MSFTNGP02.phx.gbl...
Hi, Greg,

Thank you for your =message.

Regardless what kind of measure =should be taken to improve the performance,
would you please tell whether the =result I got is normal under the given circumstance?


Quite possibly yes.



Thank you
"Greg D. Moore (Strider)" wrote in message news:OM6hzNyBHHA.4844=@.TK2MSFTNGP02.phx.gbl...

"Hongbo" =wrote in message news:ecxxF5ABHHA.5060=@.TK2MSFTNGP02.phx.gbl...
Hi Vinu and Ryan,

Thank you for your =responses.

I know that some measures could =improve the performance.

My question is: Are the =results normal in SQL Server 2005 before I take any other measures to improve the = performance?


(please refrain from posting in =HTML, most newsreaders don't like it...)

Anyway...that performance seems =about right given your setup.

First, if this database is =important, get something with RAID, otherwise a single disk failure will kill you.

Also, move your LOG file to a =different physical drive (or set of drives). This will increase your update/insert/delete statements.

Finally, Step 3... what is =RID? Does it match anything in the outside world? If not, you may prefer =to find a natural key. Perhaps item_num.

If you're selecting against =item_num, you will want that as at least one of your indices.

What other indices you will want =will depend greatly on how your normally access your data.


Also, I would hope you're not =using select * in production code.

Finally, 96 columns is a fairly =wide database. is this normalized? If not, why not? =What's the average width of a road. You mentioned varchar, but not =what the average use is.

Personally, if the item_num is =unique and a good candidate key, I'd drop the rid column you added, make =item_num my primary key and then go from there.





"Hongbo" wrote in message news:uKS%23AXoAHHA.=3836@.TK2MSFTNGP02.phx.gbl...
Hi,

During the weekend, I heard =that SQL Server 2000 is very slow with tables containing rows over millions. So =I did some tests with our new database on SQL Server 2005 Standard = Edition. The machine is Windows 2003 Standard Server box =with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID configuration. It =has drives: C: with 11GB free space and D: with 169GB free =space. SQL 2005 was installed on D: drive.

The size of the database is =10GB containing 47 tables. The main table, Items, contains 96 columns and 30 =millions rows.All columns are in varchar data type, and 2 of them are =in varchar(2500). There is no any index setup in the table neither.

The time consumed for some SQL =statements with this table are the =followings:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3DNo. &nb=sp; SQL =Statement &nbs=p;  =; = &=nbsp; =Time=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &nbs=p; =3D=3D=3D=3D=3D=3D=3D=3D=3D 1 SELECT =* FROM Items WHERE =item_num=3D'10029' 16 =minutes---=-- -- 2 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 13 =minutes---=-- -- 3 ALTER TABLE =Items ADD rid INT PRIMARY KEY = =IDENTITY(1,1) = 10 hours 50 =minutes---=-- -- 4 SELECT COUNT(*) =FROM =Items &n=bsp; &nb=sp; 20 =minutes---=-- -- 5 SELECT * FROM =Items WHERE =item_num=3D'10029' 18 =minutes=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D

The first 2 statements were run =without primary key in the table. The statements #4 and #5 were run =after "rid" was added as primary key.

This is the first time I work =with a database in such size. But the performance of SQL Server 2005 =surprised me.

Would you please tell me:1. =Is such performance normal with such number of rows? 2. Do I have to =do something to improve the performance with such simple statement =when the number of rows>1 million or >10 millions? 3. Is SQL =Server 2005 the right one to handle a database with such big table, or =should I consider DB2 9 or Oracle 10g?

Thank you

Hongbo

---

--=_NextPart_000_0610_01C70762.EED7AD90--