Reputation: 908
I have a slow query which contains more than 10 left join and 10 like operators. see bellow.
SystemContact has approx. 10K of data, the other two have more than 100k of data. The query looks not optimized, this is because it is generated by a program used to search SystemContact.
The problem that I faced is, the query is running very slow on our system which takes at least a minute to run. We have done updating statistics and defragging tables but it does not seem to improve performance much.
Initially I was thinking it might be related to the string DataType which is currently using nvarchar, but when I load all those tables with data into tempdb on the same server, the query runs about 2 seconds.
I am wondering what could be cause of the slowness?
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select
Id, Context1, Context2, Context3, LastModifiedBy
from SystemContact
left join SystemField as SF1 on SystemContact.Id = SF1.ContactId and SF1.FieldId = 1111
left join SystemField as SF2 on SystemContact.Id = SF2.ContactId and SF2.FieldId = 5445
left join SystemField as SF3 on SystemContact.Id = SF3.ContactId and SF3.FieldId = 3423
left join SystemField as SF4 on SystemContact.Id = SF4.ContactId and SF4.FieldId = 6545
left join SystemField as SF5 on SystemContact.Id = SF5.ContactId and SF5.FieldId = 5464
left join SystemFieldText as SFT1 on SystemContact.Id = SFT1.ContactId and SFT1.FieldId = 546
left join SystemFieldText as SFT2 on SystemContact.Id = SFT2.ContactId and SFT2.FieldId = 7565
left join SystemFieldText as SFT3 on SystemContact.Id = SFT3.ContactId and SFT3.FieldId = 456
left join SystemFieldText as SFT4 on SystemContact.Id = SFT4.ContactId and SFT4.FieldId = 457
left join SystemFieldText as SFT5 on SystemContact.Id = SFT5.ContactId and SFT5.FieldId = 56
left join SystemFieldText as SFT6 on SystemContact.Id = SFT6.ContactId and SFT6.FieldId = 85
where
(SF1.Value like '%[email protected]%') or (SF2.Value like '%[email protected]%') or (SF3.Value like '%[email protected]%')
or (SF4.Value like '%[email protected]%') or (SF5.Value like '%[email protected]%')
or (SFT2.Value like '%[email protected]%') or (SFT3.Value like '%[email protected]%') or (SFT4.Value like '%[email protected]%')
or (SFT5.Value like '%[email protected]%') or (SFT6.Value like '%[email protected]%')
EDIT:
Here is the link to the Execution Plan : https://goo.gl/gICWir
Upvotes: 1
Views: 502
Reputation: 11
Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.
tempdb never has anything to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
Upvotes: 1
Reputation: 122040
CREATE NONCLUSTERED INDEX ix
ON dbo.SystemFieldText (FieldId, ContactId) INCLUDE (value)
GO
SELECT id,
Context1,
Context2,
Context3,
LastModifiedBy
FROM dbo.SystemContact
WHERE EXISTS(
SELECT *
FROM dbo.SystemField SF
WHERE SystemContact.id = SF.ContactId
AND SF.value LIKE '%[email protected]%'
AND SF.FieldId IN (1111, 5445, 3423, 6545, 5464)
)
OR EXISTS(
SELECT *
FROM dbo.SystemFieldText
WHERE SystemContact.id = SFT.ContactId
AND SFT.value LIKE '%[email protected]%'
AND SFT.FieldId IN (546, 7565, 7565, 456, 457, 56, 85)
)
Upvotes: 0