原数据库构造大表或慢查询SQL语句

发布日期 2021年9月16日 · 已更新 2024年3月1日

Tags： Oracle 原创 PG GreenPlum MSSQL SQL Server MySQL 小麦苗常用大表慢查询测试

👉 本文共约484个字，系统预计阅读时间或需2分钟。

Oracle
SQL Server
MySQL
PostgreSQL、GreenPlum
GreenPlum生成100G的大库

Oracle

-- 一个查询慢的sql例子
select count(1) from dba_objects a 
inner join user_objects b on 1=1
inner join user_objects c on 1=1
;

exec dbms_lock.sleep(5);

--  构造大表
select level,level from dual connect by level<=1000;

DROP TABLE T_YH_20170705_LHR; 
CREATE TABLE T_YH_20170705_LHR NOLOGGING 
AS 
SELECT 137 || (LPAD(ROWNUM, 8,'0'))  x
  FROM DUAL
CONNECT BY LEVEL <= 99999999;

create table t1 as select * from dba_objects;
insert into t1 select * from t1;
insert into t1 select * from t1;
。。。。

-- Oracle 生成20000张表，共包含约40万个字段
DECLARE
    sql1 VARCHAR2(4000);
    sql2 VARCHAR2(4000);
    v_sql VARCHAR2(32767);
BEGIN
  FOR i IN 1..20000 LOOP

      -- 表
    sql1 :='' ;
    sql1 := 'CREATE TABLE ' || 'tb_' || i || ' (';

        -- 列
    sql2:= '';
    FOR j IN 1..20 LOOP
      sql2 := sql2 || 'c_' || j || ' number,';
    END LOOP;

    sql2 := RTRIM(sql2, ',') || ')';

    v_sql := sql1 || sql2;
    -- dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql;

  END LOOP;

END;
/

select count(*)  from  dba_tables ;
select count(*) from dba_tab_cols;
select d.owner,COUNT(1)  from dba_tables d GROUP BY d.owner ORDER BY 2 desc;

-- 一个查询慢的sql例子

select count(1) from dba_objects a

inner join user_objects b on 1=1

inner join user_objects c on 1=1

;

exec dbms_lock.sleep(5);

-- 构造大表

select level,level from dual connect by level<=1000;

DROP TABLE T_YH_20170705_LHR;

CREATE TABLE T_YH_20170705_LHR NOLOGGING

SELECT 137 || (LPAD(ROWNUM, 8,'0')) x

FROM DUAL

CONNECT BY LEVEL <= 99999999;

create table t1 as select * from dba_objects;

insert into t1 select * from t1;

。。。。

-- Oracle 生成20000张表，共包含约40万个字段

DECLARE

sql1 VARCHAR2(4000);

sql2 VARCHAR2(4000);

v_sql VARCHAR2(32767);

BEGIN

FOR i IN 1..20000 LOOP

-- 表

sql1 :='' ;

sql1 := 'CREATE TABLE ' || 'tb_' || i || ' (';

-- 列

sql2:= '';

FOR j IN 1..20 LOOP

sql2 := sql2 || 'c_' || j || ' number,';

END LOOP;

sql2 := RTRIM(sql2, ',') || ')';

v_sql := sql1 || sql2;

-- dbms_output.put_line(v_sql);

EXECUTE IMMEDIATE v_sql;

END LOOP;

END;

select count(*) from dba_tables ;

select count(*) from dba_tab_cols;

select d.owner,COUNT(1) from dba_tables d GROUP BY d.owner ORDER BY 2 desc;

SQL Server

-- 慢查询
-- 延时 '00时:00分:10秒:00毫秒' 执行下一步
waitfor delay '00:00:10:00'

-- 大表
drop table test;
select * into test from master.sys.all_columns;
insert into test select * from test;
go 30

declare @i int   
set @i=0
while @i<=25
  begin
    RAISERROR('%s:%d', 0, 1, '变量i的值：', @i)  WITH NOWAIT
    insert into test select * from test
    set @i=@i +1
end

-- 3355万行，7g数据

-- 200万 800MB
CREATE TABLE BigTable (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   age INT,
   email VARCHAR(100),
   phone VARCHAR(20),
   address VARCHAR(200),
   salary DECIMAL(10,2),
   hire_date DATE,
   is_active BIT,
   PRIMARY KEY (id)
);

INSERT INTO BigTable (id, name, age, email, phone, address, salary, hire_date, is_active)
SELECT TOP 2000000
   ROW_NUMBER() OVER (ORDER BY a.name) AS id,
   a.name,
   ABS(CHECKSUM(NEWID())) % 100 AS age,
   CONCAT(a.name, '@example.com') AS email,
   '555-555-' + RIGHT('0000' + CAST(ABS(CHECKSUM(NEWID())) % 10000 AS VARCHAR(4)), 4) AS phone,
   CONCAT(a.column_id, ', ', a.is_identity, ', ', a.is_column_set, ' ', a.collation_name) AS address,
   CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS DECIMAL(10, 2)) AS salary,
   DATEADD(day, -ABS(CHECKSUM(NEWID())) % 3650, GETDATE()) AS hire_date,
   CAST(ABS(CHECKSUM(NEWID())) % 2 AS BIT) AS is_active
FROM sys.all_columns a
CROSS JOIN sys.all_columns b;

-- 100万（很慢）
drop table TestStatistics
CREATE TABLE TestStatistics
(
    COL1 INT IDENTITY(1,1)  ,
    COL2 INT                ,
    COL3 DATETIME           ,
    COL4 VARCHAR(50)            
)
GO

INSERT INTO TestStatistics VALUES (RAND()*10,CAST(GETDATE()-RAND()*300 AS date),NEWID())
GO 1000000

-- 15分钟
Create table BusinessInfoTable
(
    BuniessCode1 varchar(50),
    BuniessCode2 varchar(50),
    BuniessCode3 varchar(50),
    BuniessCode4 varchar(50),
    BuniessStatus1 tinyint,
    BuniessStatus2 tinyint,
    BuniessDateTime1 Datetime,
    BuniessDateTime2 Datetime,
    OtherColumn1 varchar(50),
    OtherColumn2 varchar(50),
    OtherColumn3 varchar(50)
)

declare @i int=0
while @i<1000000
begin
    insert into BusinessInfoTable 
    values 
    (
        NEWID(),NEWID(),NEWID(),NEWID(),RAND()*100,RAND()*100,
        DATEADD(MI,@i,GETDATE()),DATEADD(MI,@i,GETDATE()),NEWID(),NEWID(),NEWID()
    )
    set @i=@i+1
end

-- 慢查询

-- 延时 '00时:00分:10秒:00毫秒' 执行下一步

waitfor delay '00:00:10:00'

-- 大表

drop table test;

select * into test from master.sys.all_columns;

insert into test select * from test;

go 30

declare @i int

set @i=0

while @i<=25

begin

RAISERROR('%s:%d', 0, 1, '变量i的值：', @i) WITH NOWAIT

insert into test select * from test

set @i=@i +1

end

-- 3355万行，7g数据

-- 200万 800MB

CREATE TABLE BigTable (

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

age INT,

email VARCHAR(100),

phone VARCHAR(20),

address VARCHAR(200),

salary DECIMAL(10,2),

hire_date DATE,

is_active BIT,

PRIMARY KEY (id)

);

INSERT INTO BigTable (id, name, age, email, phone, address, salary, hire_date, is_active)

SELECT TOP 2000000

ROW_NUMBER() OVER (ORDER BY a.name) AS id,

a.name,

ABS(CHECKSUM(NEWID())) % 100 AS age,

CONCAT(a.name, '@example.com') AS email,

'555-555-' + RIGHT('0000' + CAST(ABS(CHECKSUM(NEWID())) % 10000 AS VARCHAR(4)), 4) AS phone,

CONCAT(a.column_id, ', ', a.is_identity, ', ', a.is_column_set, ' ', a.collation_name) AS address,

CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS DECIMAL(10, 2)) AS salary,

DATEADD(day, -ABS(CHECKSUM(NEWID())) % 3650, GETDATE()) AS hire_date,

CAST(ABS(CHECKSUM(NEWID())) % 2 AS BIT) AS is_active

FROM sys.all_columns a

CROSS JOIN sys.all_columns b;

-- 100万（很慢）

drop table TestStatistics

CREATE TABLE TestStatistics

(

COL1 INT IDENTITY(1,1) ,

COL2 INT ,

COL3 DATETIME ,

COL4 VARCHAR(50)

)

INSERT INTO TestStatistics VALUES (RAND()*10,CAST(GETDATE()-RAND()*300 AS date),NEWID())

GO 1000000

-- 15分钟

Create table BusinessInfoTable

(

BuniessCode1 varchar(50),

BuniessCode2 varchar(50),

BuniessCode3 varchar(50),

BuniessCode4 varchar(50),

BuniessStatus1 tinyint,

BuniessStatus2 tinyint,

BuniessDateTime1 Datetime,

BuniessDateTime2 Datetime,

OtherColumn1 varchar(50),

OtherColumn2 varchar(50),

OtherColumn3 varchar(50)

)

declare @i int=0

while @i<1000000

begin

insert into BusinessInfoTable

values

(

NEWID(),NEWID(),NEWID(),NEWID(),RAND()*100,RAND()*100,

DATEADD(MI,@i,GETDATE()),DATEADD(MI,@i,GETDATE()),NEWID(),NEWID(),NEWID()

)

set @i=@i+1

end

或者使用存储过程:

在 SQL Server 中，可以通过编写包含大量复杂查询和逻辑的存储过程来模拟长时间运行的存储过程。以下是一个示例存储过程，该存储过程使用循环和延迟来模拟长时间运行的过程：


CREATE PROCEDURE LongRunningProcedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Counter INT = 0;

  WHILE @Counter < 1000000
  BEGIN
    -- Perform complex queries and logic here
    WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds

    SET @Counter = @Counter + 1;
  END

END

LongRunningProcedure

CREATE PROCEDURE LongRunningProcedure

BEGIN

SET NOCOUNT ON;

DECLARE @Counter INT = 0;

WHILE @Counter < 1000000

BEGIN

-- Perform complex queries and logic here

WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds

SET @Counter = @Counter + 1;

END

LongRunningProcedure

在这个示例存储过程中，我们使用了一个简单的循环来模拟长时间运行的过程。在每次循环中，我们可以执行一些复杂的查询和逻辑，然后使用 WAITFOR DELAY 命令在每次循环之间延迟5秒钟。

当我们执行这个存储过程时，它将运行100万次循环，每次循环都需要等待5秒钟。这将导致存储过程运行非常长的时间，从而模拟长时间运行的过程。

或使用死循环：


DECLARE @a INT;
WHILE (1=1)
BEGIN
    SELECT @a = COUNT (*) FROM  master.dbo.sysprocesses  OPTION ( MAXDOP  6);
END;
GO

DECLARE @a INT;

WHILE (1=1)

BEGIN

SELECT @a = COUNT (*) FROM master.dbo.sysprocesses OPTION ( MAXDOP 6);

END;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(12)

标签： Oracle 原创 PG GreenPlum MSSQL SQL Server MySQL 小麦苗常用大表慢查询测试

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

15065313181说道：
2023年9月27日下午10:28
麦苗老师，请问，您的secureCRT的主体很漂亮，是怎么设置的
登录以回复

发表回复取消回复

要发表评论，您必须先登录。

原数据库构造大表或慢查询SQL语句

Oracle

SQL Server

相关文章

您可能还喜欢...

1 条回复

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

原 数据库构造大表或慢查询SQL语句

Oracle

SQL Server

相关文章

您可能还喜欢...

【PG】小麦苗PGCM第8期证书邮寄

PG修改参数文件并重启后参数不生效

《PostgreSQL技术内幕——原理探索》第十章 基础备份与时间点恢复

1 条回复

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

原数据库构造大表或慢查询SQL语句

《PostgreSQL技术内幕——原理探索》第十章基础备份与时间点恢复

发表回复取消回复