Benchmarks and Performance on large databases

Benchmarks and Performance on large databases

Post by Infomatrix Ltd » Tue, 03 Feb 1998 04:00:00



We have a database with 2.5million+ records.
We are finding that returning queries is very slow.

Does anyone have some benchmark speeds for performance, showing what
difference more RAM makes, or different SQL server set ups etc.. ?

I've heard it is possible to put data into a DB with a comma delimited
file. Is this possible if so how?

We are currently adding records using single statements for each row, is
there a faster way?

I've seen people mention BCP, what is this?


In advance.. thankyou
Matt Pearson

 
 
 

Benchmarks and Performance on large databases

Post by Ben McEw » Wed, 04 Feb 1998 04:00:00




Quote:> We have a database with 2.5million+ records.
> We are finding that returning queries is very slow.

You need to do some performance tuning. I do this, and there's a number
of other regular posters here who do it on a regular basis. You can also
try doing this yourself- there's lots of information available on how to
go about this, but it's more of an art than a science.

Generally speaking, some queries will always take longer than others. If
you are retrieving every row in a 2.5M row table, there is no way to make
it go faster short of throwing faster hardware at the problem. However,
some queries, like range searches, aggregates and grouping queries, can
be tuned by examining both the query and the indexes on the table.

Simple queries (select ... where UniqueKey = <somevalue>) should be very
fast, due to the nature of balanced-tree indexes. You just need to be
sure you're not invalidating the search argument in the query, and that
you've defined an index that can actually help.

Quote:> Does anyone have some benchmark speeds for performance, showing what
> difference more RAM makes, or different SQL server set ups etc.. ?

If you haven't done some work with sp_configure, this is definitely the
first place to start. Get a good book (6.5 Unleashed works for me) and an
afternoon, and understand the basics of memory settings, procedure cache,
user connections, etc.

Quote:> I've heard it is possible to put data into a DB with a comma delimited
> file. Is this possible if so how?

Use BCP. This is a bulk copy utility available in the \mssql\binn
directory. It's a command line utility. For CSV files, some folks find it
easier to import the data to Excel or Access (to use the ISAM drivers)
then put it into SQL Server. This is an easy approach for a one time
deal, but BCP is the way to go for regular imports. Read up in the books
online.

Quote:> We are currently adding records using single statements for each row, is
> there a faster way?

Maybe. Depends on where the data comes from. If you're adding it from a
front end or some other manual source, the best way to get speed is to
batch your inserts:

begin tran
insert
insert
insert
insert
commit tran

Make sure you understand the ramifications of an insert failing inside
the transaction- a failure of the fifth insert undoes the work
of the four that came before it. Experiment with the number of inserts in
the batch.

Depending on concurrency, you may be able to get more speed on inserts by
defining your clustered index to distribute the insert location. Playing
with the fillfactor on a regular basis improves insert performance,
especially if there's lots of indexes on a table.

> I've seen people mention BCP, what is this?


> In advance.. thankyou
> Matt Pearson

--
Ben McEwan, President, Geist, LLC


 
 
 

1. Benchmarks Vs Simulation for evaluation of database performance

Quite a few benchmarks seem to be used for evaluating database performance.
Prominent among them are the Wisconsin Benchmark, OO1 Benchmark(Cattell and
Skeen) and of course the TPC-A and TPC-B benchmarks.

I was wondering if anybody has tried using simulation for database performance
evaluation?

Simulation seems to be a widely used tool in operating systems evaluation.  Is
there some reason it is not suitable for database system evaluation?

vijay

--
Vijay Raghavan, ROSE group | Your magic reunites, what customs sternly separate
Dept of Computer Sc, RPI   | all men shall be brothers,
Phone: (518)-276-4838      | whenever your gentle wings tarry

2. Delphi 2.0 C/S and Threads

3. Performance with large databases

4. VB5 BUG? or bug in my program?

5. OPENXML otpimal performance for large database insertions

6. Selecting DBMS

7. Large database performance

8. IQ & Star design

9. cross-posts (was Re: Large databases, performance)

10. Large databases, performance

11. Critical performance problems on large databases