Slow writing query

Question

Slow writing query

Mark Taylor-212571

Ten Centuries

Points: 1196
More actions
August 22, 2007 at 9:23 am

#178140

I'm looking for some advice on a query that I have written.
Example table that I will work with:
-------------
|CallID|UCID|
=============
| 1230 |aaaa|
| 1230 |bbbb|
| 2520 |bbbb|
| 5555 |bbbb|
| 6665 |cccc|
-------------
Now the first 4 rows are linked and the way you can tell this is by jumping from one column to the other eg:
CallID 1230 has a UCID values of "aaa" and "bbb"
Now look at the CallID's based on the UCID in the previous line you CallIDs of 1230 and 2520
repeat jumping across from one field to another until you get no more new UCID's or CallID's.
At this point I want to make it clear that the table was not written by me but by an external company.
What I'm trying to do is to add a new column that is a grouping column, so that when you query this data you don't have to move from one column to another to find out if the next row is linked or not.
So here is my code to resolve it:
DECLARE @MaxLoop INT
DECLARE @Looper INT
DECLARE @CountUCIDs TINYINT
DECLARE @CountCallIDs TINYINT
DECLARE @InnerLoop TINYINT
DECLARE @StartCount TINYINT
DECLARE @EndCount TINYINT
DECLARE @UCIDStatus TINYINT
DECLARE @CallIDStatus TINYINT
DECLARE @NewGroupID INT
SET @MaxLoop = 0
SET @Looper = 0
SET @InnerLoop = 1
SET @CountUCIDs = 0
SET NOCOUNT ON
DECLARE @NewUCIDVals TABLE (UCID VARCHAR(20) COLLATE Latin1_General_CI_AS NOT NULL)
DECLARE @CallIDVals TABLE (CallID INT NOT NULL)
SELECT @MaxLoop = COUNT(*) FROM dbo.TempLoadRawCmsCallHistory
WHILE @looper < @MaxLoop
BEGIN
--Get a UCID from the loading table
INSERT INTO @NewUCIDVals
SELECT TOP 1 UCID FROM dbo.TempLoadRawCmsCallHistory
--Now find all callID's linked to the UCID
INSERT INTO @CallIDVals
SELECT CallID FROM dbo.TempLoadRawCmsCallHistory WHERE UCID IN(SELECT UCID FROM @NewUCIDVals)
--Now check to see if there are more than one UCID assoicated with CallID
SET @CountUCIDs = 2
IF @CountUCIDs > 1 --There are more records, record them
BEGIN
--poss this is a multi event call need to find any other ucids and other callids
While @InnerLoop < 100
BEGIN
--Remember how many records are the ucid hash table
SET @StartCount = (SELECT COUNT(*) FROM @NewUCIDVals)
--Store all of the UCID's found
INSERT INTO @NewUCIDVals
SELECT UCID FROM dbo.TempLoadRawCmsCallHistory WHERE CallID IN (SELECT CallID FROM @CallIDVals) AND UCID NOT IN (SELECT UCID FROM @NewUCIDVals)
--Now check how many records there are now
SET @EndCount = (SELECT COUNT(*) FROM @NewUCIDVals)
IF @StartCount = @EndCount
BEGIN
SET @UCIDStatus = 1
--Reset the counter as they will be used to check the callID's
SET @StartCount = 0
SET @EndCount = 0
END
ELSE
BEGIN
SET @UCIDStatus = 0
--Reset the counter as they will be used to check the callID's
SET @StartCount = 0
SET @EndCount = 0
END
--Remember how many records are the CallID hash table
SET @StartCount = (SELECT COUNT(*) FROM @CallIDVals)
--Now Check to see if you have any new Callids
INSERT INTO @CallIDVals
SELECT CallID FROM dbo.TempLoadRawCmsCallHistory WHERE UCID IN(SELECT UCID FROM @NewUCIDVals) AND CallID NOT IN (SELECT CallID FROM @CallIDVals)
--Now check how many records there are now
SET @EndCount = (SELECT COUNT(*) FROM @CallIDVals)
IF @StartCount = @EndCount
BEGIN
SET @CallIDStatus = 1
--Reset the counter as they will be used to check the UCID's
SET @StartCount = 0
SET @EndCount = 0
END
ELSE
BEGIN
SET @CallIDStatus = 0
--Reset the counter as they will be used to check the UCID's
SET @StartCount = 0
SET @EndCount = 0
END
IF @CallIDStatus + @UCIDStatus = 2
BEGIN
SET @InnerLoop = 101
SET @CallIDStatus = 0
SET @UCIDStatus = 0
END
ELSE
BEGIN
SET @CallIDStatus = 0
SET @UCIDStatus = 0
SET @InnerLoop = @InnerLoop + 1
END
END
END
--Now you know all of the sections to the call do something
SELECT @NewGroupID = CASE WHEN MAX(GROUPID) IS NULL THEN 1 ELSE MAX(GROUPID) + 1 END FROM TempLoadGroupedCmsCallHistory
INSERT INTO TempLoadGroupedCmsCallHistory
SELECT *, @NewGroupID,0 FROM TempLoadRawCmsCallHistory WHERE UCID IN(SELECT DISTINCT * FROM @NewUCIDVals) AND CallID IN(SELECT DISTINCT * FROM @CallIDVals)
DELETE FROM TempLoadRawCmsCallHistory WHERE UCID IN(SELECT DISTINCT * FROM @NewUCIDVals) AND CallID IN(SELECT DISTINCT * FROM @CallIDVals)
--TRUNCATE TABLE @NewUCIDVals
--TRUNCATE TABLE @CallIDVals
DELETE FROM @CallIDVals
DELETE FROM @NewUCIDVals
SET @InnerLoop = 0
--move to the next call
SET @looper = (@MaxLoop - (SELECT COUNT(*) FROM TempLoadRawCmsCallHistory))
END
--DROP TABLE @NewUCIDVals
--DROP TABLE @CallIDVals
The problem I have is that running a trace whilst the code is executing is showing that the following line is holding the process up badly:
INSERT INTO @NewUCIDVals SELECT UCID FROM dbo.TempLoadRawCmsCallHistory WHERE CallID IN (SELECT CallID FROM @CallIDVals) AND UCID NOT IN (SELECT UCID FROM @NewUCIDVals) --Now check how many records there are now
Duration = avg 119
CPU = avg 119
Reads = avg 252
I have checked the Stats all all have checked the indexes. So any anyone suggest a way of improving the speed of this process

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It would help if you could show an example of how the new column's data should look. Can you take your sample data and show how the data for the new column should look?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 2

Hi John here you go:

---------------------------

|CallID|UCID|GroupID|

===============

| 1230 |aaaa| 12 |

| 1230 |bbbb| 12 |

| 2520 |bbbb| 12 |

| 5555 |bbbb| 12 |

| 6665 |cccc| 13 |

-------------

The group ID is there to group rows of data so when end users are querying data they do not need to write complex code to group all segments they can just utilise the groupid.

Hope this helps

Mark

John Rowan SSC Guru Points: 56440 More actions · Answer 3

When I run your code with your sample data, I do not get the results that you show here. Here's what I get, can you clarify?

CallID UCID GroupID

----------- ---------- -----------

1230 aaaa 1

1230 bbbb 1

2520 bbbb 1

5555 bbbb 1

6665 cccc 2

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

is there an identity key on this table, or is what you show the entire table definition? also, does the initial order of the rows need to remain intact?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

John Rowan SSC Guru Points: 56440 More actions · Answer 5

I'm not sure if this is much more efficient and there is probably a better method to this madness, but here's an example of code that will produce the same results as yours, but with trimmed down logic and looping. The statement that you are saying that this hangs up on may be due to the lack of indexing on your table variables in combination with JOINing it back to your temp tables. How many rows are you working with here?

SET NOCOUNT ON

DECLARE @TempLoadGroupedCmsCallHistory TABLE (CallID int, UCID varchar(10), GroupID int, OtherCol int)

DECLARE @TempLoadRawCmsCallHistory TABLE (CallID int, UCID varchar(10))

INSERT INTO @TempLoadRawCmsCallHistory

SELECT 1230, 'aaaa' UNION ALL

SELECT 1230, 'bbbb' UNION ALL

SELECT 2520, 'bbbb' UNION ALL

SELECT 5555, 'bbbb' UNION ALL

SELECT 6665, 'cccc'

DECLARE @NewUCIDVals TABLE (UCID VARCHAR(20))-- COLLATE Latin1_General_CI_AS NOT NULL)

DECLARE @CallIDVals TABLE (CallID INT NOT NULL)

DECLARE

@NewGroupID INT,

@intRowCount int,

@intOuterCount int

SET @intOuterCount = -1

WHILE @intOuterCount <> 0

BEGIN

DELETE FROM @CallIDVals

DELETE FROM @NewUCIDVals

--get first row

INSERT INTO @NewUCIDVals

SELECT TOP 1 t1.UCID

FROM @TempLoadRawCmsCallHistory t1

LEFT JOIN @TempLoadGroupedCmsCallHistory t2

ON t1.UCID = t2.UCID

WHERE t2.UCID IS NULL

SELECT @intRowCount = @@ROWCOUNT, @intOuterCount = @@ROWCOUNT

WHILE @intRowCount > 0

BEGIN

--Now find all callID's linked to the UCID

INSERT INTO @CallIDVals

SELECT DISTINCT t1.CallID

FROM @TempLoadRawCmsCallHistory t1

INNER JOIN @NewUCIDVals t2

ON t1.UCID = t2.UCID

LEFT JOIN @CallIDVals t3

ON t1.CallID = t3.CallID

WHERE t3.CallID IS NULL

INSERT INTO @NewUCIDVals

SELECT t1.UCID

FROM @TempLoadRawCmsCallHistory t1

INNER JOIN @CallIDVals t2

ON t1.CallID = t2.CallID

LEFT JOIN @NewUCIDVals t3

ON t1.UCID = t3.UCID

WHERE t3.UCID IS NULL

SET @intRowCount = @@ROWCOUNT

END

SELECT @NewGroupID = CASE WHEN MAX(GROUPID) IS NULL THEN 1 ELSE MAX(GROUPID) + 1 END FROM @TempLoadGroupedCmsCallHistory

INSERT INTO @TempLoadGroupedCmsCallHistory

SELECT t1.*, @NewGroupID,0

FROM @TempLoadRawCmsCallHistory t1

INNER JOIN @NewUCIDVals t2

ON t1.UCID = t2.UCID

INNER JOIN @CallIDVals t3

ON t1.CallID = t3.CallID

END

SELECT CallID, UCID, GroupID FROM @TempLoadGroupedCmsCallHistory

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 6

Sorry John

You are correct you will get in the groupid column 1 then two. The value 12, 13 were just random numbers for example purposes only.

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 7

TheSQLGuru

There are no Identity Keys on the tables.

Cheers

Mark

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 8

John

Thank you I will try your code and get back to you.

Cheers

Mark

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 9

Hi John

the table TempLoadRawCmsCallHistory has anything from 191,000 through to 400,000 depending on the number of calls the business receives.

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 10

The main table that contains 191,000 to 400,000 is more than just two columns, I trimmed it down for ease of reading but below is the full table structure with indexes:

This is the raw table before grouping is applied-----

CREATE TABLE [TempLoadRawCmsCallHistory] (

[SOURCEID] [int] NOT NULL ,

[ROWDATE] [int] NOT NULL ,

[CALLID] [int] NOT NULL ,

[SEGMENT] [int] NOT NULL ,

[SEGSTART] [datetime] NOT NULL ,

[SEGSTOP] [datetime] NULL ,

[UCID] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,

[ACWTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ACWTI__18EBB532] DEFAULT (0),

[AGT_RELEASED] [int] NOT NULL CONSTRAINT [DF__TempLoadR__AGT_R__19DFD96B] DEFAULT (0),

[ANSHOLDTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ANSHO__1AD3FDA4] DEFAULT (0),

[ANSLOCID] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ANSLO__1BC821DD] DEFAULT (0),

[ANSLOGIN] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,

[ANSREASON] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ANSRE__1CBC4616] DEFAULT (0),

[ASSIST] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ASSIS__1DB06A4F] DEFAULT (0),

[AUDIO] [int] NOT NULL CONSTRAINT [DF__TempLoadR__AUDIO__1EA48E88] DEFAULT (0),

[CALLING_II] [char] (2) COLLATE Latin1_General_CI_AS NULL ,

[CALLING_PTY] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,

[CONFERENCE] [int] NOT NULL CONSTRAINT [DF__TempLoadR__CONFE__1F98B2C1] DEFAULT (0),

[CONSULTTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__CONSU__208CD6FA] DEFAULT (0),

[CWC1] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[CWC2] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[CWC3] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[CWC4] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[CWC5] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[DA_QUEUED] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DA_QU__2180FB33] DEFAULT (0),

[DIALED_NUM] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,

[DISPIVECTOR] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DISPI__22751F6C] DEFAULT (0),

[DISPOSITION] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DISPO__236943A5] DEFAULT (0),

[DISPPRIORITY] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DISPP__245D67DE] DEFAULT (0),

[DISPSKLEVEL] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DISPS__25518C17] DEFAULT (0),

[DISPSPLIT] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DISPS__2645B050] DEFAULT (0),

[DISPTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DISPT__2739D489] DEFAULT (0),

[DISPVDN] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[DURATION] [int] NOT NULL CONSTRAINT [DF__TempLoadR__DURAT__282DF8C2] DEFAULT (0),

[EQLOCID] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EQLOC__29221CFB] DEFAULT (0),

[EQLOC] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,

[EVENT1] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2A164134] DEFAULT (0),

[EVENT2] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2B0A656D] DEFAULT (0),

[EVENT3] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2BFE89A6] DEFAULT (0),

[EVENT4] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2CF2ADDF] DEFAULT (0),

[EVENT5] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2DE6D218] DEFAULT (0),

[EVENT6] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2EDAF651] DEFAULT (0),

[EVENT7] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__2FCF1A8A] DEFAULT (0),

[EVENT8] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__30C33EC3] DEFAULT (0),

[EVENT9] [int] NOT NULL CONSTRAINT [DF__TempLoadR__EVENT__31B762FC] DEFAULT (0),

[FIRSTIVECTOR] [int] NOT NULL CONSTRAINT [DF__TempLoadR__FIRST__32AB8735] DEFAULT (0),

[FIRSTVDN] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[HELD] [int] NOT NULL CONSTRAINT [DF__TempLoadRa__HELD__339FAB6E] DEFAULT (0),

[HOLDABN] [int] NOT NULL CONSTRAINT [DF__TempLoadR__HOLDA__3493CFA7] DEFAULT (0),

[LASTCWC] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[LASTDIGITS] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,

[LASTOBSERVER] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,

[MALICIOUS] [int] NOT NULL CONSTRAINT [DF__TempLoadR__MALIC__3587F3E0] DEFAULT (0),

[NETINTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__NETIN__367C1819] DEFAULT (0),

[OBSERVINGCALL] [int] NOT NULL CONSTRAINT [DF__TempLoadR__OBSER__37703C52] DEFAULT (0),

[OBSLOCID] [int] NOT NULL CONSTRAINT [DF__TempLoadR__OBSLO__3864608B] DEFAULT (0),

[ORIGHOLDTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ORIGH__395884C4] DEFAULT (0),

[ORIGLOCID] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ORIGL__3A4CA8FD] DEFAULT (0),

[ORIGLOGIN] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,

[ORIGREASON] [int] NOT NULL CONSTRAINT [DF__TempLoadR__ORIGR__3B40CD36] DEFAULT (0),

[SPLIT1] [int] NOT NULL CONSTRAINT [DF__TempLoadR__SPLIT__3C34F16F] DEFAULT (0),

[SPLIT2] [int] NOT NULL CONSTRAINT [DF__TempLoadR__SPLIT__3D2915A8] DEFAULT (0),

[SPLIT3] [int] NOT NULL CONSTRAINT [DF__TempLoadR__SPLIT__3E1D39E1] DEFAULT (0),

[TALKTIME] [int] NOT NULL CONSTRAINT [DF__TempLoadR__TALKT__3F115E1A] DEFAULT (0),

[TKGRP] [int] NOT NULL CONSTRAINT [DF__TempLoadR__TKGRP__40058253] DEFAULT (0),

[TRANSFERRED] [int] NOT NULL CONSTRAINT [DF__TempLoadR__TRANS__40F9A68C] DEFAULT (0),

[QUEUETIME] [int] NULL ,

[RINGTIME] [int] NULL ,

[VDN2] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN3] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN4] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN5] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN6] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN7] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN8] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[VDN9] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,

[UUILEN] [int] NULL ,

[ASAIUUI] [varchar] (192) COLLATE Latin1_General_CI_AS NULL ,

PRIMARY KEY CLUSTERED

(

[SOURCEID],

[ROWDATE],

[CALLID],

[SEGMENT]

) ON [PRIMARY]

GO

CREATE INDEX [TempLoadRawCmsCallHistory_I] ON [dbo].[TempLoadRawCmsCallHistory]([ROWDATE]) ON [PRIMARY]

GO

CREATE INDEX [TempLoadRawCmsCallHistory_SGSTRT] ON [dbo].[TempLoadRawCmsCallHistory]([SEGSTART]) ON [PRIMARY]

GO

CREATE INDEX [TempLoadRawCmsCallHistory_CALLID] ON [dbo].[TempLoadRawCmsCallHistory]([SOURCEID], [CALLID]) ON [PRIMARY]

GO

CREATE INDEX [TempLoadRawCmsCallHistory_UCID] ON [dbo].[TempLoadRawCmsCallHistory]([UCID]) ON [PRIMARY]

GO

When the routine finishes it places data into a mirror table but with one difference there is an additional column named groupid.

Hope this helps

Cheers Mark

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Mark, you didn't answer my second question about whether the order of the rows matters. If I move these rows into another table for processing, can I order them by firstcol/secondcol?

Oh, wait, never mind. I just looked at your FULL table definition. You ARE clustering on 4 columns --> we have both a key and a known order. Let me work on it a bit. May be a while though. I have production server issues at one of my clients at the moment.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service