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,IPD EST,TYPE "+
"FROM EVENT "+
"WHERE ID >= ? "+
" AND ID <= ?";
private static String QueryString2 =
"SELECT COUNT(*),IPSOUR CE "+
"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 < ?"; 2 4262
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 pw**********@ni trodata.com (rocky) wrote in message news:<39******* *************** ****@posting.go ogle.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,IPD EST,TYPE "+ "FROM EVENT "+ "WHERE ID >= ? "+ " AND ID <= ?";
private static String QueryString2 = "SELECT COUNT(*),IPSOUR CE "+ "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,I PDEST 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" <pw**********@n itrodata.com> wrote in message
news:39******** *************** ***@posting.goo gle.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,IPD EST,TYPE "+ "FROM EVENT "+ "WHERE ID >= ? "+ " AND ID <= ?";
private static String QueryString2 = "SELECT COUNT(*),IPSOUR CE "+ "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 < ?"; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Alex Vinokur |
last post by:
For instance, we need to measure performance
of assignment 'ch1 = ch2' where ch1 and ch2 are of char type.
We need to do that for different optimization levels of the same compiler.
Here is some test program.
Environment
-----------
|
by: Rune |
last post by:
Is it best to use double quotes and let PHP expand variables inside strings,
or is it faster to do the string manipulation yourself manually?
Which is quicker?
1)
$insert = 'To Be';
$sentence = "$insert or not $insert. That is the question.";
or
|
by: AC Slater |
last post by:
Whats the simplest way to change a single stored procedures query
optimization level?
In UDB8 that is.
/F
|
by: Eugene |
last post by:
I am trying to set query optimization class in a simple SQL UDF like
this:
CREATE FUNCTION udftest ( in_item_id INT )
SPECIFIC udftest
MODIFIES SQL DATA
RETURNS TABLE( location_id INT, period_id INT )
BEGIN ATOMIC
SET CURRENT QUERY OPTIMIZATION 1;
|
by: WantedToBeDBA |
last post by:
Hi all,
db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced \
db2 (cont.) => enable query optimization)
DB20000I The SQL command completed successfully.
db2 => insert into emp values(1,'m')
| |
by: Kunal |
last post by:
Hello,
I need help in removing if ..else conditions inside for loops. I have
used the following method but I am not sure whether it has actually
helped.
Below is an example to illustrate what I have used.
Original code :
c= 0 ;
for (i=0; i<999; i++)
|
by: mjbackues at yahoo |
last post by:
Hello.
I'm having a problem with the Visual Studio .net (2003) C++ speed
optimization, and hope someone can suggest a workaround.
My project includes many C++ files, most of which work fine with speed
optimization turned on. At least one does not however, though it does
work with size optimization turned on. I don't know specifically what
the optimizer is doing wrong, just that the output is incorrect. And I
know within about 10...
|
by: wkaras |
last post by:
I've compiled this code:
const int x0 = 10;
const int x1 = 20;
const int x2 = 30;
int x = { x2, x0, x1 };
struct Y
{
|
by: db2admin |
last post by:
hi,
I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i change optimization
level of this one sql. also, developers are not comfortable with this.
i am thinking of writing stored procedure in which i can execute sql
with lower optimization level and return resultset to java code.
|
by: Ravikiran |
last post by:
Hi Friends,
I wanted know about whatt is ment by zero optimization and
sign optimization and its differences....
Thank you...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |