June 25, 2012 at 1:50 am
Hi,
Please bear with me if the post is too long. I'll try my best to make you guys understand my requirement.
I have a table which has 43118 rows of data. It holds Data related to Library of a school. The table is as follows:
DDL:
CREATE TABLE [dbo].[LibraryInfoMaster](
[BookCode] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[DDC] [varchar](50) NOT NULL,
[MaterialTypeId] [smallint] NOT NULL,
[BookName] [varchar](300) NOT NULL,
[Curriculum] [varchar](50) NULL,
[NewArrivalDisplayStart] [datetime] NULL,
[NewArrivalDisplayEnd] [datetime] NULL,
[Remarks] [nvarchar](150) NULL,
[ShowToStudent] [tinyint] NOT NULL,
[ReferenceBook] [tinyint] NULL,
[CreatedBy] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedBy] [bigint] NOT NULL,
[LastUpdation] [datetime] NOT NULL,
[IsDeleted] [tinyint] NOT NULL,
[DeletedBy] [bigint] NULL,
[DeletedOn] [datetime] NULL,
[PlaceofPublication] [nvarchar](300) NULL,
[YearofPublication] [varchar](4) NULL,
[CollationName] [varchar](100) NULL,
[BookNumber] [varchar](200) NULL,
[IsNewArrival] [smallint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LibraryInfoMaster] WITH CHECK ADD CONSTRAINT [FK_LibraryInfoMaster_LibraryMaterialTypeMaster] FOREIGN KEY([MaterialTypeId])
REFERENCES [dbo].[LibraryMaterialTypeMaster] ([MaterialTypeId])
GO
ALTER TABLE [dbo].[LibraryInfoMaster] CHECK CONSTRAINT [FK_LibraryInfoMaster_LibraryMaterialTypeMaster]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] WITH CHECK ADD CONSTRAINT [FK_LibraryInfoMaster_MemberMaster] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[MemberMaster] ([MemberId])
GO
ALTER TABLE [dbo].[LibraryInfoMaster] CHECK CONSTRAINT [FK_LibraryInfoMaster_MemberMaster]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] WITH CHECK ADD CONSTRAINT [FK_LibraryInfoMaster_MemberMaster1] FOREIGN KEY([UpdatedBy])
REFERENCES [dbo].[MemberMaster] ([MemberId])
GO
ALTER TABLE [dbo].[LibraryInfoMaster] CHECK CONSTRAINT [FK_LibraryInfoMaster_MemberMaster1]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] WITH CHECK ADD CONSTRAINT [FK_LibraryInfoMaster_MemberMaster2] FOREIGN KEY([DeletedBy])
REFERENCES [dbo].[MemberMaster] ([MemberId])
GO
ALTER TABLE [dbo].[LibraryInfoMaster] CHECK CONSTRAINT [FK_LibraryInfoMaster_MemberMaster2]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] ADD CONSTRAINT [DF_LibraryInfoMaster_ShowToStudent] DEFAULT (0) FOR [ShowToStudent]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] ADD CONSTRAINT [DF_LibraryInfoMaster_ReferenceBook] DEFAULT (0) FOR [ReferenceBook]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] ADD CONSTRAINT [DF_LibraryInfoMaster_LastUpdation] DEFAULT (getdate()) FOR [LastUpdation]
GO
ALTER TABLE [dbo].[LibraryInfoMaster] ADD CONSTRAINT [DF_LibraryInfoMaster_IsDeleted] DEFAULT (0) FOR [IsDeleted]
GO
Sample Data:
Insert Into LibraryInfoMaster Values(1,0,18,'New Living Science For Class 8th','General',NULL,NULL, '', 0,0,1,'2011-05-02 14:01:51.633',1,'2011-05-02 14:01:51.633',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(2,0,19,'Getting Ahead In Social Studies 5','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:09.150',1,'2011-05-02 14:17:09.150',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(3,0,19,'Vyakaran Sopaan For Class 7th','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:15.367',1,'2011-05-02 14:17:15.367',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(4,0,18,'Sachitra Hindi Vyakaran Tatha Rachna For Class 6','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:16.510',1,'2011-05-02 14:17:16.510',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(5,0,18,'Science And Technology : A Textbook For Class 9','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:17.633',1,'2011-05-02 14:17:17.633',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(6,0,20,'Pankhudiyon Hindi Praveshika','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:18.790',1,'2011-05-02 14:17:18.790',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(7,0,20,'Inkheart Farid`s Story','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:20.010',1,'2011-05-02 14:17:20.010',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(8,0,18,'Set Of Solutions : A Textbook Of Biotechnology For Class 11','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:21.307',1,'2011-05-02 14:17:21.307',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(9,0,21,'Natural Home Remedies For Common Ailments','General',NULL,NULL, '', 0,1,1,'2011-05-02 14:17:22.557',1,'2011-05-02 14:17:22.557',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
Insert Into LibraryInfoMaster Values(10,0,21,'Thirteen : 13 Tales Of Horror By 13 Masters Of Horror','General',NULL,NULL, 0,1,1,'2011-05-02 14:17:23.853',1,'2011-05-02 14:17:23.853',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
There is a function that selects the uses 'BookCode' as an input and finds all the AuthorNames from another Table. I use a simple Stuff Query to get the Author Names as Comma Delimited String from the table. The query is as follows:
Select Distinct LTrim(Rtrim(STUFF((Select ',' + AuthorName From View_LibraryBookAuthor Where
BookCode = @BookCode For XML Path('')), 1, 1, '')))
From View_LibraryBookAuthor
The above query when run as a single query on the whole 43118 rows, runs in 5 seconds.
But the business requirement here is that I have to wrap it in a function and use various versions of the above Stuff Query like: by changing Author name to AuthorId/PublisherId/PublisherName depending upon certain conditions.
When I do this and then call the Function for the 43118 'bookCodes' of the above table, it takes ages to execute. Normally goes upto 20 + minutes.
Is there a better way to do this??....Looking forward to your help.
June 25, 2012 at 2:31 am
O.K. no replies yet...that means I did something wrong.
I've simplified the requirement further.
The following query runs in 4 seconds on the above mention table having 40,000+rows bringing back the same resultset of around 40,000+.
Select LTrim(Rtrim(STUFF((Select ',' + AuthorName From View_LibraryBookAuthor As a Where a.BookCode = b.BookCode For XML Path('')), 1, 1, '')))
From LibraryInfoMaster As b
It matches the BookCodes from LibraryInfoMaster table(mentioned above) with BookCodes in a view named 'View_LibraryBookAuthor'.
Now I wrap it as a function as follows:
Create Function ABC(@BookCode BigInt)
Returns Varchar(4000)
Begin
Declare @msg Varchar(4000)
Set @msg = (Select LTrim(Rtrim(STUFF((Select ',' + AuthorName From View_LibraryBookAuthor Where BookCode = @BookCode For XML Path('')), 1, 1, ''))))
Return @msg
End
Then I call this function to get the AuthorNames for BookCodes in the LibraryInfoMaster table(mentioned above) as follows:
Select dbo.ABC(BookCode) From LibraryInfoMaster
This increases the Execution Time and the query takes more than 20 minutes to Execute.
Is there a better way to select the Author names for all bookcodes??
June 25, 2012 at 2:32 am
Can you post the full DDL and sample data.
The table references other tables in foreign key constrains but you have not provided them.
Also the query references a view which you have not provided the DDL for.
Also include any indexes which are on the tables and view.
June 25, 2012 at 2:42 am
vinu512 (6/25/2012)
O.K. no replies yet...that means I did something wrong.I've simplified the requirement further.
The following query runs in 4 seconds on the above mention table having 40,000+rows bringing back the same resultset of around 40,000+.
Select LTrim(Rtrim(STUFF((Select ',' + AuthorName From View_LibraryBookAuthor As a Where a.BookCode = b.BookCode For XML Path('')), 1, 1, '')))
From LibraryInfoMaster As b
It matches the BookCodes from LibraryInfoMaster table(mentioned above) with BookCodes in a view named 'View_LibraryBookAuthor'.
Now I wrap it as a function as follows:
Create Function ABC(@BookCode BigInt)
Returns Varchar(4000)
Begin
Declare @msg Varchar(4000)
Set @msg = (Select LTrim(Rtrim(STUFF((Select ',' + AuthorName From View_LibraryBookAuthor Where BookCode = @BookCode For XML Path('')), 1, 1, ''))))
Return @msg
End
Then I call this function to get the AuthorNames for BookCodes in the LibraryInfoMaster table(mentioned above) as follows:
Select dbo.ABC(BookCode) From LibraryInfoMaster
This increases the Execution Time and the query takes more than 20 minutes to Execute.
Is there a better way to select the Author names for all bookcodes??
Check the estimated plan, but I'd guess that since there's no correlation between the function and the table, you're getting a cross join between them. If this is the case, you could solve it by introducing a parameter to the function so that it operates on a result set restricted by the parameter - probably a key in the table LibraryInfoMaster.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 2:46 am
Right off the bat, I would recommend to try converting the multi-statement function into an in-line TVF. In-line TVFs offer better performance when compared to multi-statement TVFs. Here's a BOL reference: http://msdn.microsoft.com/en-us/library/ms186755.aspx
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
June 25, 2012 at 4:35 am
Hi guys,
Thanks for all the replies. I solved the problem. Well, technically... I didn't solve it, I took a Work around.
My Procedure had the following Query:
SELECT Distinct a.BookCode, b.MaterialType, a.BookName,
dbo.FN_GetLibraryBookAuthorPublisherNameNew(c.AccessionNo,a.BookCode,'AuthorName') AS BookAuthorName,
dbo.FN_GetLibraryBookAuthorPublisherNameNew(c.AccessionNo,a.BookCode,'PublisherName') AS BookPublisherName,a.DDC
FROM dbo.LibraryInfoMaster As a INNER JOIN
dbo.LibraryMaterialTypeMaster As b ON a.MaterialTypeId = b.MaterialTypeId INNER JOIN
dbo.LibraryBookDetails As c ON a.BookCode = c.BookCode
Where a.BookName Like 'Sachitra Hindi Vyakaran Tatha Rachna For Class 6%' AND
dbo.FN_GetLibraryBookAuthorPublisherName(a.BookCode,'AuthorName') Like '%Prakash, Rajendra' and
dbo.FN_GetLibraryBookAuthorPublisherName(a.BookCode,'PublisherName') Like 'Geeta Publihing House, Delhi' and
a.IsDeleted = 0 And b.IsDeleted = 0 And b.IsBookPeriodical = 0
Order By a.DDC, b.MaterialType, a.BookName
In this Query the Function will be called for every row in the table Library Info Master(ie: 4,00,000 * 4 times). That is why it ws taking 16 minutes to execute. That is not a smart thing, I know.
But, I was stumped because I thought that I need to have a Function to select data via a Select Query.
But then I just removed the Functions part from the query and added the Stuff Query directly to the above Query as follows:
SELECT Distinct dbo.LibraryInfoMaster.BookCode, dbo.LibraryMaterialTypeMaster.MaterialType, dbo.LibraryInfoMaster.BookName,
LTrim(Rtrim(STUFF((Select ',' + x.AuthorName From LibraryBookAuthorMaster As x,LibraryBookAuthor As y,LibraryBookDetails As z
Where x.AuthorId=y.AuthorId And y.AccessionNo=z.AccessionNo And z.BookCode=dbo.LibraryInfoMaster.BookCode
And z.AccessionNo=dbo.LibraryBookDetails.AccessionNo For Xml Path('')), 1, 1, ''))) As BookAuthorName,
LTrim(Rtrim(STUFF((Select ',' + x.PublisherName From LibraryBookPublisherMaster As x,LibraryBookPublisher As y,LibraryBookDetails As z
Where x.PublisherId=y.PublisherId And y.AccessionNo=z.AccessionNo And z.BookCode=dbo.LibraryInfoMaster.BookCode
And z.AccessionNo=dbo.LibraryBookDetails.AccessionNo For Xml Path('')), 1, 1, ''))) AS BookPublisherName,dbo.LibraryInfoMaster.DDC
FROM dbo.LibraryInfoMaster INNER JOIN dbo.LibraryMaterialTypeMaster ON dbo.LibraryInfoMaster.MaterialTypeId = dbo.LibraryMaterialTypeMaster.MaterialTypeId INNER JOIN
dbo.LibraryBookDetails ON dbo.LibraryInfoMaster.BookCode = dbo.LibraryBookDetails.BookCode
Where
dbo.LibraryInfoMaster.BookName Like 'Sachitra Hindi Vyakaran Tatha Rachna For Class 6%' AND
LTrim(Rtrim(STUFF((Select ',' + x.AuthorName From View_LibraryBookAuthor As x Where x.BookCode = dbo.LibraryInfoMaster.BookCode For XML Path('')), 1, 1, ''))) Like '%Prakash, Rajendra' and
LTrim(Rtrim(STUFF((Select ',' + x.PublisherName From View_LibraryBookPublisher As x Where x.BookCode = dbo.LibraryInfoMaster.BookCode For XML Path('')), 1, 1, ''))) Like 'Geeta Publihing House, Delhi' and
dbo.LibraryInfoMaster.IsDeleted = 0 And dbo.LibraryMaterialTypeMaster.IsDeleted = 0 And dbo.LibraryMaterialTypeMaster.IsBookPeriodical = 0
Order By dbo.LibraryInfoMaster.DDC, dbo.LibraryMaterialTypeMaster.MaterialType, dbo.LibraryInfoMaster.BookName
After doing this the Result Set remained same as before but the Execution Time reduced from 16 minutes to 1 second.
Thought I should let you know how I did it.
Thanks for all you help guys.
June 25, 2012 at 5:04 am
vinu512 (6/25/2012)
Hi guys,Thanks for all the replies. I solved the problem. Well, technically... I didn't solve it, I took a Work around.
My Procedure had the following Query:
SELECT Distinct a.BookCode, b.MaterialType, a.BookName,
dbo.FN_GetLibraryBookAuthorPublisherNameNew(c.AccessionNo,a.BookCode,'AuthorName') AS BookAuthorName,
dbo.FN_GetLibraryBookAuthorPublisherNameNew(c.AccessionNo,a.BookCode,'PublisherName') AS BookPublisherName,a.DDC
FROM dbo.LibraryInfoMaster As a INNER JOIN
dbo.LibraryMaterialTypeMaster As b ON a.MaterialTypeId = b.MaterialTypeId INNER JOIN
dbo.LibraryBookDetails As c ON a.BookCode = c.BookCode
Where a.BookName Like 'Sachitra Hindi Vyakaran Tatha Rachna For Class 6%' AND
dbo.FN_GetLibraryBookAuthorPublisherName(a.BookCode,'AuthorName') Like '%Prakash, Rajendra' and
dbo.FN_GetLibraryBookAuthorPublisherName(a.BookCode,'PublisherName') Like 'Geeta Publihing House, Delhi' and
a.IsDeleted = 0 And b.IsDeleted = 0 And b.IsBookPeriodical = 0
Order By a.DDC, b.MaterialType, a.BookName
In this Query the Function will be called for every row in the table Library Info Master(ie: 4,00,000 * 4 times). That is why it ws taking 16 minutes to execute. That is not a smart thing, I know.
But, I was stumped because I thought that I need to have a Function to select data via a Select Query.
But then I just removed the Functions part from the query and added the Stuff Query directly to the above Query as follows:
SELECT Distinct dbo.LibraryInfoMaster.BookCode, dbo.LibraryMaterialTypeMaster.MaterialType, dbo.LibraryInfoMaster.BookName,
LTrim(Rtrim(STUFF((Select ',' + x.AuthorName From LibraryBookAuthorMaster As x,LibraryBookAuthor As y,LibraryBookDetails As z
Where x.AuthorId=y.AuthorId And y.AccessionNo=z.AccessionNo And z.BookCode=dbo.LibraryInfoMaster.BookCode
And z.AccessionNo=dbo.LibraryBookDetails.AccessionNo For Xml Path('')), 1, 1, ''))) As BookAuthorName,
LTrim(Rtrim(STUFF((Select ',' + x.PublisherName From LibraryBookPublisherMaster As x,LibraryBookPublisher As y,LibraryBookDetails As z
Where x.PublisherId=y.PublisherId And y.AccessionNo=z.AccessionNo And z.BookCode=dbo.LibraryInfoMaster.BookCode
And z.AccessionNo=dbo.LibraryBookDetails.AccessionNo For Xml Path('')), 1, 1, ''))) AS BookPublisherName,dbo.LibraryInfoMaster.DDC
FROM dbo.LibraryInfoMaster INNER JOIN dbo.LibraryMaterialTypeMaster ON dbo.LibraryInfoMaster.MaterialTypeId = dbo.LibraryMaterialTypeMaster.MaterialTypeId INNER JOIN
dbo.LibraryBookDetails ON dbo.LibraryInfoMaster.BookCode = dbo.LibraryBookDetails.BookCode
Where
dbo.LibraryInfoMaster.BookName Like 'Sachitra Hindi Vyakaran Tatha Rachna For Class 6%' AND
LTrim(Rtrim(STUFF((Select ',' + x.AuthorName From View_LibraryBookAuthor As x Where x.BookCode = dbo.LibraryInfoMaster.BookCode For XML Path('')), 1, 1, ''))) Like '%Prakash, Rajendra' and
LTrim(Rtrim(STUFF((Select ',' + x.PublisherName From View_LibraryBookPublisher As x Where x.BookCode = dbo.LibraryInfoMaster.BookCode For XML Path('')), 1, 1, ''))) Like 'Geeta Publihing House, Delhi' and
dbo.LibraryInfoMaster.IsDeleted = 0 And dbo.LibraryMaterialTypeMaster.IsDeleted = 0 And dbo.LibraryMaterialTypeMaster.IsBookPeriodical = 0
Order By dbo.LibraryInfoMaster.DDC, dbo.LibraryMaterialTypeMaster.MaterialType, dbo.LibraryInfoMaster.BookName
After doing this the Result Set remained same as before but the Execution Time reduced from 16 minutes to 1 second.
Thought I should let you know how I did it.
Thanks for all you help guys.
Not so fast - this query is horrible, let's fix it up and you will see what I mean.
Couple of questions: Your output list has BookAuthorName and BookPublisherName. Your WHERE clause filters on AuthorName and PublisherName. The correlated subqueries look very similar, except the two in the output list use tables whereas the two in the WHERE clause reference a view. How equivalent are they meant to be?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2012 at 10:09 pm
Yes, the output list contains the Author/publisher Names from a Table named "LibraryInfoMaster". The Where Clause uses the Views "View_LibraryBookAuthor" and "LibraryBookPublisher".
The difference here is because I want to select all author names(as a Comma Delimited String) for each BookCode of the table "LibraryInfoMaster" from the table which are also present in the Views based on other conditions from the Where Clause.
The Views were designed as follows and hold details for only some Books not all.
View_LibraryBookAuthor:
CREATE View [dbo].[View_LibraryBookAuthor]
As
SELECT dbo.LibraryBookAuthorMaster.AuthorId, dbo.LibraryBookAuthorMaster.AuthorName, dbo.LibraryBookAuthor.BookCode
FROM dbo.LibraryBookAuthor INNER JOIN
dbo.LibraryBookAuthorMaster ON dbo.LibraryBookAuthor.AuthorId = dbo.LibraryBookAuthorMaster.AuthorId
Where dbo.LibraryBookAuthor.IsDeleted = 0
And dbo.LibraryBookAuthorMaster.IsDeleted = 0
View_LibrarayBookPublisher:
CREATE View [dbo].[View_LibraryBookPublisher]
As
SELECT dbo.LibraryBookPublisher.BookCode, dbo.LibraryBookPublisherMaster.PublisherId, dbo.LibraryBookPublisherMaster.PublisherName
FROM dbo.LibraryBookPublisher INNER JOIN
dbo.LibraryBookPublisherMaster ON dbo.LibraryBookPublisher.PublisherId = dbo.LibraryBookPublisherMaster.PublisherId
Where dbo.LibraryBookPublisher.IsDeleted = 0
And dbo.LibraryBookPublisherMaster.IsDeleted = 0
Someone from our Dot Net Team wrote this query as part of a Procedure before I had joined.
It was taking a very long time to Execute(16+ minutes).
So, they asked me to optimize it somehow.
June 26, 2012 at 2:09 am
The correlated subqueries in the output, and the correlated subqueries in the WHERE clause, are correlated on different columns. Focussing on Author, here's the output query:
SELECT BookAuthorName= LTrim(Rtrim(STUFF((
SELECT ',' + x.AuthorName
FROM LibraryBookAuthorMaster As x
INNER JOIN LibraryBookAuthor As y
ON y.AuthorId = x.AuthorId
WHERE y.AccessionNo = bd.AccessionNo -- Outer reference to dbo.LibraryBookDetails
For Xml Path(''))
, 1, 1, '')))
and here's the filter query:
CREATE View [dbo].[View_LibraryBookAuthor]
As
SELECT x.AuthorId, x.AuthorName, y.BookCode -- Outer reference to dbo.LibraryInfoMaster
FROM dbo.LibraryBookAuthorMaster x
INNER JOIN dbo.LibraryBookAuthor y
ON y.AuthorId = x.AuthorId
Where y.IsDeleted = 0
Is this intentional? Would you get the desired result by filtering on the output comma-delimited string? The reason I ask, is because the output queries can be dropped down into the FROM list as CROSS APPLYs - where their output is within scope of the WHERE clause, like this:
SELECT Distinct
im.BookCode,
tm.MaterialType,
im.BookName,
output1.BookAuthorName,
output2.BookPublisherName,
im.DDC
FROM dbo.LibraryInfoMaster im
INNER JOIN dbo.LibraryMaterialTypeMaster tm
ON im.MaterialTypeId = tm.MaterialTypeId
INNER JOIN dbo.LibraryBookDetails bd
ON im.BookCode = bd.BookCode
CROSS APPLY (
SELECT BookAuthorName= LTrim(Rtrim(STUFF((
SELECT ',' + x.AuthorName
FROM LibraryBookAuthorMaster As x
INNER JOIN LibraryBookAuthor As y
ON y.AuthorId = x.AuthorId
WHERE y.AccessionNo = bd.AccessionNo -- Outer reference to dbo.LibraryBookDetails
For Xml Path(''))
, 1, 1, '')))
) output1
CROSS APPLY (
SELECT BookPublisherName= LTrim(Rtrim(STUFF((
SELECT ',' + x.PublisherName
FROM LibraryBookPublisherMaster As x
INNER JOIN LibraryBookPublisher As y
ON x.PublisherId = y.PublisherId
WHERE y.AccessionNo = bd.AccessionNo -- Outer reference to dbo.LibraryBookDetails
For Xml Path(''))
, 1, 1, '')))
) output2
WHERE im.BookName Like 'Sachitra Hindi Vyakaran Tatha Rachna For Class 6%'
AND output1.BookAuthorName LIKE '%Prakash, Rajendra%'
AND output2.BookPublisherName LIKE '%Geeta Publihing House, Delhi%'
and im.IsDeleted = 0
And tm.IsDeleted = 0
And tm.IsBookPeriodical = 0
ORDER BY im.DDC, tm.MaterialType, im.BookName
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 2:13 am
If the query above doesn't match the spec, then you can still make a big improvement to the design by rearranging the two correlated subquery filters. You don't need to expensively generate a comma-delimited list of all matching authors;
WHERE im.BookName Like 'Sachitra Hindi Vyakaran Tatha Rachna For Class 6%'
AND EXISTS(SELECT 1 FROM View_LibraryBookAuthor As x WHERE x.BookCode = im.BookCode AND x.AuthorName LIKE '%Prakash, Rajendra%')
AND EXISTS(SELECT 1 FROM View_LibraryBookPublisher As x WHERE x.BookCode = im.BookCode AND x.PublisherName Like '%Geeta Publihing House, Delhi%')
and im.IsDeleted = 0
And tm.IsDeleted = 0
And tm.IsBookPeriodical = 0
ORDER BY im.DDC, tm.MaterialType, im.BookName
Don't forget to put a % sign at both ends of the string to match when you are attempting to match anywhere in the reference string.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 4:44 am
Would you get the desired result by filtering on the output comma-delimited string?
Let me put some more light on the requirement 🙂
One book may have many authors and co-authors which are all stored separately. BookCode is common to all of them. So I need to get all the authors for a book by its BookCode. That is what I am selecting as a Comma Delimited String.
The result of your CrossAplly query and my query is same.
But, your Cross Apply query executes in half as much time(4,00,000 to 4,50,000 m.s.) on the actual data.
That is a very good attempt. I'm just learning and would take some more time to have a go at the Cross Apply myself.
It was Dwain who explained the concept of STUFF() in another post and I feel it was worth the effort.
Thanks for the Cross Apply query Chris. I am sure it would definitely come in handy one of these days.
Thanks for all you effort.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply