MS SQL/.NET Performance

[news]

I use MS SQL and .Net on a dedicated server (1GB), but the speed of db
access is very slow.

Are there any methods/software to improve speed of access to db?

Will more memory help?

 

[Greg Burns]

Are you using SQLClient provider?

I would investigate your database design/indexes before adding hardware.

Greg

 

[Lucas Tam]

I use MS SQL and .Net on a dedicated server (1GB), but the speed of db
access is very slow.

Are there any methods/software to improve speed of access to db?

Will more memory help?

Here are some things to look at:

-Add Indexes
-Optimize Queries
-Cache data into it's own table or into a .NET application variable
-More Memory
-Faster/More Disks (RAID array?)
-Faster CPU
-Build a database cluster

The 1st two options usually speed up performance significantly.

 

[Scott Allen]

The design, the indexes, and the queries are usually dominate
performance optimizations in the database.

I did an article recently:

SQL Performance Tuning using Indexes
http://odetocode.com/Articles/237.aspx

HTH,

 

[Trevor Oakley]

Thank you for all these suggestions; I have done most of them such as
indexing, using caches and memory

My indexes are complicated with 300 thousand word referencing from part
descriptions. To avoid duplicates a new word is checked and then a
secondary index record is built. This means millions of db accesses for
index creation - only done once per update for the whole website.

I think the solution is more fundamental than .NET programming and
connected to DBMS and limits of MS SQL (eg verus DB2, ORACLE).

One solution is to avoid Web updates and run a job direct against the
database which is much quicker. But I dont have server access (apart
from via the WWW), so I cant run a task (eg a BAT job) on the server.

I am interested to know of any hosts offering DB2 or ORACLE as a
standard package. I know ORACLE has powerful facilities but the cost is
higher.

 

[Lucas Tam]

My indexes are complicated with 300 thousand word referencing from part
descriptions. To avoid duplicates a new word is checked and then a
secondary index record is built. This means millions of db accesses for
index creation - only done once per update for the whole website.

I think the solution is more fundamental than .NET programming and
connected to DBMS and limits of MS SQL (eg verus DB2, ORACLE).

Have you considered upgrading your disk subsystem or adding a secondary
database server?

Often DB2 or Oracle seem faster than SQL server because DB2 and Oracle tend
to run on larger machines...

 

[Trevor Oakley]

I will have to look at the systems aspect more, a second machine really
means web hosting myself.

I worked with DB2/ORACLE when I was in corporations and I know the DBA's
were always looking at systems performance.

 

[Trevor Oakley]

I found one big improvement: space. I had a lot of temp tables created
by searches. They took up a lot of space, and when I deleted them the db
access was much quicker. The update words program went from 3 hours to 3
mins. I assume MS SQL struggles with scratch pad areas or memory on
larger dbs.

It makes me think MS SQL cant handle large dbs under my current system