dta yields 0% improvement (and query optimization)

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 2005 Forums

Transact-SQL (2005)

dta yields 0% improvement (and query optimization)

Author

Topic

luj
Starting Member

1 Post

Posted - 2009-06-08 : 17:08:56

I've been trying to speed up a set of queries in SQL Server 2005 to no avail.

The script I've written combines the use of both static tables which contain many rows of relational data and temporary tables which are created/destroyed within a single execution of the script.

For statements that only deal with temporary tables the advisor says "Statement does not reference any tables" (which makes sense I suppose). The advisor completes with "success" on all steps but unfortunately reports an "estimated improvement" of 0%.

I tried passing the script as a workload, I tried creating a trace file, both yield the same result.

It may be doing its job the way it is supposed to; that is, there may not be any way to help the performance.

However, if there is anything to suggest I try, please let me know.

On the other hand...

If the dta is in fact doing its job right, then perhaps the statement which I believe gives me the biggest hit in performance can be optimized:

(The static portion of the database is pretty simple; it contains relationships between Words, Titles, Articles, and Categories. The ArtCat table stores the relationship between Article IDs and Category IDs, TitWord table stores the relationship between Title IDs and Word IDs, etc... The only temporary table in use is #QueryWords which contains WIDs of interest.)


WHILE @i > 1
BEGIN
	INSERT INTO #Quads
	SELECT DISTINCT #QueryWords.WID, TACs.TID, TACs.AID, TACs.CID FROM
		(SELECT TAs.TID, TAs.AID, ArtCat.CID FROM
			(SELECT Ts.TID, TA.AID FROM
				(SELECT DISTINCT TitWord.TID FROM TitWord INNER JOIN #QueryWords ON TitWord.WID = #QueryWords.WID) AS Ts
				INNER JOIN TitArt AS TA ON Ts.TID = TA.TID
				WHERE
				(
					(SELECT COUNT(*) FROM TitWord WHERE TitWord.TID = Ts.TID AND TitWord.WID IN (SELECT WID FROM #QueryWords)) +
					(SELECT COUNT(*) FROM ArtWord WHERE ArtWord.AID = TA.AID AND ArtWord.WID IN (SELECT WID FROM #QueryWords)
						AND ArtWord.WID NOT IN
						(SELECT DISTINCT TW.WID FROM TitWord AS TW INNER JOIN #QueryWords AS QW ON TW.WID = QW.WID WHERE TW.TID = Ts.TID)
					)
				) = @i
			) AS TAs		
			INNER JOIN ArtCat ON TAs.AID = ArtCat.AID) AS TACs
		CROSS JOIN #QueryWords;
	
	IF @@ROWCOUNT > 0
		BREAK
	
	SET @i = @i - 1;
END

Any help is much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 03:30:25

Please provide some sample data and expected output.
I think it's this part that is the problematic one:

(
					(SELECT COUNT(*) FROM TitWord WHERE TitWord.TID = Ts.TID AND TitWord.WID IN (SELECT WID FROM #QueryWords)) +
					(SELECT COUNT(*) FROM ArtWord WHERE ArtWord.AID = TA.AID AND ArtWord.WID IN (SELECT WID FROM #QueryWords)
						AND ArtWord.WID NOT IN
						(SELECT DISTINCT TW.WID FROM TitWord AS TW INNER JOIN #QueryWords AS QW ON TW.WID = QW.WID WHERE TW.TID = Ts.TID)
					)
				) = @i

If I interpret the code correctly you sum all matches from ArtWord table (red part), plus the sum of all matches from TitWord table (green) that not already has been summed by ArtWord table with respect to wID value?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources