Table dependencies and query optimization issue

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

SQL Server 2008 Forums

Transact-SQL (2008)

Table dependencies and query optimization issue

Author

Topic

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-12 : 11:19:04

I have an issue with the following query which takes longer than anticipated.


SELECT     COUNT(*)
	FROM         dbo.testIdentifier LEFT OUTER JOIN
                      dbo.swVersion ON dbo.testIdentifier.id = dbo.swVersion.id LEFT OUTER JOIN
                      dbo.loopResult ON dbo.testIdentifier.id = dbo.loopResult.id_testIdentifier LEFT OUTER JOIN
                      dbo.dutLoopConfiguration ON dbo.loopResult.id = dbo.dutLoopConfiguration.id_loopResult LEFT OUTER JOIN
                      dbo.dut ON dbo.dutLoopConfiguration.id_dut = dbo.dut.id LEFT OUTER JOIN
                      dbo.videoSource ON dbo.dutLoopConfiguration.id_videoSource = dbo.videoSource.id LEFT OUTER JOIN
                      dbo.thermalSource ON dbo.dutLoopConfiguration.id_thermalSource = dbo.thermalSource.id LEFT OUTER JOIN
                      dbo.audioSource ON dbo.dutLoopConfiguration.id_audioSource = dbo.audioSource.id LEFT OUTER JOIN
                      dbo.powerSource ON dbo.dutLoopConfiguration.id_powerSource = dbo.powerSource.id LEFT OUTER JOIN
                      dbo.audioError ON dbo.loopResult.id = dbo.audioError.id_loopResult LEFT OUTER JOIN
                      dbo.packetError ON dbo.loopResult.id = dbo.packetError.id_loopResult LEFT OUTER JOIN
                      dbo.psnr ON dbo.loopResult.id = dbo.psnr.id_loopResult LEFT OUTER JOIN
                      dbo.psnrType ON dbo.psnr.id_psnrType = dbo.psnrType.id LEFT OUTER JOIN
                      dbo.registrationTime ON dbo.loopResult.id = dbo.registrationTime.id_loopResult LEFT OUTER JOIN
                      dbo.snr ON dbo.loopResult.id = dbo.snr.id_loopResult LEFT OUTER JOIN
                      dbo.videoError ON dbo.loopResult.id = dbo.videoError.id_loopResult LEFT OUTER JOIN
                      dbo.videoTime ON dbo.loopResult.id = dbo.videoTime.id_loopResult LEFT OUTER JOIN
                      dbo.pin ON dbo.loopResult.id = dbo.pin.id_loopResult
WHERE     (dbo.loopResult.loop > 0) AND 
          (dbo.loopResult.id_testIdentifier = 97 OR  dbo.loopResult.id_testIdentifier = 95 OR dbo.loopResult.id_testIdentifier = 97) AND 
					  (dbo.packetError.totalFrames > 0 OR dbo.packetError.totalFrames IS NULL) AND 
					  --(dbo.packetError.totalPackets > 0 OR dbo.packetError.totalPackets IS NULL) AND 
					  --(dbo.packetError.errPackets >= 0 OR dbo.packetError.errPackets IS NULL) AND 
					  (dbo.psnr.y > 45 OR dbo.psnr.y IS NULL) AND 
					  (dbo.psnr.yCbCr > 50 OR dbo.psnr.yCbCr IS NULL) AND 
					  (dbo.psnr.std BETWEEN -110 AND -50 OR dbo.psnr.std IS NULL) AND 
                      (dbo.videoError.totalFrames > 0 OR dbo.videoError.totalFrames IS NULL) AND 
                      (dbo.audioError.blockCount > 0 OR dbo.audioError.blockCount IS NULL) AND 
                      (dbo.audioError.ber <= 1e-5 OR dbo.audioError.ber IS NULL) AND 
                      (dbo.audioError.bler <= 1e-5 OR dbo.audioError.bler IS NULL) AND 
                      (dbo.pin.ant0 > - 51 OR dbo.pin.ant0 IS NULL) AND 
                      (dbo.pin.ant1 > - 51 OR dbo.pin.ant1 IS NULL) AND 
                      (dbo.pin.ant2 > - 51 OR dbo.pin.ant2 IS NULL) AND 
                      (dbo.pin.ant3 > - 51 OR dbo.pin.ant3 IS NULL) AND 
                      (dbo.pin.ant4 > - 52 OR dbo.pin.ant4 IS NULL) AND
                      (dbo.videoTime.time > 0 OR dbo.videoTime.time IS NULL) AND
                      (dbo.registrationTime.time > 0 OR dbo.registrationTime.time IS NULL)

I narrowed it down that the use of LEFT JOIN in the FROM clause with the IS NULL in the WHERE clause is slowing me down, but unfortunately I can't understand why.

The number of rows in each table is no more then 390 so it's not that much data but still it's very slow than expected, about 7 -9 seconds instead of miliseconds.

Does anyone have an idea?

Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-07-12 : 11:57:32

TheSQL Engine does not do well with OR conditions and you have a lot of them. One approach to try would be to use IsNull to convert Nulls to a value that will pass the other half of your OR operator. For instance:

Change: (dbo.pin.ant0 > - 51 OR dbo.pin.ant0 IS NULL)
To: ISNULL(dbo.pin.ant0, 1000) > - 51 -- NOTE: 1000 is just a sufficiently "big" number

The downside of this technique is that it makes that column non-SARGable which means that any indexes won't be used. However, you probably are accessing a number of columns here that are not being accessed as part of an index. The execution plan should let you infer which columns are being used in the indexes for this statement.

Also, you are requiring values in the WHERE clause for the loopResult table but are using a left join to access the table. Change that to an INNER JOIN.
=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-12 : 12:15:31

Thanks,

I tried your first suggestion and it still didn't make it quicker.

Here is the new query;

use QA_New4
SELECT COUNT(loop)
FROM dbo.loopResult LEFT OUTER JOIN
dbo.dutLoopConfiguration ON dbo.loopResult.id = dbo.dutLoopConfiguration.id_loopResult LEFT OUTER JOIN
dbo.videoSource ON dbo.dutLoopConfiguration.id_videoSource = dbo.videoSource.id LEFT OUTER JOIN
dbo.thermalSource ON dbo.dutLoopConfiguration.id_thermalSource = dbo.thermalSource.id LEFT OUTER JOIN
dbo.audioSource ON dbo.dutLoopConfiguration.id_audioSource = dbo.audioSource.id LEFT OUTER JOIN
dbo.powerSource ON dbo.dutLoopConfiguration.id_powerSource = dbo.powerSource.id LEFT OUTER JOIN
dbo.audioError ON dbo.loopResult.id = dbo.audioError.id_loopResult LEFT OUTER JOIN
dbo.packetError ON dbo.loopResult.id = dbo.packetError.id_loopResult LEFT OUTER JOIN
dbo.psnr ON dbo.loopResult.id = dbo.psnr.id_loopResult LEFT OUTER JOIN
dbo.psnrType ON dbo.psnr.id_psnrType = dbo.psnrType.id LEFT OUTER JOIN
dbo.registrationTime ON dbo.loopResult.id = dbo.registrationTime.id_loopResult LEFT OUTER JOIN
dbo.snr ON dbo.loopResult.id = dbo.snr.id_loopResult LEFT OUTER JOIN
dbo.videoError ON dbo.loopResult.id = dbo.videoError.id_loopResult LEFT OUTER JOIN
dbo.videoTime ON dbo.loopResult.id = dbo.videoTime.id_loopResult LEFT OUTER JOIN
dbo.pin ON dbo.loopResult.id = dbo.pin.id_loopResult
WHERE (dbo.loopResult.loop > 0) AND
(dbo.loopResult.id_testIdentifier = 97) AND
ISNULL(dbo.packetError.totalFrames,1) > 0 AND
--(dbo.packetError.totalPackets > 0 OR dbo.packetError.totalPackets IS NULL) AND
--(dbo.packetError.errPackets >= 0 OR dbo.packetError.errPackets IS NULL) AND
ISNULL(dbo.psnr.y,46) > 45 AND
ISNULL(dbo.psnr.yCbCr, 51) > 50 AND
ISNULL(dbo.psnr.std, -80) BETWEEN -110 AND -50 AND
ISNULL(dbo.videoError.totalFrames,1) > 0 AND
ISNULL(dbo.audioError.blockCount, 1) > 0 AND
ISNULL(dbo.audioError.ber, 1e-6) <= 1e-5 AND
ISNULL(dbo.audioError.bler, 1e-6) <= 1e-5 AND
ISNULL(dbo.pin.ant0, -50) > - 51 AND
ISNULL(dbo.pin.ant1, -50) > - 51 AND
ISNULL(dbo.pin.ant2, -50) > - 51 AND
ISNULL(dbo.pin.ant3, -50) > - 51 AND
ISNULL(dbo.pin.ant4, -50) > - 51 AND
ISNULL(dbo.videoTime.time,1) > 0 AND
ISNULL(dbo.registrationTime.time,1) > 0

Regarding the LEFT OUTER JOIN, I cannot change it to INNER JOIN cause then I don't get any result, always 0.
I guess I'm maybe not using the right query.

What I have is a main table "loopResult" which holds the logic of repetitive test.
Most other tables (lets call them parameter tables) just hold a current state of my test, and they are not aware to which loop they belong. In addition, each parameter table is not aware to the other parameter tables.

The loopResult table issues a new id in each test loop, which I input to each parameter table and in that way I know which parameters were read in a certain loop.

Now I want to go loop by loop and query the different parameters line in the table and then see if all parameters are in the correct limit and count all this lines to get the overall count, which is the test result.

One more exception is that I don't know if a certain parameter table was used in a certain test and in another it was not, so I'm forced to query for all of them each time, and that is way I used the is null to know if a parameter was even input and if not then not to include it as a fail result.

I hope I was clear.

Do you have a better suggestion then my query?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-12 : 13:04:54

You might want to take another look at your join TYPES.. Meaning you have all LEFT OUTER joins, but your where predicates make most of them the same as an INNER JOIN. So, SQL is probably having to do a lot of extra work that it wouldn't need to if the proper join type were used.

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-12 : 13:33:13

That's exactly what I'm trying to understand, I don't understand how to change it, can you maybe elaborate, or point me to where I can understand, preferably with some example?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-12 : 13:45:44

Here is a quick sample that deonstates how the join type a predicate placement affect the results:

DECLARE @A TABLE (ID INT)
INSERT @A (ID) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)

DECLARE @B TABLE (ID INT)
INSERT @B (ID) VALUES (2), (4), (6), (8), (10), (12)


SELECT *
FROM 
    @A AS A
LEFT OUTER JOIN
    @B AS B
    ON A.ID = B.ID
    
SELECT *
FROM 
    @A AS A
LEFT OUTER JOIN
    @B AS B
    ON A.ID = B.ID
    AND B.ID > 5
    
-- These next two produce the same results.    
SELECT *
FROM 
    @A AS A
LEFT OUTER JOIN
    @B AS B
    ON A.ID = B.ID
WHERE
    B.ID > 5
    
SELECT *
FROM 
    @A AS A
INNER JOIN
    @B AS B
    ON A.ID = B.ID
WHERE
    B.ID > 5

Does that help?

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-12 : 14:17:08

Thanks for the detailed example, but my issue is a little different.

I use a key which is primary in loopResult table and is foreign in all parameter tables.

Let's for example say I have the following tables:

testResults
===========
id | loop | id_testIdentifier
1 | 1 | 1
2 | 2 | 1

Pin
===
id_loopResult | val1 | val2 | val3
1 | 12 | 13 | 14
2 | 45 | 46 | 47

Audio
=====
id_loopResult | val1 | val2
1 | 25 | 27
2 | 56 | 59

Video
=====
id_loopResult | val1 | val2 | val3 | val4
/* empty table*/

packets
=====
id_loopResult | val1 | val2
/* empty table*/

Now, I need a query that joins all this tables by id_loopResult and then checks their values, if they pass test limits.

If I use INNER JOINS then because tables video and packets are empty the result will be nothing, because the join will fail to do so because of the non-existing id_loopResult keys in the empty tables.

Is my problem more clear?!

Sorry if I'm messing things up...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-07-12 : 19:56:04

The outer joins should not be a problem per se since you are accounting for NULLs in the WHERE clause. I am assuming that they are necessary in order to satisfy your business requirements.

Does the execution plan point to a particular pain point in your query? If so, can you share some details?

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-13 : 08:42:31

Can someone please instruct me what data should I share here that will bring the big picture of my problem?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-13 : 09:44:06

To resolve problems like this you often just have to pick your way though the query plan.

If you publish the query plan, someone may help you.

As you are asking the optimizer to do a lot, and if your maximum count is not too large,
it may be quicker to try a divide and conquer approach with temp tables. This gives the optimizer a better
chance of producing a reasonable plan. With your original query, try something like:


SELECT dbo.testIdentifier.id
	,dbo.packetError.totalFrames AS PETotalFrames
	,dbo.psnr.y
	,dbo.psnr.yCbCr
	,dbo.psnr.std
	,dbo.videoError.totalFrames AS VETotalFrames
	,dbo.audioError.blockCount
	,dbo.audioError.ber
	,dbo.audioError.bler
	,dbo.pin.ant0
	,dbo.pin.ant1
	,dbo.pin.ant2
	,dbo.pin.ant3
	,dbo.pin.ant4
	,dbo.videoTime.time AS VTTime
	,dbo.registrationTime.time AS RTTime
INTO #temp
FROM dbo.testIdentifier
	JOIN dbo.loopResult
		ON dbo.testIdentifier.id = dbo.loopResult.id_testIdentifier
			AND dbo.loopResult.loop > 0
	LEFT OUTER JOIN dbo.swVersion
		ON dbo.testIdentifier.id = dbo.swVersion.id

	LEFT OUTER JOIN dbo.dutLoopConfiguration
		ON dbo.loopResult.id = dbo.dutLoopConfiguration.id_loopResult
	LEFT JOIN dbo.dut
		ON dbo.dutLoopConfiguration.id_dut = dbo.dut.id
	LEFT JOIN dbo.videoSource
		ON dbo.dutLoopConfiguration.id_videoSource = dbo.videoSource.id
	LEFT OUTER JOIN dbo.thermalSource
		ON dbo.dutLoopConfiguration.id_thermalSource = dbo.thermalSource.id
	LEFT OUTER JOIN dbo.audioSource 
		ON dbo.dutLoopConfiguration.id_audioSource = dbo.audioSource.id
	LEFT OUTER JOIN dbo.powerSource
		ON dbo.dutLoopConfiguration.id_powerSource = dbo.powerSource.id
	LEFT OUTER JOIN dbo.audioError
		ON dbo.loopResult.id = dbo.audioError.id_loopResult
	LEFT OUTER JOIN dbo.packetError
		ON dbo.loopResult.id = dbo.packetError.id_loopResult
	LEFT OUTER JOIN dbo.psnr
		ON dbo.loopResult.id = dbo.psnr.id_loopResult
	LEFT OUTER JOIN dbo.psnrType
		ON dbo.psnr.id_psnrType = dbo.psnrType.id
	LEFT OUTER JOIN dbo.registrationTime
		ON dbo.loopResult.id = dbo.registrationTime.id_loopResult
	LEFT OUTER JOIN dbo.snr
		ON dbo.loopResult.id = dbo.snr.id_loopResult
	LEFT OUTER JOIN dbo.videoError
		ON dbo.loopResult.id = dbo.videoError.id_loopResult
	LEFT OUTER JOIN dbo.videoTime
		ON dbo.loopResult.id = dbo.videoTime.id_loopResult
	LEFT OUTER JOIN dbo.pin
		ON dbo.loopResult.id = dbo.pin.id_loopResult
WHERE dbo.testIdentifier.id IN (97,95)

-- now left with a table scan - no joins
SELECT COUNT(*)
FROM #temp
WHERE (PETotalFrames > 0 OR PETotalFrames IS NULL)
	AND (y > 45 OR y IS NULL)
	AND (yCbCr > 50 OR yCbCr IS NULL)
	AND (std BETWEEN -110 AND -50 OR std IS NULL)
	AND (VETotalFrames > 0 OR VETotalFrames IS NULL)
	AND (blockCount > 0 OR blockCount IS NULL)
	AND (ber <= 1e-5 OR  IS NULL)
	AND (ant0 > - 51 OR ant0 IS NULL)
	AND (ant1 > - 51 OR ant1 IS NULL)
	AND (ant2 > - 51 OR ant2 IS NULL)
	AND (ant3 > - 51 OR ant3 IS NULL)
	AND (ant4 > - 52 OR ant4 IS NULL)
	AND (VTTime > 0 OR VTTime IS NULL)
	AND (RTTime > 0 OR RTTime IS NULL)

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-13 : 10:19:53

My problem is already occurring in the first query.
This query takes approximately 1.5-3 seconds per each testIdentifier I have.
So you can guess what happens when I try to get 10 tests in one query.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-13 : 10:43:09

For anyone to help you, you are going to have to produce a lot more information. Query Plan, test data, row counts etc

Oren.Me
Starting Member

7 Posts

Posted - 2011-07-13 : 11:41:23

Okay, here is a sample of my DB structure that relates to my question.

/*---------------------------------------------------------------*/
CREATE TABLE [dbo].[loopResult](
[id] [int] IDENTITY(1,1) NOT NULL,
[loop] [smallint] NULL,
[id_testIdentifier] [int] NOT NULL,
CONSTRAINT [PK__loopResu__3213E83F4316F928] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/*---------------------------------------------------------------*/
CREATE TABLE [dbo].[videoError](
[id_loopResult] [int] NULL,
[totalFrames] [int] NULL,
[errCrcBlocks] [int] NULL,
[crcBlocksPerFrame] [int] NULL,
[worstFrame] [smallint] NULL,
[timeSpan] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[videoError] WITH CHECK ADD CONSTRAINT [FK__videoErro__id_lo__5070F446] FOREIGN KEY([id_loopResult])
REFERENCES [dbo].[loopResult] ([id])
GO

ALTER TABLE [dbo].[videoError] CHECK CONSTRAINT [FK__videoErro__id_lo__5070F446]
GO

/*---------------------------------------------------------------*/
CREATE TABLE [dbo].[audioError](
[id_loopResult] [int] NULL,
[blockCount] [int] NULL,
[bitError] [smallint] NULL,
[blockError] [smallint] NULL,
[ber] [float] NULL,
[bler] [float] NULL,
[timeSpan] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[audioError] WITH CHECK ADD CONSTRAINT [FK__audioErro__id_lo__4F7CD00D] FOREIGN KEY([id_loopResult])
REFERENCES [dbo].[loopResult] ([id])
GO

ALTER TABLE [dbo].[audioError] CHECK CONSTRAINT [FK__audioErro__id_lo__4F7CD00D]
GO

/*---------------------------------------------------------------*/
CREATE TABLE [dbo].[packetError](
[id_loopResult] [int] NULL,
[totalFrames] [smallint] NULL,
[totalPackets] [smallint] NULL,
[errPackets] [smallint] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[packetError] WITH CHECK ADD CONSTRAINT [FK__packetErr__id_lo__5165187F] FOREIGN KEY([id_loopResult])
REFERENCES [dbo].[loopResult] ([id])
GO

ALTER TABLE [dbo].[packetError] CHECK CONSTRAINT [FK__packetErr__id_lo__5165187F]
GO

Noe here's some data to enter to the first, second and third tables, but not the forth one.

INSERT INTO loopResult(loop,id_testIdentifier) VALUES ('1','1')
INSERT INTO audioError(id_loopResult,blockCount,bitError,blockError,ber,bler) VALUES ('1','16145','0','0','0','0')
INSERT INTO videoError(id_loopResult,totalFrames,errCrcBlocks,crcBlocksPerFrame,worstFrame) VALUES ('1','604','0','6546','0')

INSERT INTO loopResult(loop,id_testIdentifier) VALUES ('2','1')
INSERT INTO audioError(id_loopResult,blockCount,bitError,blockError,ber,bler) VALUES ('2','16288','0','0','0','0')
INSERT INTO videoError(id_loopResult,totalFrames,errCrcBlocks,crcBlocksPerFrame,worstFrame) VALUES ('2','610','0','6548','0')

INSERT INTO loopResult(loop,id_testIdentifier) VALUES ('3','1')
INSERT INTO audioError(id_loopResult,blockCount,bitError,blockError,ber,bler) VALUES ('3','16334','0','0','0','0')
INSERT INTO videoError(id_loopResult,totalFrames,errCrcBlocks,crcBlocksPerFrame,worstFrame) VALUES ('3','611','0','6548','0')

Now, what I want to do is to preform a query that will eventually give me how many loops passed all my test criteria.

each row is constructed from all tables which have the same "id_loopResult" as in loopResult table id column.

For example, if we look at the row we get for id_loopResult = 1 we get

loop 1

id 1

id_testIdentifier 1

blockCount 16145

bitError 0

blockError 0

ber 0

bler 0

timeSpan 0

totalFrames 604

errCrcBlocks 0

crcBlocksPerFrame 6546

worstFrame 0

timeSpan 0

Now, I need a query that, off course, queries for all the rows, that correspond to id_testIdentifier in loopResult table, In our example it is only 2 lines, but in my DB it may get to a total of 400 lines per test, which is not that much in any case.

For this rows I would like to apply a where clause in which I put my filtration by different limits for different parameters I check.

Now, to make things complicated, I need a generic query that queries for all the tables, including the forth one (packetError), which is currently empty.
The point is that many test are ran and I cannot know which parameter is being read in each test.
I want only one query that can give me each time the result "How many test loop passed out of the total loops"
To get the total loops is easy, but to get the how many is my problem.

Is this enough data?

=============UPDATE======================

Thanks for your help guys, I've figured it out.
Not that glamorous for me, but I didn't put indexes.
Well, that comes to show that you should study before you implement, but what the hack, I'm not a DBA and never learned DB nor SQL so I'll give myself a little slack.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources