July 17, 2010 at 11:29 pm
Hi,
I have a problem with query which is returning more than 45000 records. I have applied covering indexes on query. After implementation of covering index it's taking 1.36 seconds to return more than 45000 records. Previously it was taking 1.51 seconds. But I wish to return below query within 5 seconds.
My T-Sql query is:
DECLARE @ExportTable TABLE (MemberId INT)
INSERT @ExportTable
SELECT pk_MemberId FROM tbl_MARC_MemberMaster WHERE b_IsActive=1
AND (ISNULL(str_FirstName,'') LIKE '%'+@str_FirstName+'%' OR ISNULL(str_KataFirstName,'') LIKE '%'+@str_FirstName+'%')
AND (ISNULL(str_FamilyName,'') LIKE '%'+@str_FamilyName+'%' OR ISNULL(str_KataFamilyName,'') LIKE '%'+@str_FamilyName+'%')
AND (ISNULL(str_ReservationPhone,'') LIKE '%%' OR ISNULL(str_OtherPhone,'') LIKE '%%' OR ISNULL(str_ReservationPhone,'') LIKE '%%' OR ISNULL(str_ReservationPhone2,'') LIKE '%%' OR ISNULL(str_SecretaryContact,'') LIKE '%%')
and (ISNULL(str_CompanyEmail,'') LIKE '%%' OR ISNULL(str_SecretaryEmail,'') LIKE '%%' OR ISNULL(str_PersonalEmail,'') LIKE '%%' OR ISNULL(str_SpouseEmail,'') LIKE '%%')
AND ISNULL(str_CompanyName,'') LIKE '%%'
AND ISNULL(b_IsVip,'')=CASE WHEN @b_Vip=0 OR @b_Vip=1 THEN @b_Vip ELSE ISNULL(b_IsVip,'') END
AND ISNULL(b_NewsLetter,'')=CASE WHEN @b_NewsLetter=0 OR @b_NewsLetter=1 THEN @b_NewsLetter ELSE ISNULL(b_NewsLetter,'') END
AND (LTRIM(RTRIM(ISNULL(str_FirstName,''))) NOT LIKE N'Walkin%' AND LTRIM(RTRIM(ISNULL(str_FamilyName,''))) NOT LIKE 'Diner%')
IF(@fk_RestaurantId!=0 OR @fk_PromotionId!=0)
BEGIN
DELETE FROM @ExportTable WHERE NOT EXISTS
(
SELECT fk_MemberId FROM tbl_MARC_Reservation
WHERE fk_RestaurantId=CASE WHEN @fk_RestaurantId!=0 THEN @fk_RestaurantId ELSE fk_RestaurantId END
ANDfk_PromotionId=CASE WHEN @fk_PromotionId!=0 THEN @fk_PromotionId ELSE fk_PromotionId END
AND b_IsCancelled=0 AND fk_MemberId=MemberId
)
END
IF(@fk_CardId!=0)
DELETE FROM @ExportTable WHERE NOT EXISTS(SELECT fk_MemberId FROM tbl_MARC_Memberships WHERE fk_CardId=@fk_CardId AND b_IsActive=1 AND fk_MemberId=MemberId)
IF(@fk_SourceId!=0)
DELETE FROM @ExportTable WHERE NOT EXISTS(SELECT fk_MemberId FROM tbl_MARC_MemberSourceData WHERE fk_SourceDataId=@fk_SourceId AND fk_MemberId=MemberId)
SELECT str_Title,str_FirstName,str_FamilyName,str_KataFirstName,str_KataFamilyName,str_ReservationPhone,
i_ReservationPhoneType=CASE WHEN i_ReservationPhoneType =1 THEN 'Phone1'
WHEN i_ReservationPhoneType=2 THEN 'Phone2'
WHEN i_ReservationPhoneType=3 THEN 'Home'
WHEN i_ReservationPhoneType=4 THEN 'Office'
WHEN i_ReservationPhoneType=5 THEN 'Hotel'
END,
str_ReservationPhone2,
i_ReservationPhoneType2=CASE WHEN i_ReservationPhoneType2 =1 THEN 'Phone1'
WHEN i_ReservationPhoneType2=2 THEN 'Phone2'
WHEN i_ReservationPhoneType2=3 THEN 'Home'
WHEN i_ReservationPhoneType2=4 THEN 'Office'
WHEN i_ReservationPhoneType2=5 THEN 'Hotel'
END,
str_JobTitle,str_CompanyName,str_CompanyEmail,str_CompanyArea,str_CompanyPhone,str_CompanyFax,
str_SecretaryName,str_SecretaryContact,str_SecreteryFax,str_HomeAddess,str_PersonalEmail,str_OtherPhone,
i_OtherPhoneType=CASE WHEN i_OtherPhoneType =1 THEN 'Phone1'
WHEN i_OtherPhoneType=2 THEN 'Phone2'
WHEN i_OtherPhoneType=3 THEN 'Home'
WHEN i_OtherPhoneType=4 THEN 'Office'
WHEN i_OtherPhoneType=5 THEN 'Hotel'
END,
b_IsVip=CASE WHEN b_IsVip =1 THEN 'Yes' WHEN b_IsVip=0 OR b_IsVip IS NULL THEN 'No' END,str_VipType,
dt_BirthDate=CASE WHEN CONVERT(VARCHAR(20),dt_BirthDate,101)='01/01/1900' THEN ''
ELSE CONVERT(VARCHAR(20),dt_BirthDate,101)
END,
dt_AnniversaryDate=CASE WHEN CONVERT(VARCHAR(20),dt_AnniversaryDate,101)='01/01/1900' THEN ''
ELSE CONVERT(VARCHAR(20),dt_AnniversaryDate,101)
END
,Member1=(SELECT TOP 1 MC.str_MembershipName FROM tbl_MARC_MembershipCardMaster MC, tbl_MARC_Memberships MS
WHERE MC.b_IsActive=1 AND MS.b_IsActive=1 AND MS.fk_MemberId=MM.pk_MemberId AND MC.pk_CardId=MS.fk_CardId)
,MemberRef1=(SELECT TOP 1 MS.str_CardNumber FROM tbl_MARC_MembershipCardMaster MC, tbl_MARC_Memberships MS
WHERE MC.b_IsActive=1 AND MS.b_IsActive=1 AND MS.fk_MemberId=MM.pk_MemberId AND MC.pk_CardId=MS.fk_CardId)
,Member2=(SELECT TOP 1 MC.str_MembershipName FROM tbl_MARC_MembershipCardMaster MC, tbl_MARC_Memberships MS
WHERE MC.b_IsActive=1 AND MS.b_IsActive=1 AND MS.fk_MemberId=MM.pk_MemberId AND MC.pk_CardId=MS.fk_CardId ORDER BY MC.str_MembershipName DESC)
,MemberRef2=(SELECT TOP 1 MS.str_CardNumber FROM tbl_MARC_MembershipCardMaster MC, tbl_MARC_Memberships MS
WHERE MC.b_IsActive=1 AND MS.b_IsActive=1 AND MS.fk_MemberId=MM.pk_MemberId AND MC.pk_CardId=MS.fk_CardId ORDER BY MC.str_MembershipName DESC)
,str_SourceID=STUFF(( SELECT ','+ str_SourceOfData FROM tbl_MARC_MemberSourceData MSD,tbl_MARC_SourceMaster SM
WHERE MSD.fk_MemberId = MM.pk_MemberId AND MSD.fk_SourceDataId=SM.pk_sourceId AND SM.b_IsActive=1
FOR XML PATH('')),1, 1, '')
FROM tbl_MARC_MemberMaster MM,tbl_Marc_TitleMaster,@ExportTable
WHERE pk_TitleId=fk_TitleId AND MM.Pk_MemberId=MemberId
If I removed below
str_SourceID=STUFF(( SELECT ','+ str_SourceOfData FROM tbl_MARC_MemberSourceData MSD,tbl_MARC_SourceMaster SM
WHERE MSD.fk_MemberId = MM.pk_MemberId AND MSD.fk_SourceDataId=SM.pk_sourceId AND SM.b_IsActive=1
FOR XML PATH('')),1, 1, '')
part from query, then it's taking 2 seconds. but if I added this then it's taking 1.36 seconds. How to return records within 5 seconds?
Please help me to sort out this kind of problem.
Many Thanks,
Sagar Sawant
July 18, 2010 at 3:04 am
Several things:
#1: 2 seconds and 1.36 seconds, respectively, are both below your target of 5 seconds 😀
#2: instead of using a table variable you should use an indexed temp table
#3: it seems like you're populating your intermediate table based on a catch-all-query. Please see Gails[/url] or Erland Sommarskogs links for issues with such queries including alternatives.
#4: you're using TOP1 without a ORDER BY. This will return random results.
#5: What is the purpose of returning MS.str_CardNumber and MC.str_MembershipName twice (based on a random TOP1 and TPO 1 ORDER BY)
#6: Instead of using multiple subqueries for MS.str_CardNumber and MC.str_MembershipName you should use a single subquery/CTE and a join.
#7: Instead of using the CROSS JOIN - WHERE syntax you should change it to INNER JOIN ON for readability.
#8: get the concatenated list from a subquery without the temp table.
Edit: #9: @ExportTable is currently used as a cross join. Is this intentionally?
That's the stuff I spotted when looking at the query. In order help you improve performance I'd recommend you use an indexed temp table first and change the concatenation to a subquery as mentioned. If you still have performance issues please post the related table DDL together with the actual execution plan as an attached sqlplan file.
July 19, 2010 at 3:51 am
Hi,
Thanks for reply but some problems are
#2: Indexed temp table allocating resources in temp db as compared to table variable.
#4 & 5: TOP 1 used two times because one member has two membership card and card numbers.
Main problem I am facing is.
str_SourceID=STUFF(( SELECT ','+ str_SourceOfData FROM tbl_MARC_MemberSourceData MSD,tbl_MARC_SourceMaster SM
WHERE MSD.fk_MemberId = MM.pk_MemberId AND MSD.fk_SourceDataId=SM.pk_sourceId AND SM.b_IsActive=1
FOR XML PATH('')),1, 1, '')
The xml path is taking too much time.. Is there any valid way to return more quickly.
Many Thanks,
Sagar Sawant
July 19, 2010 at 4:13 am
Sagar Sawant (7/19/2010)
Hi,Thanks for reply but some problems are
#2: Indexed temp table allocating resources in temp db as compared to table variable.
...
Who told you that? Is it your guess?
Why you think that table variable does not take resourcved in tempdb?
Check this out, execute:
select * from tempdb.sys.tables
Check how many entries you have there.
Then, run this:
declare @MyTest table (a int)
select * from tempdb.sys.tables
You will see your table variable exactly the same way as temp table...
There are no much difference between how temp table and table variables in realtion to memory use.
Whenever possible SQLServer will keep both of them in memory. There Difference is in the scope. Table variable scope is limited to the batch where it is defined while temp table will stay there for the duration of
connection.
Also ther is a differnec in terms of indexing possibilities.
Table variable can only have one index (unique clustered one) which can not be created explicitly, it will be created by defualt if you define primary key. Temp table allows indices to be defined in the same way as for permanent tables.
In general, you should not use table variable for large datasets. It is ok to use it for small ones, however a lot of SQL developers avoid using them at all for the production code. Personaly, I only use them for quick dev/testing code.
July 19, 2010 at 4:53 am
Hi,
I know table variable allocating less resources as compared to index temp tables and also it stored in temp db.
But how to improve below query. it's taking more than one min to returning over 45000 records.
,str_SourceID=STUFF(( SELECT ','+ str_SourceOfData FROM tbl_MARC_MemberSourceData MSD,tbl_MARC_SourceMaster SM
WHERE MSD.fk_MemberId = MM.pk_MemberId AND MSD.fk_SourceDataId=SM.pk_sourceId AND SM.b_IsActive=1
FOR XML PATH('')),1, 1, '')
July 19, 2010 at 5:22 am
Eugene Elutin (7/19/2010)
...Also there is a differnec in terms of indexing possibilities.Table variable can only have one index (unique clustered one) which can not be created explicitly, it will be created by defualt if you define primary key. Temp table allows indices to be defined in the same way as for permanent tables.
In general, you should not use table variable for large datasets. It is ok to use it for small ones, however a lot of SQL developers avoid using them at all for the production code. Personaly, I only use them for quick dev/testing code.
Ooo! Someone else missed Wayne Sheffield's fine comparison article on this subject I see! 😀
http://www.sqlservercentral.com/articles/66720/
I must show you a table variable with more than one index:
DECLARE @demo
TABLE (
col_1 INTEGER NOT NULL PRIMARY KEY CLUSTERED,
col_2 INTEGER NOT NULL UNIQUE NONCLUSTERED, -- Index 2!
data SQL_VARIANT NULL
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 5:47 am
Paul, yep, I was not exectly correct in my post, I should just say that it is impossible to create indices explicitly on table variable after its declaration, only implicitly as result of defining unique constrains (therefore I guess only unique indices are possible...).
Should say again, that I'm not using table variables often, may be in UDF sometimes...
Sagar,
1. Sql server allocates as much resources as needed and possible for temp tables or table variables. You cannot state that it's less or more for one or another...
2. To improve your query, you need to find why it is slow.
- It may be that there is no sufficient index
- Statisc was not updated for long time
I also recommend use INNER JOIN cluase instead of implicit cross join...
July 19, 2010 at 6:36 am
Eugene,
I have applied necessary changes in query.
If I removed below code from sql query then query executed in 2 seconds.
,str_SourceID=STUFF(( SELECT ','+ str_SourceOfData FROM tbl_MARC_MemberSourceData MSD,tbl_MARC_SourceMaster SM
WHERE MSD.fk_MemberId = MM.pk_MemberId AND MSD.fk_SourceDataId=SM.pk_sourceId AND SM.b_IsActive=1
FOR XML PATH('')),1, 1, '')
My only question is that How to improve this part only.
Many Thanks,
Sagar Sawant
July 19, 2010 at 6:42 am
Eugene Elutin (7/19/2010)
Paul, yep, I was not exectly correct in my post, I should just say that it is impossible to create indices explicitly on table variable after its declaration, only implicitly as result of defining unique constrains (therefore I guess only unique indices are possible...).Should say again, that I'm not using table variables often, may be in UDF sometimes...
No worries - I kinda guessed that you knew about the possibility of UNIQUE constaints (and therefore an enforcing index), so I just posted the link for other people that might be reading this thread. You're also right that it is not possible to create a non-unique index on a table variable (though you can cheat by including the PK in an otheriwse non-unique index to make it unqiue...)
I do use table variables quite a lot, but each has its own advantages and disadvantages, as Wayne clearly shows in his article.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 10:19 am
Sagar Sawant (7/19/2010)
Eugene,I have applied necessary changes in query.
If I removed below code from sql query then query executed in 2 seconds.
,str_SourceID=STUFF(( SELECT ','+ str_SourceOfData FROM tbl_MARC_MemberSourceData MSD,tbl_MARC_SourceMaster SM
WHERE MSD.fk_MemberId = MM.pk_MemberId AND MSD.fk_SourceDataId=SM.pk_sourceId AND SM.b_IsActive=1
FOR XML PATH('')),1, 1, '')
My only question is that How to improve this part only.
Many Thanks,
Sagar Sawant
See #8 and #9 from my initial reply.
July 19, 2010 at 10:25 am
Paul White NZ (7/19/2010)
...I do use table variables quite a lot, but each has its own advantages and disadvantages, as Wayne clearly shows in his article.
Paul
Absolutely agreed.
And based on the information currently available I think an indexed temp table would be a better choice since (I'm guessing here due to the lack of execution plans or any other related information) there will be more than just a few rows in that table.
July 19, 2010 at 10:31 am
I think the interesting thing is the concern about the use of resources. You could use a table variable thinking it would "use fewer resources" but then have your query not be as efficient, thus increasing the processing time, which is another resource that you're using.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply