Repost along with DDL's and sample data.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Repost along with DDL's and sample data.

Author

Topic

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-06 : 02:04:42

Here’s a more in depth breakdown of my problem:

We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is, send that entry along with the result set, if it is not valid, Check that the dongle area code is valid, if dongle area is valid select with the result set, and if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).

I have a table named lnkPBXUser which contains the following:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lnkPBXUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lnkPBXUser]
GO

DDL:
CREATE TABLE [dbo].[lnkPBXUser] (
[RegionalID] [int] NOT NULL ,
[pbxID] [decimal](18, 0) NOT NULL ,
[userID] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

Sample data:
INSERT INTO lnkPBXUser(RegionalID, pbxID, userID)
SELECT 0, 543, 2 UNION ALL
SELECT 0, 10961, 6 UNION ALL
SELECT 0, 1012, 17 UNION ALL
SELECT 0, 16499, 26 UNION ALL
SELECT 0, 14061, 36 UNION ALL
SELECT 0, 16499, 2

I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):

PBXID DailupDT DongleAccessNum CLI RegionalID RegionalDialup
83 8/8/2006 8:58:11 AM T2 UQ 28924 0132493700 0 0800003554
543 8/8/2006 8:55:44 AM T0 UA 33902 0123623500 0 0800003554
1219 8/8/2006 8:59:03 AM T3 ZD 02031 0152958095 0 0800003554
1012 8/8/2006 9:02:54 AM T0 UA 41261 0173011050 0 0800003554
1331 8/8/2006 8:59:57 AM T0 UA 01938 0124604627 0 0800003554
1979 8/8/2006 9:02:52 AM T0 UA 09836 0163751210 0 0800003554
1903 8/8/2006 8:58:41 AM T0 UA 26009 0147175356 0 0800003554
1522 8/8/2006 8:58:54 AM T3 MB 94595 0573912871 0 0800004249
319 8/8/2006 8:51:28 AM T2 ZD 32892 0543375100 0 0800004249
3270 8/8/2006 9:04:26 AM T2 MB 87331 0 0800004249

DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDialupLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDialupLog]
GO

CREATE TABLE [dbo].[tblDialupLog] (
[RegionalID] [int] NOT NULL ,
[PBXID] [int] NULL ,
[DialupDT] [datetime] NULL ,
[DongleAccessNum] [varchar] (64) NULL ,
[CLI] [varchar] (64) NULL ,
[RegionalDialup] [varchar] (50) NULL
) ON [PRIMARY]
GO

Sample data:
INSERT INTO tblDialupLog(PBXID,DailupDT ,DongleAccessNum,CLI,RegionalID,RegionalDialup )
SELECT 83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554' UNION ALL
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2006 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800003554' UNION ALL
SELECT 16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'

You see that the DongleAccessNumber is actually made up of three parts, and it is the middle part (ie.UQ) which i will use to check that the tbldongleArea.DongleAreaCode is valid for that region

I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :

CodeID RegionalID ExtName SubsNDCD LocCD UpdateStatus RegionDesc
7973 1 PRETORIA 012 362 0 NORTH EASTERN REGION
7974 1 HARTEBEESHOEK 012 3012 0 NORTH EASTERN REGION
7975 1 HARTEBEESHOEK 012 3013 0 NORTH EASTERN REGION
7976 1 PRETORIA 017 3014 0 NORTH EASTERN REGION
7977 1 PRETORIA 012 3015 0 NORTH EASTERN REGION

DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCodes]
GO

CREATE TABLE [dbo].[tblCodes] (
[CodeID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[ExtName] [varchar] (64) NULL ,
[SubsNDCD] [varchar] (10) NULL ,
[LocCD] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionDesc] [varchar] (255) NULL
) ON [PRIMARY]
GO

Sample Data:

INSERT INTO tblCodes(CodeID ,RegionalID ,ExtName , SubsNDCD ,LocCD ,UpdateStatus,RegionDesc)
SELECT 7973,1, 'PRETORIA', '012', '362', 0 ,'NORTH EASTERN REGION' UNION ALL
SELECT 7974,1, 'HARTEBEESHOEK ', '012', '3012', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7975,1, 'HARTEBEESHOEK ', '012', '3013', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7976,1, 'PRETORIA', '012', '3014', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7977,1, 'PRETORIA', '017', '3015', 0,'NORTH EASTERN REGION'

I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):

AreaID RegionalID DongleAreaCode AreaDesc UpdateStatus
12 1 UA Oumashoop 0
13 1 UB Pietersburg 0
14 1 UC Warmbad 0 1
15 1 UD Nylstroom 0
16 1 UE Potgietersrus 0
27 1 UF Louis Trichardt 0
28 1 UG Messina 0

DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDongleArea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDongleArea]
GO

CREATE TABLE [dbo].[tblDongleArea] (
[AreaID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[DongleAreaCode] [varchar] (5) NULL ,
[AreaDesc] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL
) ON [PRIMARY]
GO

Sample Data:
INSERT INTO tblDongleArea(AreaID,RegionalID,DongleAreaCode,AreaDesc,UpdateStatus)
SELECT 12,1, 'UA', Oumashoop, 0 UNION ALL
SELECT 13, 1, 'UB', 'Pietersburg', 0 UNION ALL
SELECT 14, 1 ,'UC' ,'Warmbad', 0 UNION ALL
SELECT 15, 1, 'UD', 'Nylstroom', 0 UNION ALL
SELECT 16, 1, 'UE', 'Potgietersrus', 0 UNION ALL
SELECT 27, 1, 'UF', 'Louis Trichardt', 0 UNION ALL
SELECT 28, 1, 'UG', 'Messina', 0

I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:

RegionalID RegionalDialup Region UpdateStatus RegionCode LocalRegion
1 0800003554 North Eastern 0 1 0
2 0800005027 Gauteng 0 2 0
3 0800006194 Eastern 0 3 0
4 0800004249 Central 0 4 0
5 0800201859 Southern 0 5 0
6 0800201989 Western 0 6 0
7 0800113515 HO 1 0 1
8 0800222204 Tellumat 0 7 0

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegionNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegionNumbers]
GO

CREATE TABLE [dbo].[tblRegionNumbers] (
[RegionalID] [int] NOT NULL ,
[RegionalDialup] [varchar] (255) NULL ,
[Region] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionCode] [int] NULL ,
[LocalRegion] [int] NULL ,
) ON [PRIMARY]
GO

INSERT INTO tblRegionNumbers(RegionalID ,RegionalDialup,Region,UpdateStatus,RegionCode,LocalRegion)
SELECT 1,'0800003554', 'North Eastern', 0, 1, 0 UNION ALL
SELECT 2, '0800005027' ,'Gauteng', 0 ,2, 0 UNION ALL
SELECT 3, '0800006194','Eastern', 0, 3, 0 UNION ALL
SELECT 4, '0800004249' ,'Central', 0, 4, 0 UNION ALL
SELECT 5, '0800201859','Southern', 0 ,5, 0 UNION ALL
SELECT 6, '0800201989' ,'Western' 0, 6, 0 UNION ALL
SELECT 0, '0800113515', 'HO', 1, 0, 1 UNION ALL
SELECT 8, '0800222204', 'Tellumat', 0, 7, 0

Ok, I am dealing with the lnkPBXUser table at the moment,

I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)

If it does exist, then it is part of the fourth region and should be returned in the result set.

If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.

If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.

If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.

So from the above tables an expected result would be:

RegionalID pbxID userID
0 1012 17
0 543 2

Please assist, it would be greatly appreciated.
Regards
SQLJunior

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 02:25:25

This?

SELECT		u.RegionalID,
		u.PbxID,
		u.UserID
FROM		lnkPBXUser AS u
INNER JOIN	tblDialupLog AS l ON l.pbxID = u.pbxID AND l.RegionalID = u.RegionalID
LEFT JOIN	tblCodes AS c ON l.CLI LIKE c.SubsNDCD + c.LocCD + '%'
LEFT JOIN	tblDongleArea AS a ON L.DongleAccessNum LIKE '%' + a.DongleAreaCode + '%'
WHERE		c.CodeID IS NOT NULL
		OR a.AreaID IS NOT NULL
		OR l.RegionalID = '080003554'
ORDER BY	u.RegionalID,
		u.PbxID,
		u.UserID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 02:30:36

Depending on your business rules, you may want to throw in a

GROUP BY	u.RegionalID,
		u.PbxID,
		u.UserID

too.

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-06 : 02:44:23

That is perfect, i did a couple of tests and it works wonderfully!

Thank you so much Peter, much obliged.

P.S. At least now i know which procedure to follow when posting a question.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 02:45:57

Great! Take that as a lesson.
Good luck.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 02:47:50

At some point you also may want to replace the WHERE above to this

WHERE		CASE
			WHEN c.CodeID IS NOT NULL THEN 1
			WHEN a.AreaID IS NOT NULL THEN 1
			WHEN l.RegionalID = '080003554' THEN 1
			ELSE 0
		END = 1

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-06 : 02:54:43

lol, i will definitely take that as a lesson...

and thanks again for all your help!
Have an awesome one!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 03:03:30

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 03:49:31

[code]SELECT u.RegionalID,
u.PbxID,
u.UserID
FROM lnkPBXUser AS u
INNER JOIN tblDialupLog AS l ON l.pbxID = u.pbxID AND l.RegionalID = u.RegionalID
LEFT JOIN tblCodes AS c ON l.CLI LIKE c.SubsNDCD + c.LocCD + '%'
LEFT JOIN tblDongleArea AS a ON L.DongleAccessNum LIKE '%' + a.DongleAreaCode + '%'
WHERE CASE
WHEN c.CodeID IS NOT NULL THEN 1
WHEN a.AreaID IS NOT NULL THEN 1
WHEN l.RegionalID = '080003554' THEN 1
ELSE 0
END = 1
GROUP BY u.RegionalID,
u.PbxID,
u.UserID
ORDER BY u.RegionalID,
u.PbxID,
u.UserID[/code]
Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-06 : 05:10:50

Hi Peter

I tried the above but i am getting the following syntax error:

Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHEN'.

I have tried a few alternatives with no luck, any ideas?

Also, the actual lnkPBXUser contains about 320 rows, the other tables i need to run the query on are even bigger, I am now running your initial query (with the GROUP BY statement implemented) on the actual lnkPBXUser table (i was using a test db previously), it has been running for the past hour and 40 mins, and still busy... any tips on how to improve performance?

Thanking you,
SQLJunior

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 05:25:32

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 05:32:44

Performance problems due to the LIKEs with % wildcard char?
Add adequate indexes. But that will help no much for you due to bad database design.

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-07 : 02:24:24

Hi Peter,

I have noticed that when i try to do a simple Select * from tblDialupLog, it takes about 30 mins to process, this is very obviously where my problem lies, i will add indexes and see what happens.

Thanks again for all your help and advice.

tc...SQLJunior

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 04:38:43

Well spotted!

Add an index over pbxID and RegionalID.

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-07 : 05:53:22

thnx!... it managed to execute in twenty minutes now.

I removed data which i definitely did not have to search through (ie. all regions except 0) from the tables which needed processing and added the indexes.

much better indeed!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 05:57:38

How many records are there in tblDialupLog?

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-07 : 07:09:54

At the moment, 9776582 records.
By the time i do the migration it will probably be more, because dialups are done on a daily basis, and new entries are added.

why do you ask?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 07:43:17

Beacuse these two JOINs are killing your performance!

LEFT JOIN	tblCodes AS c ON l.CLI LIKE c.SubsNDCD + c.LocCD + '%'
LEFT JOIN	tblDongleArea AS a ON L.DongleAccessNum LIKE '%' + a.DongleAreaCode + '%'

I am thinking of a way to enhance performance.

Maybe add a new column in tblCodes? With SubsNDCD + LocCD + '%' value. Then an index over that will gain CLI index!
It is worse with DongleAccessNum. Maybe extracting the two-digit number to a new column and put an index over that?
And then an index over DongleAreaCode would match that very fast.

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-07 : 07:56:34

ok sounds like a definite plan, maybe i could just create these columns and indexes at the beginning of my script and delete them after?

Will using the CHARINDEX function as shown below have a positive effect on my execution performance?

LEFT JOIN ON CHARINDEX(c.SubsNDCD + c.LocCD, l.CLI) > 0
LEFT JOIN tblDongleArea AS a ON L.DongleAccessNum LIKE '%' + a.DongleAreaCode + '%'

thanking you...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 07:58:36

No.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-07 : 08:06:51

quote:

CASE
WHEN c.CodeID IS NOT NULL THEN 1
WHEN a.AreaID IS NOT NULL THEN 1
WHEN l.RegionalID = '080003554' THEN 1
ELSE 0
END = 1

I am curious -- why are you recommending this? Did you find it to be more efficient rather than simply using some OR logic in the WHERE clause? I would be very surprised. Then again, maybe in this case it doesn't matter since I doubt any indexes are being used ...

- Jeff
http://weblogs.sqlteam.com/JeffS

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources