# Jmeter RDBMS 테스트용 쿼리문 정리

Jmeter 테스트용 쿼리문 정리

Jmeter 에서 RDBMS를 벤치마크하기 위해 사용한 쿼리문들이다.

jdbc:<cubrid://220.117.238.110:3306/testdb>

220.117.238.110

db외부 접근 과정

커넥션 설정(커넥터가 세팅됨)

-> 외부 접근 허용 설정(ip, 계정) ->

- 테이블 생성 SQL문

## CREATE TABLE INVENTORY

(

idInventory Date,

idChar UInt32,

nameChar String,

nameItem String,

typeItem String,

numItem **UInt32**

) ENGINE = MergeTree(idInventory,  idChar, 8192);

## CREATE TABLE INVENTORY

(

idInventory **INT PRIMARY KEY AUTO\_INCREMENT**,

idChar **INT**,

nameChar **VARCHAR**(**20**),

nameItem **VARCHAR**(**20**),

typeItem **VARCHAR**(**20**),

numItem **INT**

);

postgre 문법

postgre dialect - auto increment -> serial

CREATE TABLE INVENTORY

(

idInventory SERIAL PRIMARY KEY NOT NULL ,

idChar INT,

nameChar VARCHAR(20),

nameItem VARCHAR(20),

typeItem VARCHAR(20),

numItem INT

);

-----

- Insert 문

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

777,

'${\_\_RandomString(${\_\_Random(3,8,)},abcdefghijklmnopqrstuvwxyz0123456789,)}' ,

'빨간약' ,

'소모품' ,

${\_\_Random(1,100,)} );

# INSERT TEST : 아이템 획득하여 인벤토리 테이블에 추가하기

# 랜덤한 캐릭터 id, 캐릭터명, 아이템 갯수 생성

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

${\_\_Random(1,1000,)},

'${\_\_RandomString(${\_\_Random(3,8,)},abcdefghijklmnopqrstuvwxyz0123456789,)}' ,

'redpotion' ,

'typePotion' ,

${\_\_Random(1,100,)} );

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

777,

'nameNova' ,

'redpotion' ,

'typePotion' ,

55 );

#UPDATE TEST : 아이템 갯수 업데이트하기

#인벤토리 id를 임의로가져온 후, 임의 갯수(1~100)만큼 추가

## UPDATE INVENTORY SET

## numItem = numItem + ${\_\_Random(1,100,)}

## WHERE idInventory = ${\_\_Random(1,10000,)};

- select : 아이템 목록 가져오기

#SELECT TEST : 캐릭터의 인벤토리 불러오기

#임의로 캐릭터의 id값을 가진 인벤토리를 불러온다.

## SELECT \* FROM INVENTORY WHERE idChar = ${\_\_Random(1,1000,)};

## SELECT idInventory, idChar, nameItem, typeItem, numItem  FROM INVENTORY WHERE idInventory = ${\_\_Random(1,1000,)};

**DROP PROCEDURE** **myFunction**;

**DELIMITER** $$

**CREATE PROCEDURE** **myFunction**()

## BEGIN

**DECLARE** i **INT DEFAULT** **1**;

## WHILE (i < 1000) DO

START TRANSACTION;

## UPDATE INVENTORY SET

numItem = numItem + i

**WHERE** idInventory = i;

## UPDATE INVENTORY SET

numItem = numItem + i + **5**

**WHERE** idInventory = i + **5**;

**INSERT INTO** **INVENTORY** ( idChar, nameChar, nameItem, typeItem, numItem)

**VALUES** (

**777**,

**CONCAT**(**'item'**, i ) ,

**'****redpotion****'** ,

**'potiontype****'** ,

i );

COMMIT;

**SET** i = i + **1**;

**END WHILE**;

**END**$$

**DELIMITER** ;

**CALL** **myFunction**();

CREATE PROCEDURE insert\_data(a integer, b integer)

LANGUAGE SQL

AS $$

INSERT INTO tbl VALUES (a);

INSERT INTO tbl VALUES (b);

$$;

CALL insert\_data(1, 2);

#

#

# START TRANSACTION;

#

# UPDATE INVENTORY SET

# numItem = numItem +2

# WHERE idInventory =3;

#

# UPDATE INVENTORY SET

# numItem = numItem +2

# WHERE idInventory =3;

#

# UPDATE INVENTORY SET

# numItem = numItem +2

# WHERE idInventory =3;

#

# COMMIT;

#

#SELECT TEST : 인벤토리 테이블에서 특정 유저의 아이템 목록 가져오기

# SELECT nameItem, typeItem, numItem FROM INVENTORY

# WHERE nameChar = '캐릭터명';

#

#

#

# #

# DELETE FROM INVENTORY;

# ALTER TABLE INVENTORY AUTO\_INCREMENT = 1;

# SELECT \*from TB\_MEMBER;

# INSERT INTO TB\_MEMBER (emailMember, pwMember, nameMember)

# VALUES ('[test@nova.com](mailto:test@nova.com)', '1234', 'nova');

# INSERT INTO TB\_CHARACTER ( idMember, nameChar, jobChar)

# VALUES (4, 'jamsya', 'warrior');

#

# DELETE FROM TB\_MEMBER WHERE idMember = 2;

#

# #

# INSERT INTO MEMBER (emailMember, pwMember, nameMember)

# VALUES (

# # 랜덤 이메일을 생성하는 파트

# concat( substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# '@',

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()\*36+1, 1),

# '.com'),

#

# # 랜덤 패스워드를 생성하는 파트

#

# concat('pw', substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1)

# )

# ,

# # 랜덤 회원 이름을 생성하는 파트

# concat('nova',substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() \* 36 + 1, 1)

# ));

#

#