Need help with modified query - SQL Server Forums

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

Need help with modified query

Author

Topic

topazsparrow
Starting Member

7 Posts

Posted - 2011-08-31 : 18:23:53

Essentially the following query runs on a system that generates internet usage reports for our network. It displays the 95'th percentile for the given fields. We need it to show that information as well as another field (In_TotalBytes) stored in another table (InterfaceTraffic). I've added the field to the select statement and created an Inner Join for it. I'm very new to SQL though so I'm not sure if I did something wrong here. The report doesn't run, it just times out and I need to determine if it's my sql query or a bug with our server software.

I've marked the changes I made in bold. It worked great before I made those changes. Any insight or help is greatly appreciated!


DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -3, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

set nocount on
create table #tmpJoin (fromDate datetime, toDate datetime)
insert into #tmpJoin values(@StartDate,@EndDate)
set nocount off

SELECT
Nodes.NodeID,
Interfaces.Comments AS Comments,
Nodes.Caption AS NodeName,     
Interfaces.InBandwidth AS Recv_Bandwidth,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95,
InterfaceTraffic.In_TotalBytes

FROM Nodes 

INNER JOIN InterfaceTraffic
                ON (Nodes.NodeID = InterfaceTraffic.NodeID)

INNER JOIN Interfaces 
	ON (Nodes.NodeID = Interfaces.NodeID) 

INNER JOIN	
(
	SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
	FROM (
		SELECT DISTINCT A.InterfaceID
		FROM dbo.InterfaceTraffic A
		JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
	) AS AA
) as RESULT_IN
	ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

INNER JOIN	
(
	SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
	FROM (
		SELECT DISTINCT A.InterfaceID
		FROM dbo.InterfaceTraffic A
		JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
	) AS AA
) as RESULT_OUT
	ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)

INNER JOIN	
(
	SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
	FROM (
		SELECT DISTINCT A.InterfaceID
		FROM dbo.InterfaceTraffic A
		JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
	) AS AA
) as RESULT_MAX
	ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)

WHERE (
   (Nodes.IP_Address = 'x.x.x.x') AND 
   (Interfaces.InterfaceAlias LIKE '%Telus%')) OR 
  (
   (Nodes.IP_Address = 'x.x.x.x') AND 
   (Interfaces.WANFeed = 'BI')) OR 
  (
   (Nodes.IP_Address = 'x.x.x.x') AND 
   (Interfaces.WANFeed = 'BI')) OR 
  (
   (Nodes.IP_Address = 'x.x.x.x') AND 
   (Interfaces.WANFeed = 'BI'))
ORDER BY MAXbps_95 DESC

Drop table #tmpJoin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-31 : 18:45:41

Is the join condition indexed?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 00:05:33

how big is the InterfaceTraffic table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

topazsparrow
Starting Member

7 Posts

Posted - 2011-09-01 : 11:11:21

@Tkizer I'm not sure, I'm new to SQL and I'm not familiar with that term, google hasn't been overly helpful. What should I be aware of in that regard?

@visakh16 It's probably very large as it stores traffic data for thousands of interfaces. I've never had a problem accessing it in other reports though.

Essentially I'm just wondering if there is an obvious flaw in the SQL query that I'm not seeing or if this is a bug on the server end.

Thanks for the help so far!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-01 : 11:29:49

Hi.
"how big is the InterfaceTraffic table?"
Try running


SELECT COUNT(*) FROM InterfaceTraffic (NOLOCK)

To get an idea of the size of the table.

"Is the join condition indexed?"
You are making an INNER JOIN between InterfaceTraffic and Nodes on the NodeID column in each table.

An INDEX is just like an index in a phone book - it helps sqlserver find the rows that match a particular entry. Say for NodeID = 24.

If there is no index on the table then the only thing that sql server can do to find all rows where NodeID = 24 is to SCAN the whole table looking at all the NodeID's If there is an INDEX then sql server just needs to look up the right pages. This is called SEEKing.

You can find out if there is an index by going to management studio. Expand the tables and then look in the Indexes folder for that table.

An index will speed up the join considerably. (possibly by hundreds of times).

NodeID on the table Nodes is probably the PRIMARY key of the Nodes table and probably has an index (generated by default).

NodeID on the table InterfaceTraffic is probably a FOREIGN KEY referencing Nodes.NodeID a foreign key does *NOT* have an index by default. you have to add them manually.

To create an index for the join you would issue this command:


CREATE NONCLUSTERED INDEX IX_InterfaceTraffic_NodeID ON InterfaceTraffic (NodeID)

Do all your testing on this in a safe place. Don't play with indexes in production unless you really need to.

Here is some reading to get you started.:
http://www.petri.co.il/introduction-to-sql-server-indexes.htm

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

topazsparrow
Starting Member

7 Posts

Posted - 2011-09-02 : 14:13:54

Thanks Charlie!

I looked into it and the InterfaceTraffic table is already indexed with three different main keys. (InterfaceID, DateTime, and archive)

I ran the count, there are 59054184 entries.

After running the same query through the SQL manager it spat out a bunch of identical errors on varying lines:

MSG 102, Level 15, State 1
Incorrect syntax near " ", Line 14.

Any ideas? The syntax looks fine to me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-02 : 14:27:21

So you are missing an index on NodeID then, hence your problem (or at least one of them).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

topazsparrow
Starting Member

7 Posts

Posted - 2011-09-02 : 15:47:22

I tried joining it with known indexed fields like so:

INNER JOIN InterfaceTraffic
ON (Interfaces.interfaceID = InterfaceTraffic.interfaceID)

To the same effect. :(

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-02 : 16:06:58

Why don't you add the index that we are suggesting? You can't just join it to another column and get desired results, that makes no sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

topazsparrow
Starting Member

7 Posts

Posted - 2011-09-02 : 18:11:33

I'm hesitant to do it on the production environment and I'm waiting for test environment access.

Oddly enough when I run the report 'x.x.x.x' in the ip_address field the report runs fine, it's only when I put valid IP's in there that this problem happens.

Thanks for the help so far though, I appreciate your time and effort.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-03 : 03:11:44

Well that makes sense. if that query results in very few rows in one or more tables (or none at all) then the rest of the joins will be fast!.

You have 59 million entries in that table and sql server has no way to satisfy the join condition rather than SCANNING that table (and you hope it will only do that once).

An index over that column should improve this considerably.

But also you are doing different permutations of this derived table



INNER JOIN	
(
	SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
	FROM (
		SELECT DISTINCT A.InterfaceID
		FROM dbo.InterfaceTraffic A
		JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
	) AS AA
) as RESULT_IN
	ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

for different aggregations. This isn't optimal! Especially if you end up scanning that table every time.

IF you want a kick ass query for this then:
1) Post the table definitions.
2) Post some sample data
3) Post the desired results

people here just can't help themselves when there is a query to optimise.

You'll get a much nicer query that will perform a lot better.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-03 : 19:21:37

quote:
Originally posted by topazsparrow

I'm hesitant to do it on the production environment and I'm waiting for test environment access.

Oddly enough when I run the report 'x.x.x.x' in the ip_address field the report runs fine, it's only when I put valid IP's in there that this problem happens.

Thanks for the help so far though, I appreciate your time and effort.

Let us know when you've added the index. Until then there's really nothing we can do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources