Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Task Prepare procedures for testing SQL server performance. Prepare tables with about 1,000,000 rows of data. Prepare procedures that generate random data. Check search performance

Task Prepare procedures for testing SQL server performance.

Prepare tables with about 1,000,000 rows of data.

Prepare procedures that generate random data.

Check search performance with and without index.

Data fields: key field

1 date type

1 varchar(255)

1 varchar(40)

Use the timekeeping function.

code>>

USE [?????] GO /****** Object: Table [dbo].[test_time] Script Date: 30.11.2022 14:02:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[test_time]( [id] [int] IDENTITY(1,1) NOT NULL, [date] [date] NULL, [string1] [varchar](255) NULL, [string2] [varchar](40) NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: StoredProcedure [dbo].[create_string] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[create_string] @max_l int, @min_l int,@string varchar(255) output AS BEGIN declare @l int, @i int =1, @z int exec rnd_v @max_l,@min_l, @l output set @string ='' while @i<=@l begin exec rnd_v 90,65, @z output set @string=@string+char(@z) set @i=@i+1 end END GO /****** Object: StoredProcedure [dbo].[random_date] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[random_date] @date date output as begin declare @y int declare @m int declare @d int declare @s_date varchar(10) exec rnd_v 2022,1900, @y output exec rnd_v 12,1, @m output if @m=2 begin exec rnd_v 28,1, @d output end else begin if @m=4 or @m=6 or @m=9 begin exec rnd_v 30,1, @d output end else begin exec rnd_v 30,1, @d output end end set @s_date=cast(@y as varchar(4))+'-'+cast(@m as varchar(2))+'-'+cast(@d as varchar(2)) set @date =cast(@s_date as date) end GO /****** Object: StoredProcedure [dbo].[rnd_v] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create PROCEDURE [dbo].[rnd_v] @x int, @y int, @z int output AS BEGIN set @z=cast(rand()*(@x-@y+1)+@y as int) END GO /****** Object: StoredProcedure [dbo].[test] Script Date: 30.11.2022 14:02:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[test] @n_r int as begin declare @w varchar(255),@w2 varchar(255) declare @r_date date, @i int=1 while @i<=@n_r begin exec create_string 39,3, @w output exec create_string 255,3, @w2 output exec random_date @r_date output insert into test_time values (@r_date, @w2,@w) set @i=@i+1 end end GO

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Murach's SQL Server 2012 For Developers

Authors: Bryan Syverson, Joel Murach, Mike Murach

1st Edition

1890774693, 9781890774691

More Books

Students also viewed these Databases questions

Question

demand curves in competitive markets represent

Answered: 1 week ago

Question

2. Why?

Answered: 1 week ago

Question

1. Where do these biases come from?

Answered: 1 week ago