Thread Yield mengatasi masalah Deadlock Thread

Thread yield definition

Forces the current thread to yield control to the part of the implementation responsible for scheduling threads. Doing so may have the effect of allowing other threads to run, and may be essential to avoid deadlock in a co-operative scheduling environment.

Mari Mengelak Mengesan Table ( How to Avoid Table Scans in Mysql)

7.2.16. How to Avoid Table Scans

The output from EXPLAIN shows ALL in the type column when MySQL uses a table scan to resolve a query. This usually happens under the following conditions:

• The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.

• There are no usable restrictions in the ON or WHERE clause for indexed columns.

• You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 7.2.4, “WHERE Clause Optimization”.

• You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

MySQL Enterprise. For expert advice on avoiding excessive table scans subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.

For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:

• Use ANALYZE TABLE tbl_name to update the key distributions for the scanned table. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.

• Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:

  SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
   WHERE t1.col_name=t2.col_name;
  

  See Section 13.2.7.2, “Index Hint Syntax”.

• Start mysqld with the --max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that no key scan causes more than 1,000 key seeks. See Section 5.2.3, “System Variables”.

How to Identify and Delete Duplicate SQL Server Records

How to Identify and Delete Duplicate SQL Server Records

---

by Randy Dyess

Recently, I was asked to help someone clean up their database after they had double loaded an import file. The problem they were having in identifying and deleting the duplicate information was the fact that a timestamp is applied to each row of data as it is inserted into the table. While the rest of the row of data was duplicated, the timestamp made the row unique. It was this uniqueness that caused the simple methods of determining and deleting duplicate data to fail. They needed a way to delete data from a table in which they determine the criteria of what made the data duplicate.

After helping them out with their problem, I decided to write a short article to show the simple solution I came up with to delete the duplicate data from a table, even if that data is considered unique by SQL Server. I know there are many ways to delete duplicate data, but bear with me as I explain my way. As always, if you have another way, great write it up and let us know about it. If not, look over these scripts and see if you can use them to create your own method.

Before I get into the example that actually deals with the described problem, I am going to start by showing a method to delete simple duplicate data for those who may be new to SQL Server and do not know how to clean up duplicate data from a table.

/**********************************************
Example of a simple duplicate data delete script.
**********************************************/

/**********************************************
Set up test environment
**********************************************/
SET NOCOUNT ON

--Create test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

CREATE TABLE tDupData
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Create test data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address2','01/15/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address3','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address','01/16/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address','01/16/2003')

-- Dup Data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
GO

/**********************************************
Finish set up
**********************************************/

/**********************************************
Simple duplicate data
**********************************************/

--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress, dtmModified
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified

--Insert the delete data back
INSERT INTO tDupData
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress,dtmModified
HAVING COUNT(*) > 1

--Check table
SELECT * FROM tDupData

--Drop temp table
DROP TABLE #tempduplicatedata

--drop test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

As you can see, it is not hard to delete data that is duplicated across all columns of a table. What is harder to do is to delete data that you consider duplicate based on your business rules while SQL Server considers it unique data. This usually happens when one or more columns contain different data, but your business rules have determined that because the main columns of the table are the same, you have duplicate data. This usually happens when you have a problem during a data load and data is loaded multiple times generating new timestamps or identity values for each row. The identify value or the data field will cause uniqueness in the data and the simple delete method will fail.

/**********************************************
Example of a complex duplicate data delete script.
**********************************************/

/**********************************************
Set up test environment
**********************************************/
SET NOCOUNT ON

--Create test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

CREATE TABLE tDupData
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Create test data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address2','01/15/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address3','01/15/2003')
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
-- Simple Dup Data and complex dup data
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address','01/16/2003')
-- complex dup data
INSERT INTO tDupData VALUES (3,'CompanyThree','Address','01/16/2003')
-- complex dup data
GO

/**********************************************
Finish set up
**********************************************/

/**********************************************
Complex duplicate data
**********************************************/

--Clean table out to include only one row per company
--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
lngCompanyID INTEGER
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Clean out simple duplicate data first
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress, dtmModified
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified

--Insert the delete data back
INSERT INTO tDupData
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM tDupData
GROUP BY lngCompanyID,strCompanyName,strAddress,dtmModified
HAVING COUNT(*) > 1

--Clean out temp table
TRUNCATE TABLE #tempduplicatedata

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata (lngCompanyID,strCompanyName)
SELECT lngCompanyID,strCompanyName FROM tDupData
GROUP BY lngCompanyID,strCompanyName
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'

--Update temp table to add strAddress and dtmModified
UPDATE #tempduplicatedata
SET strAddress = tDupData.strAddress
,dtmModified = tDupData.dtmModified
FROM #tempduplicatedata
INNER JOIN tDupData
ON #tempduplicatedata.lngCompanyID = tDupData.lngCompanyID
AND #tempduplicatedata.strCompanyName = tDupData.strCompanyName


--Delete dup from original table
DELETE FROM tDupData
FROM tDupData
INNER JOIN #tempduplicatedata
ON  tDupData.lngCompanyID = #tempduplicatedata.lngCompanyID
AND tDupData.strCompanyName = #tempduplicatedata.strCompanyName
AND tDupData.strAddress = #tempduplicatedata.strAddress
AND tDupData.dtmModified = #tempduplicatedata.dtmModified

--Verify original table only has three rows of data
SELECT * FROM tDupData

--Drop temp table
DROP TABLE #tempduplicatedata

--drop test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

This is a little more complicated than the simple duplicate data delete script, but easy to figure out once you see it. A word of caution here, you should investigate any child tables before you delete data from a table in order to prevent creating orphan rows. You can ether delete the data from the child tables first or update them to reflect the identity key value of the data row in the main table you are going to keep. The choice will be determined by your situation and any operating standards you may have.

While having to clean up duplicate data is not something you should have to do every day, the processes you learn from playing with these two scripts should give you a starting point the next time you find duplicate information in your database.

Copyright 2003 by Randy Dyess, All Rights Reserved

Make sure you purchase your copy of Transact-SQL Language Reference Guide from my website www.TransactSQL.Comtoday to learn more about Transact-SQL by reviewing the more than 1200 examples contained within the book.

Mapping obj to relational db strategies

1. Table per concrete class - buang semua hubungan polymorphism and inheritance dari domain model

2. Table per class hierarchy- wujudkan poly dengan cara meng normalkan hubungan model dan gunakan jenis diskriminasi column utk hold type info


3. table per subclass - represent "is a" (inherintance) r/ships as "has a" (foreign key) relationships

Configuring Tomcat with IIS Web Server

http://www.onjava.com/pub/a/onjava/2002/12/18/tomcat.html?page=1

Installation of SAP NetWeaver 2004s Java and ABAP Trial Version

• English
• IT
• Review
• SAP NetWeaver
• Software
• Tips and Tricks

I'm going to try the latest trial version of SAP NetWeaver 2004s Java and ABAP on a virtual machine based on VMware Server. First of all, I have to download all packages from SDN.

If you planned to install these products on separate host, the installation order is not important. If you have only one machine to try, it is recommended to install ABAP edition prior to the Java edition (read more).

One more thing, the Java edition does not support JDK 1.5.0 so you have to install JDK 1.4.2 instead.

Install SAP NetWeaver 04s Full ABAP Trial Version

Installing ABAP Trial Version is quite easy. I just answered 2 questions.

1. Install location: E:\SAP
2. Master password: ********

It took about 2 hours to finish the installation (8.6 GB). Then I started to install Java edition.

Install SAP NetWeaver 04s SP9 Java Trial Version

1. JDK location: C:\j2sdk1.4.2_13
2. JCE location: E:\jce_policy-1.4.2.zip
3. Instace ID: J2E
4. Install location: E:
5. Master password: ********
6. User domain: Local installation
7. Administrator password: ********
8. Service user password: ********
9. Database ID: J2E
10. Host: xxxx
11. MaxDB installation drives: E:
12. Database administrator password: ********
13. Database manager operator password: ********
14. Java database schema password: ********
15. Secure store settings: ********
16. Database statistics: Create statistics after the import ends
17. SCS instance number: 01
18. SDM password: ********

Consequences

It turned out that these trial products must not be installed on the same machine in any orders. I tried both and got unexpected error during installation.

1. ABAP -> Java - It went very close to success but eventually, Java version didn't detect the existing ABAP instance correctly.

2. Java -> ABAP - There is no option to choose instance number in ABAP version. Furthermore, the install wizard in ABAP didn't even try to add new database but remove all existing database and install new ones.

Finally, I managed to have Java version in virtual machine with 384 MB main memory. It took about 3 hours to successfully start Java engine. Anyway, it is possible to freeze the machine so I don't have to shutdown and start it again.

Tags: sap netweaver, abap, java

Java vs NetWeaver Expert ....To be Or Not Tobe

NetWeaver, what`s that?.... java, What`s that?....
Will i become Netweaver Expert??????
To Be or Not To Be???
See Me later....

Morale Of Story...

Time`s to change....