February 7, 2013 at 10:24 am
I need some help with a query to split a table
master table
Id ComputerName Ip Vuln ScanDate
1 computerA 1.1.1 iav1 1/1/13
2 computerA 1.1.1 iav2 1/1/13
3 computerB 2.2.2 iav3 2/1/13
into to
computerInfo table
Id ComputerName Ip
1 computerA 1.1.1
2 computerB 2.2.2
vulnerabilities table
Id ComputerId Vuln ScanDate
1 1 iav1 1/1/13
2 1 iav2 1/1/13
3 2 iav3 2/1/13
thank you
February 7, 2013 at 5:50 pm
huy1002 (2/7/2013)
I need some help with a query to split a table
You can't do it with a single query but you can do it with two:
DECLARE @Computers TABLE
(Id INT, ComputerName VARCHAR(20), Ip VARCHAR(10)
,Vuln VARCHAR(10), ScanDate DATETIME)
INSERT INTO @Computers
SELECT 1,'computerA','1.1.1','iav1','2013-01-01'
UNION ALL SELECT 2,'computerA','1.1.1','iav2','2013-01-01'
UNION ALL SELECT 3,'computerB','2.2.2','iav3','2013-02-01'
UNION ALL SELECT 4,'computerC','3.3.3','iav3','2013-03-01'
SELECT Id=ROW_NUMBER() OVER (ORDER BY ComputerName)
,ComputerName, Ip
FROM @Computers
GROUP BY Ip, ComputerName
SELECT Id=ROW_NUMBER() OVER (ORDER BY Vuln)
,ComputerId=(
SELECT Id
FROM (
SELECT Id=ROW_NUMBER() OVER (ORDER BY ComputerName)
,ComputerName
FROM @Computers
GROUP BY Ip, ComputerName) b
WHERE a.ComputerName = b.ComputerName
)
,ComputerName, Vuln, ScanDate=MAX(ScanDate)
FROM @Computers a
GROUP BY Vuln, ComputerName
Note how the first query is the derived table of the subquery used in query 2.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 7, 2013 at 5:57 pm
--create some test data
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ComputerID] INT IDENTITY(1,1) NOT NULL,
[ComputerName] NVARCHAR(50) NULL,
127.0.0.1 NVARCHAR(50) NULL,
[Vuln] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (ComputerID))
INSERT INTO #TempTable
SELECT 'computerA','1.1.1','iav1','1/1/2013'
UNION
SELECT 'computerA','1.1.1','iav2','1/2/2013'
UNION
SELECT 'computerB','2.2.2','iav1','1/2/2013'
UNION
SELECT 'computerC','3.3.3','iav1','1/3/2013'
UNION
SELECT 'computerC','3.3.3','iav2','1/4/2013'
UNION
SELECT 'computerC','3.3.3','iav3','1/7/2013'
UNION
SELECT 'computerD','4.4.4','iav2','1/4/2013'
UNION
SELECT 'computerD','4.4.4','iav3','1/10/2013'
Create the target tables:
IF OBJECT_ID('tempdb..#computerInfo') IS NOT NULL
DROP TABLE #computerInfo
CREATE TABLE #computerInfo (
[ComputerID] INT NOT NULL,
[ComputerName] NVARCHAR(50) NULL,
127.0.0.1 NVARCHAR(50) NULL,
PRIMARY KEY (ComputerID))
IF OBJECT_ID('tempdb..#vulnerabilities') IS NOT NULL
DROP TABLE #vulnerabilities
--[EDITED TO ADD IDENTITY COL]
CREATE TABLE #vulnerabilities (
[ComputerID] INT NOT NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (ComputerID))
CREATE TABLE #vulnerabilities (
[VulnerabilityID] INT IDENTITY(1,1) NOT NULL,
[ComputerID] INT NOT NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (VulnerabilityID))
Now move the data from #TempTable
into the two new tables
--the original table
SELECT * FROM #TempTable
--populate the first table
INSERT INTO #computerInfo
(ComputerID,ComputerName,IP)
SELECT
ComputerID
,ComputerName
,IP
FROM
#TempTable
WHERE
ComputerID > 0
--now delete temptable
--and populate vulnerabilities at the same time
DELETE #TempTable
OUTPUT
DELETED.ComputerID
,DELETED.Vuln
,DELETED.ScanDate
INTO #vulnerabilities
WHERE ComputerID > 0
SELECT * FROM #computerInfo
SELECT * FROM #vulnerabilities
--now the table is empty!
SELECT * FROM #TempTable
February 8, 2013 at 10:57 am
Similar to the one above... you can run this entire script and see the results...
The one above was missing the Vulerability ID field...
--================Setting up sample data========
declare @master as table(
ID int not null
,ComputerName varchar(50)
,IP varchar(15)
,Vuln varchar(50)
,ScanDate datetime
)
insert into @master(ID, ComputerName, IP, Vuln, ScanDate)
values(1,'CompA','1.1.1.1','iav1', '1/1/13')
,(2,'CompA','1.1.1.1','iav2', '2/1/13')
,(3,'CompB','1.1.1.2','iav3', '2/1/13')
,(4,'CompC', '1.1.1.3','iav4','2/1/13')
--==============================================
--Assuming you have the tables created already
--==============================================
declare @CompInfo as table (
ID int not null
,ComputerName varchar(50)
,IP varchar(15)
);
declare @Vulnerabilities as table(
ID int not null
,ComputerID int not null
,Vuln varchar(50)
,ScanDate datetime);
--==============================================
--Actual Work Script
--==============================================
--Prep data and save to temp table
select c.CompID
, m1.ComputerName
, IP
,Row_Number()over(order by m1.computerName) VulnID
,Vuln
,ScanDate
into #TempTable
from @master m1
Inner Join (select row_number()over(order by ComputerName) CompID, ComputerName
from @master m2
group by ComputerName) c
on m1.ComputerName = c.ComputerName
group by c.CompID, m1.ComputerName, IP, Vuln, ScanDate
--===== Insert the Computer info into Existing Table=====
insert into @CompInfo(id, ComputerName,IP)
select distinct Compid, ComputerName, IP
from #TempTable
--===== Insert the Vuln data into Existing Table=====
insert into @Vulnerabilities(ID,ComputerID,Vuln, ScanDate)
select Vulnid, Compid,Vuln, ScanDate
from #TempTable
--========Clean up Temp Table=================
drop Table #TempTable
--=============RESULTS=====================
select *
from @CompInfo
select *
from @Vulnerabilities
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
February 8, 2013 at 1:28 pm
Want a cool Sig (2/8/2013)
Similar to the one above... you can run this entire script and see the results...The one above was missing the Vulerability ID field...
You're correct. An oversight on my part. I intended that the create table statement auto-generate the VulnerabilityID by using the IDENTITY attribute like this:
CREATE TABLE #vulnerabilities (
[VulnerabilityID] INT IDENTITY(1,1) NOT NULL,
[ComputerID] INT NOT NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (VulnerabilityID))
Thanks for the catch.
February 8, 2013 at 11:44 pm
Just for fun I decided to run each of these three methods (plus a fourth one I thought of later) to see which would perform the best. It's hard to tell with trivial data, so I threw them into a test procedure I--ahem!--"borrowed" from Jeff Moden. I've been working on creating a general purpose test procedure that would allow functions or scripts to be easily compared. This test procedure can generate up to 10,000,000 rows (the most I did was 1 million). It generates random data in each row and inserts it into a regular non-temporary table. Then each piece of code is run in a progression of 10, 100, 1000 (etc) rows with the time to run the task recorded in a results table. The results table saves each run separately unless a flag is set to clear all data. This is still a work in progress Any suggestions would be appreciated. Eventually I'll want to make a procedure out of it and turn it into a general purpose tool.
As for the test results...I was somewhat surprised at which of the snippets split the table into two tables the quickest. You'll have to run the code yourself to find the answer! 🙂
Note: At the bottom of the script are a couple of functions that will need creation for this to work. They are svfs so won't earn any praise. Eventually I'll try to turn them into iTVFs. Also, you will need a standard tally table.
SET NOCOUNT ON
DECLARE
@SampleSize BIGINT
,@CheckCount BIGINT
,@ClearResults BIT
SET @SampleSize = 100000
SET @ClearResults = 0
/* Create a table to hold the test results */
IF NOT EXISTS
(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
AND TABLE_NAME='TestResultsTableSplit')
BEGIN
CREATE TABLE dbo.TestResultsTableSplit (
[ID] INT IDENTITY(1,1) NOT NULL,
[RunNum] INT NULL,
[TestName] NVARCHAR(250) NULL,
[TotalTime] DECIMAL(18,6) NULL,
[RowCount] BIGINT NULL,
[StartTime] DATETIME2,
[EndTime] DATETIME2,
PRIMARY KEY (ID))
END
IF @ClearResults = 1
TRUNCATE TABLE dbo.TestResultsTableSplit
/* Create tables to hold the sample data */
IF NOT EXISTS
(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
AND TABLE_NAME='TestSampleDataTableSplit')
BEGIN
CREATE TABLE dbo.TestSampleDataTableSplit (
[ComputerID] INT NOT NULL,
[ComputerName] NVARCHAR(50) NULL,
127.0.0.1 NVARCHAR(50) NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (ComputerID))
END
SELECT @CheckCount = COUNT(*) FROM TestSampleDataTableSplit
IF @CheckCount <> @SampleSize
TRUNCATE TABLE TestSampleDataTableSplit
SELECT @CheckCount = COUNT(*) FROM TestSampleDataTableSplit
IF @CheckCount = 0
BEGIN
INSERT INTO TestSampleDataTableSplit
SELECT
*
FROM
(
SELECT
ComputerID = ROW_NUMBER() OVER (ORDER BY t2.N)
,a.*
FROM
(SELECT
ComputerName = 'comp' +CAST(dbo.svfGenerateRandomNumbers(0,10,'FLOAT',0,3) AS VARCHAR(3))
,IP = STUFF(REPLICATE('.'+CAST(dbo.svfGenerateRandomNumbers(0,10,'BIGINT',0,1) AS VARCHAR(3)),3),1,1,'')
,Vulnerability = 'iav1'+CAST(dbo.svfGenerateRandomNumbers(0,10,'FLOAT',0,3) AS VARCHAR(3))
,ScanDate = CAST(CAST(dbo.svfGenerateRandomNumbers(1,12,'BIGINT',0,1) AS VARCHAR(2))
+'/'+CAST(dbo.svfGenerateRandomNumbers(1,28,'BIGINT',0,1) AS VARCHAR(2))+'/2013' AS DATETIME)
) a
CROSS APPLY
dbo.Tally t1
CROSS JOIN
dbo.Tally t2
) b
WHERE
b.ComputerID <= @SampleSize
END
/* Start building the tests */
DECLARE
@TestID INT
,@TestName VARCHAR(250)
,@TestResult VARCHAR(250)
,@StartTime DATETIME2
,@EndTime DATETIME2
,@RowCount BIGINT
,@RunNum INT
,@i INT
,@x INT
,@strSQL VARCHAR(MAX)
,@strDesc VARCHAR(250)
/* Add the tests to run here */
IF OBJECT_ID('tempdb..#TestStrings') IS NOT NULL
DROP TABLE #TestStrings
CREATE TABLE #TestStrings (
[ID] INT IDENTITY(1,1) NOT NULL,
[TestName] VARCHAR(150) NULL,
[TestString] VARCHAR(MAX) NULL,
PRIMARY KEY (ID))
INSERT INTO #TestStrings (TestName,TestString)
VALUES (
'CoolSig'
,dbo.svfRemoveExcessSpaces(
'DECLARE @computerInfo AS TABLE
(ComputerID INT NULL,ComputerName NVARCHAR(50),IP NVARCHAR(50));
DECLARE @vulnerabilities AS TABLE
(VulnerabilityID INT NULL,ComputerID INT NULL,Vulnerability NVARCHAR(50),ScanDate DATETIME);
INSERT INTO @computerInfo
(ComputerID,ComputerName,IP)
SELECT DISTINCT ComputerID,ComputerName,IP
FROM #TempTable;
INSERT INTO @vulnerabilities
(ComputerID,Vulnerability,ScanDate)
SELECT ComputerID,Vulnerability,ScanDate
FROM #TempTable
DROP TABLE #TempTable;')
)
INSERT INTO #TestStrings (TestName,TestString)
VALUES (
'Dwain'
,dbo.svfRemoveExcessSpaces(
'INSERT INTO #computerInfo
SELECT ComputerID,ComputerName,Ip
FROM #TempTable
WHERE ComputerID > 0
GROUP BY Ip,ComputerName,ComputerID
ORDER BY ComputerID;
INSERT INTO #vulnerabilities
SELECT ComputerId =
(SELECT ComputerID FROM
(SELECT ComputerID,ComputerName
FROM #computerInfo
GROUP BY Ip,ComputerName,ComputerID) b
WHERE a.ComputerID = b.ComputerID)
,Vulnerability,ScanDate = MAX(ScanDate)
FROM #TempTable a
WHERE a.ComputerID > 0
GROUP BY a.Vulnerability,a.ComputerID;
DROP TABLE #TempTable;')
)
INSERT INTO #TestStrings (TestName,TestString)
VALUES (
'Willis'
,dbo.svfRemoveExcessSpaces(
'INSERT INTO #computerInfo
(ComputerID,ComputerName,IP)
SELECT ComputerID,ComputerName,IP
FROM #TempTable
WHERE ComputerID > 0;
DELETE #TempTable
OUTPUT DELETED.ComputerID,DELETED.Vulnerability,DELETED.ScanDate
INTO #vulnerabilities WHERE ComputerID > 0;')
)
INSERT INTO #TestStrings (TestName,TestString)
VALUES (
'Willis_ALTER_RENAME'
,dbo.svfRemoveExcessSpaces(
'INSERT INTO dbo.TestVulnerabilities
(ComputerID,Vulnerability,ScanDate)
SELECT ComputerID,Vulnerability,ScanDate
FROM dbo.TestSplitTable;
ALTER TABLE dbo.TestSplitTable DROP COLUMN Vulnerability,ScanDate;
SELECT * INTO dbo.TestComputerInfo FROM dbo.TestSplitTable;
DROP TABLE dbo.TestSplitTable;')
)
/*****************************************************/
SELECT @RunNum = MAX(runnum)+1 FROM dbo.TestResultsTableSplit
SET @RunNum = ISNULL(@RunNum,1)
--SELECT * FROM #TempTable
--SELECT * FROM #TestStrings
--RETURN
/* Get the tests to run */
DECLARE RunTests CURSOR
FOR
SELECT
ID
,TestName
,TestString
FROM
#TestStrings
OPEN RunTests
FETCH NEXT FROM RunTests INTO
@TestID
,@TestName
,@strSQL
SET @i = 1
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT
-- @TestID
-- ,@TestName
-- ,@strSQL
WHILE @i <= 8
BEGIN
SET @RowCount = POWER(10,@i)
IF @RowCount > @SampleSize
BREAK
ELSE
BEGIN
SET ROWCOUNT @RowCount
/* drop and/or create some real tables for test 4 */
IF EXISTS
(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
AND TABLE_NAME='TestSplitTable')
DROP TABLE dbo.TestSplitTable
IF @TestID = 4
BEGIN
SELECT *
INTO dbo.TestSplitTable
FROM #TempTable
ORDER BY NewID()
--SELECT COUNT(*) FROM dbo.TestSplitTable
END
IF EXISTS
(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
AND TABLE_NAME='TestVulnerabilities')
DROP TABLE dbo.TestVulnerabilities
IF EXISTS
(SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
AND TABLE_NAME='TestComputerInfo')
DROP TABLE dbo.TestComputerInfo
IF @TestID = 4
BEGIN
CREATE TABLE dbo.TestVulnerabilities (
[VulnerabilityID] INT IDENTITY(1,1) NOT NULL,
[ComputerID] INT NOT NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (VulnerabilityID))
END
/* drop and recreate the target tables for each test */
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ComputerID] INT NOT NULL,
[ComputerName] NVARCHAR(50) NULL,
127.0.0.1 NVARCHAR(50) NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (ComputerID))
INSERT INTO #TempTable
SELECT *
FROM dbo.TestSampleDataTableSplit
ORDER BY NewID()
--SELECT COUNT(*) FROM #TempTable AS tt
IF OBJECT_ID('tempdb..#computerInfo') IS NOT NULL
DROP TABLE #computerInfo
CREATE TABLE #computerInfo (
[ComputerID] INT NOT NULL,
[ComputerName] NVARCHAR(50) NULL,
127.0.0.1 NVARCHAR(50) NULL,
PRIMARY KEY (ComputerID))
IF OBJECT_ID('tempdb..#vulnerabilities') IS NOT NULL
DROP TABLE #vulnerabilities
CREATE TABLE #vulnerabilities (
[VulnerabilityID] INT IDENTITY(1,1) NOT NULL,
[ComputerID] INT NOT NULL,
[Vulnerability] NVARCHAR(50) NULL,
[ScanDate] DATETIME NULL,
PRIMARY KEY (VulnerabilityID))
SET @strDesc = @TestName+' '+CAST(@RowCount AS VARCHAR(10))
RAISERROR(@strDesc,10,1) WITH NOWAIT;
DBCC FREEPROCCACHE;
/* Start the timer */
SELECT @StartTime = GETDATE()
/* Run the test */
EXEC(@strSQL)
/* Stop the timer and record the test */
SELECT @EndTime = GETDATE()
SET @TestResult = 'Total Time: '+CAST(DATEDIFF(ms,@StartTime,@EndTime)/1000.0 AS VARCHAR(10))
RAISERROR(@TestResult,10,1) WITH NOWAIT;
IF @TestName IS NOT NULL
BEGIN
INSERT INTO dbo.TestResultsTableSplit
SELECT
@RunNum
,@TestName
,DATEDIFF(ms,@StartTime,@EndTime)/1000.0
,@RowCount
,@StartTime
,@EndTime
END
SET @i = @i + 1
SET ROWCOUNT 0
END
END
SET @i = 1
FETCH NEXT FROM RunTests INTO
@TestID
,@TestName
,@strSQL
END
CLOSE RunTests
DEALLOCATE RunTests
SELECT *
FROM dbo.TestResultsTableSplit
ORDER BY RunNum DESC, ID ASC
/*
CREATE FUNCTION [dbo].[svfGenerateRandomNumbers]
(
@StartValue INT
,@EndValue INT
,@ResultType VARCHAR(50) = 'BIGINT'
,@isInclusive BIT = 1
,@NumLength FLOAT
)
RETURNS BIGINT
AS
BEGIN
DECLARE
@Range INT
,@MyNewID UNIQUEIDENTIFIER
,@MyRand FLOAT
,@ReturnVal BIGINT
IF ISNULL(@StartValue,0) = 0 SET @StartValue = 1
IF ISNULL(@EndValue,0) = 0 SET @EndValue = 1
IF @NumLength > 18 SET @NumLength = 18
IF @isInclusive = 1
SET @Range = (@EndValue - @StartValue) + 1
ELSE
SET @Range = (@EndValue - @StartValue)
IF ISNULL(@Range,0) = 0 SET @Range = 1
SET @NumLength = CAST('1'+REPLICATE('0',@NumLength-1) AS FLOAT)
SELECT TOP(1) @MyNewID = MyNewID FROM dbo.iFunction
SELECT TOP(1) @MyRand = MyRand FROM dbo.iFunction
SELECT TOP(1)
@ReturnVal = RandomValue
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
,CAST(FLOOR((CASE
WHEN @ResultType = 'FLOAT' THEN @MyRand * @Range + @StartValue
WHEN @ResultType = 'BIGINT' THEN ABS(CHECKSUM(@MyNewID)) % @Range + @StartValue
END) * @NumLength) AS BIGINT) AS RandomValue
FROM
dbo.Tally AS t1
CROSS JOIN dbo.Tally AS t2
WHERE
t1.n > 0
AND t2.n > 0
) AS r
ORDER BY
RowNum
RETURN @ReturnVal
END
GO
CREATE FUNCTION [dbo].[svfRemoveExcessSpaces]
(
@strText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
BEGIN
DECLARE
@STR NVARCHAR(MAX)
,@xml XML
SET @STR = @strText
SET @STR = REPLACE(@str,CHAR(9),' ') -- convert tabs to spaces
SET @STR =
REPLACE(
REPLACE(
REPLACE(
,' ',' '+CHAR(7))
,CHAR(7)+' ','')
,CHAR(7),'')
RETURN @STR
END
GO
*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply