view to be indexed but has a subquery

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)

view to be indexed but has a subquery

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-07-30 : 17:56:49

Hi Guys I have a view that has a subquery, now this query is super slow to get the data however putting an index on it would help out in spades I am sure. I can not do this because of the darn subquery. What other options do I have here?
View looks like so:



SELECT     ItemId, dbo.NBrightBuyLangMerge(XMLData,
                          (SELECT     TOP (1) XMLData
                            FROM          dbo.NBrightBuy AS NB2
                            WHERE      (NB1.ParentItemId = ItemId) AND (ISNULL(NB1.Lang, N'') <> ''))) AS XMLData, ISNULL(Lang, '') AS Lang, ParentItemId
FROM         dbo.NBrightBuy AS NB1
WHERE     (ISNULL(Lang, N'') <> '')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-30 : 18:07:32

Do you have an index on ParentItemId and also one on ItemId? How about on Lang?

Try this WHERE clause instead: WHERE Lang IS NOT NULL AND Lang <> ''

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

2revup
Posting Yak Master

112 Posts

Posted - 2014-07-30 : 18:29:33

Tried the where, little to no difference.

Also all of the cols are index, itemID being the PK.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-30 : 19:11:22

Please post the execution plan and output when you add SET STATISTICS IO ON.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

2revup
Posting Yak Master

112 Posts

Posted - 2014-07-30 : 20:50:42

You are able to download both stats and the execute plan.
http://www.fileconvoy.com/dfl.php?id=g0080fb6514699f459995361098136401184c47f52

cheers,
Brad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 12:30:06

Please run this and provide the stats io output:

SET STATISTICS IO ON

SELECT ItemId, dbo.NBrightBuyLangMerge(XMLData,
(SELECT TOP (1) XMLData
FROM dbo.NBrightBuy AS NB2
WHERE (NB1.ParentItemId = ItemId) AND (ISNULL(NB1.Lang, N'') <> ''))) AS XMLData, ISNULL(Lang, '') AS Lang, ParentItemId
FROM dbo.NBrightBuy AS NB1
WHERE (ISNULL(Lang, N'') <> '')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-31 : 12:33:44

I just realized you are using a function in that query. Can you post that code?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-31 : 17:45:41

I suggest trying OUTER APPLY, as below.

Btw, in the subquery, should that be "NB1.Lang" or "NB2.Lang"?



SELECT     ItemId, dbo.NBrightBuyLangMerge(XMLData, ca1.XMLData) AS XMLData, ISNULL(Lang, '') AS Lang, ParentItemId
FROM         dbo.NBrightBuy AS NB1
OUTER APPLY (
    SELECT     TOP (1) XMLData
    FROM          dbo.NBrightBuy AS NB2
    WHERE      (NB1.ParentItemId = NB2.ItemId) AND (NB1.Lang > '')
) AS ca1 --                                           ?

WHERE     (Lang > '')

Edit: Changed "CROSS APPLY" to "OUTER APPLY" to allow for no matching row from the subquery.

2revup
Posting Yak Master

112 Posts

Posted - 2014-08-01 : 00:52:28

Here is the function

USE [Protoys]
GO
/****** Object:  UserDefinedFunction [dbo].[NBrightBuyLangMerge]    Script Date: 08/01/2014 14:47:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[NBrightBuyLangMerge](@xmllangdata AS XML,@xmlbasedata AS XML)
RETURNS XML
BEGIN

DECLARE @rtndata AS XML

IF NOT @xmlbasedata IS NULL
BEGIN
	IF NOT @xmllangdata IS NULL
	BEGIN
		SET @xmlbasedata.modify('insert <lang/> as last into /genxml[1]')
		SET @xmlbasedata.modify('insert sql:variable("@xmllangdata") as last into /genxml[1]/lang[1]')
	END
	SET @rtndata = @xmlbasedata
END
ELSE
BEGIN
	-- is not a langauge record so just return the langauge data
	SET @rtndata = ISNULL(@xmllangdata,'')
END

RETURN @rtndata

END

the: AND (NB1.Lang > '') is actully NB1. but I guess NB2. woudl work also being a self join.

Trying the query using the outer apply seems to give me the results in around the same time frame.

2revup
Posting Yak Master

112 Posts

Posted - 2014-08-01 : 00:54:22

owh and here are the stats top 100

(100 row(s) affected)
Table 'NBrightBuy'. Scan count 1, logical reads 662, physical reads 0, read-ahead reads 0, lob logical reads 2068, lob physical reads 0, lob read-ahead reads 0.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources