MY db is very slow - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

MY db is very slow

Author

Topic

jmgonzalez
Starting Member

3 Posts

Posted - 2003-11-09 : 07:29:32

hello
My problem is this

I have mount the sql server in cluster with raid 5, when I insert dates in the database tempdb is very fast (21.000 in 10 seg) and the process of sql run the 90 %,while if i insert dates in one database create for my is very slow (1250 in 10 seg) and the process of sql run only the 2%

Can you help me for performance my datebase

Thanks for all
url="mailto: jmgonzalez@telefonicab2b.com"]jmgonzalez@telefonicab2b.com[/url]

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-09 : 11:35:18

hi, what do you mean by "insert dates"? The performance of most SQL statements will vary across databases substantially, even on the same server, with factors like space available in the database, indexes, number of connected users, etc all affecting the speed of execution. Can you post the queries that are running slowly? Also some database structure and sample data would help.

Owais

Where there's a will, I want to be in it.

jmgonzalez
Starting Member

3 Posts

Posted - 2003-11-09 : 17:12:29

helo mohdowais the query is this:
CREATE TABLE xyz
(
col1 int PRIMARY KEY IDENTITY(1,1) NOT NULL,
col2 int NOT NULL DEFAULT 999,
col3 char(10) NOT NULL DEFAULT 'ABCDEFGHIJ'
)

GO

DECLARE @counter int
SELECT @counter=1
WHILE (@counter <= 100000)
BEGIN
INSERT xyz DEFAULT VALUES
SET @counter=@counter+1
END

By the way in other machine with a hard disk ide the query run very fast

Thanks

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-09 : 18:57:32

Compare initial sizes of "slow" and "fast" databases.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-10 : 01:35:34

Try this, the power of the CROSS JOIN!!


create table #foo
(
num int
)

CREATE TABLE #xyz
(
col1 int PRIMARY KEY IDENTITY(1,1) NOT NULL,
col2 int NOT NULL DEFAULT 999,
col3 char(10) NOT NULL DEFAULT 'ABCDEFGHIJ'
)


insert into #foo select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 0

insert into #xyz(col2, col3)
select 999, 'ABCDEFGHIJ' from #foo a cross join #foo b 
cross join #foo c cross join #foo d cross join #foo e

drop table #foo
--drop table #xyz

As Stoad mentioned, check the initial and "grow by" sizes of the databases and the transaction logs. While the database or transaction log file grows, all transactions have to wait. I think the Recovery model of the database might also have something to do with the speed of the inserts as well.

Owais

Where there's a will, I want to be in it.

jmgonzalez
Starting Member

3 Posts

Posted - 2003-11-10 : 10:45:34

Hello Owais

this query run very fast is fantastic

thaaaaaaaaaaaaaaanks for all

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-10 : 14:31:51

BTW the below will be not much slower than Owais' nice query:

BEGIN TRAN
DECLARE @counter int
SELECT @counter=1
WHILE (@counter <= 100000)
BEGIN
INSERT xyz DEFAULT VALUES
SET @counter=@counter+1
END
COMMIT TRAN

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-10 : 14:58:56

quote:
Originally posted by jmgonzalez

Hello Owais

this query run very fast is fantastic

thaaaaaaaaaaaaaaanks for all

You have somebody else to thank for that...ALL HAIL TO JEFF...ALL HAIL TO JEFF...JEFF...JEFF...JEFF...THE CROSS JOIN MAN!!!!

Owais

Where there's a will, I want to be in it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources