Optimization

We're building a company wide network monitoring system
in Java, and need some advice on the database design and
tuning.

The application will need to concurrently INSERT,
DELETE, and SELECT from our EVENT table as efficiently as
possible. We plan to implement an INSERT thread, a DELETE
thread, and a SELECT thread within our Java program.

The EVENT table will have several hundred million records
in it at any given time. We will prune, using DELETE, about
every five seconds to keep the active record set down to
a user controlled size. And one of the three queries will
be executed about every twenty seconds. Finally, we'll
INSERT as fast as we can in the INSERT thread.

Being new to MSSQL, we need advice on

1) Server Tuning - Memory allocations, etc.
2) Table Tuning - Field types
3) Index Tuning - Are the indexes right
4) Query Tuning - Hints, etc.
5) Process Tuning - Better ways to INSERT and DELETE, etc.

Thanks, in advance, for any suggestions you can make :-)

The table is

// CREATE TABLE EVENT (
// ID INT PRIMARY KEY NOT NULL,
// IPSOURCE INT NOT NULL,
// IPDEST INT NOT NULL,
// UNIXTIME BIGINT NOT NULL,
// TYPE TINYINT NOT NULL,
// DEVICEID SMALLINT NOT NULL,
// PROTOCOL TINYINT NOT NULL
// )
//
// CREATE INDEX INDEX_SRC_DEST_TYPE
// ON EVENT (
// IPSOURCE,IPDEST,TYPE
// )

The SELECTS are

private static String QueryString1 =
"SELECT ID,IPSOURCE,IPDEST,TYPE "+
"FROM EVENT "+
"WHERE ID >= ? "+
" AND ID <= ?";

private static String QueryString2 =
"SELECT COUNT(*),IPSOURCE "+
"FROM EVENT "+
"GROUP BY IPSOURCE "+
"ORDER BY 1 DESC";

private static String QueryString3 =
"SELECT COUNT(*),IPDEST "+
"FROM EVENT "+
"WHERE IPSOURCE = ? "+
" AND TYPE = ? "+
"GROUP BY IPDEST "+
"ORDER BY 1 DESC";

The DELETE is

private static String DeleteIDString =
"DELETE FROM EVENT "+
"WHERE ID < ?";Hi,

Refer www.SQL-Server-Performance.com web site for SQL Server Performance issues.
Almost all yours queries id answered in perfect articles and example wise.

Thanks, Amit

pwhittington@.nitrodata.com (rocky) wrote in message news:<3961374c.0307041440.4ebb73ef@.posting.google.com>...
> We're building a company wide network monitoring system
> in Java, and need some advice on the database design and
> tuning.
> The application will need to concurrently INSERT,
> DELETE, and SELECT from our EVENT table as efficiently as
> possible. We plan to implement an INSERT thread, a DELETE
> thread, and a SELECT thread within our Java program.
> The EVENT table will have several hundred million records
> in it at any given time. We will prune, using DELETE, about
> every five seconds to keep the active record set down to
> a user controlled size. And one of the three queries will
> be executed about every twenty seconds. Finally, we'll
> INSERT as fast as we can in the INSERT thread.
> Being new to MSSQL, we need advice on
> 1) Server Tuning - Memory allocations, etc.
> 2) Table Tuning - Field types
> 3) Index Tuning - Are the indexes right
> 4) Query Tuning - Hints, etc.
> 5) Process Tuning - Better ways to INSERT and DELETE, etc.
> Thanks, in advance, for any suggestions you can make :-)
>
> The table is
> // CREATE TABLE EVENT (
> // ID INT PRIMARY KEY NOT NULL,
> // IPSOURCE INT NOT NULL,
> // IPDEST INT NOT NULL,
> // UNIXTIME BIGINT NOT NULL,
> // TYPE TINYINT NOT NULL,
> // DEVICEID SMALLINT NOT NULL,
> // PROTOCOL TINYINT NOT NULL
> // )
> //
> // CREATE INDEX INDEX_SRC_DEST_TYPE
> // ON EVENT (
> // IPSOURCE,IPDEST,TYPE
> // )
> The SELECTS are
> private static String QueryString1 =
> "SELECT ID,IPSOURCE,IPDEST,TYPE "+
> "FROM EVENT "+
> "WHERE ID >= ? "+
> " AND ID <= ?";
> private static String QueryString2 =
> "SELECT COUNT(*),IPSOURCE "+
> "FROM EVENT "+
> "GROUP BY IPSOURCE "+
> "ORDER BY 1 DESC";
> private static String QueryString3 =
> "SELECT COUNT(*),IPDEST "+
> "FROM EVENT "+
> "WHERE IPSOURCE = ? "+
> " AND TYPE = ? "+
> "GROUP BY IPDEST "+
> "ORDER BY 1 DESC";
> The DELETE is
> private static String DeleteIDString =
> "DELETE FROM EVENT "+
> "WHERE ID < ?";|||Some mixed advice:

1) Make sure that you have plenty of memory in the server. Query2 will
always generate a full index scan, and you need to be able to fit all of the
data for that index in memory to avoid excessive disk I/O. 100 million rows
* 13 bytes = 1.3 GB of memory. I would suggest having at least 2GB memory in
the server.

2) Make sure you have a disk subsystem with good performance for both
reading and writing. Use several striped disks for the data (not RAID5), and
a separate disk for log. Use a disk controller with a battery-backed write
cache. The disk performance is important even if you have plenty of memory,
but it is absolutely vital if you can not fit all the data in memory.

3) Add a NOLOCK hint to all SELECT queries. Otherwise, all inserts will be
blocked while executing the SELECTs.

4) Change the index to IPSOURCE,TYPE,IPDEST to optimize Query3

5) Make sure that you batch inserts. Have the INSERT thread generate batches
of inserts and send them all to the server in a single batch as a single
transaction. Having a transaction for each inserted row will kill
performance unless you have a really good disk controller.

/SG

"rocky" <pwhittington@.nitrodata.com> wrote in message
news:3961374c.0307041440.4ebb73ef@.posting.google.c om...
> We're building a company wide network monitoring system
> in Java, and need some advice on the database design and
> tuning.
> The application will need to concurrently INSERT,
> DELETE, and SELECT from our EVENT table as efficiently as
> possible. We plan to implement an INSERT thread, a DELETE
> thread, and a SELECT thread within our Java program.
> The EVENT table will have several hundred million records
> in it at any given time. We will prune, using DELETE, about
> every five seconds to keep the active record set down to
> a user controlled size. And one of the three queries will
> be executed about every twenty seconds. Finally, we'll
> INSERT as fast as we can in the INSERT thread.
> Being new to MSSQL, we need advice on
> 1) Server Tuning - Memory allocations, etc.
> 2) Table Tuning - Field types
> 3) Index Tuning - Are the indexes right
> 4) Query Tuning - Hints, etc.
> 5) Process Tuning - Better ways to INSERT and DELETE, etc.
> Thanks, in advance, for any suggestions you can make :-)
>
> The table is
> // CREATE TABLE EVENT (
> // ID INT PRIMARY KEY NOT NULL,
> // IPSOURCE INT NOT NULL,
> // IPDEST INT NOT NULL,
> // UNIXTIME BIGINT NOT NULL,
> // TYPE TINYINT NOT NULL,
> // DEVICEID SMALLINT NOT NULL,
> // PROTOCOL TINYINT NOT NULL
> // )
> //
> // CREATE INDEX INDEX_SRC_DEST_TYPE
> // ON EVENT (
> // IPSOURCE,IPDEST,TYPE
> // )
> The SELECTS are
> private static String QueryString1 =
> "SELECT ID,IPSOURCE,IPDEST,TYPE "+
> "FROM EVENT "+
> "WHERE ID >= ? "+
> " AND ID <= ?";
> private static String QueryString2 =
> "SELECT COUNT(*),IPSOURCE "+
> "FROM EVENT "+
> "GROUP BY IPSOURCE "+
> "ORDER BY 1 DESC";
> private static String QueryString3 =
> "SELECT COUNT(*),IPDEST "+
> "FROM EVENT "+
> "WHERE IPSOURCE = ? "+
> " AND TYPE = ? "+
> "GROUP BY IPDEST "+
> "ORDER BY 1 DESC";
> The DELETE is
> private static String DeleteIDString =
> "DELETE FROM EVENT "+
> "WHERE ID < ?";