January 29, 2015 at 8:12 am
Is there a better way to write following query it is taking 12 sec to execute Your help will required I need this query in less than 4 sec... any improvement anyone can suggest would be highly appreciated
I have non clustered index on dbo.ShippingInfoImports.TrackingNumber column
Declare@status nvarchar(50),
@SearchText nvarchar(250)
set @status ='SalesOrderOpen'
set @SearchText = 21
if @status <> 'All'
begin
SELECT Distinct o.ID
FROM dbo.Orders o with (nolock)
join dbo.ShippingInfoImports si
WITH (NOLOCK) on si.ShippingKey LIKE Convert(varchar(8), o.OrderNumber) + '%'
Join dbo.status s on s.EnumKey = @status
WHERE o.statusid = s.ID
and si.TrackingNumber like '%' + @SearchText +'%'
end
else
begin
SELECT Distinct o.ID
FROM dbo.Orders o with (nolock)
Inner join dbo.ShippingInfoImports si
WITH (NOLOCK) on si.ShippingKey LIKE Convert(varchar(8), o.OrderNumber) + '%'
WHERE si.TrackingNumber like '%' + @SearchText +'%'
end
January 29, 2015 at 8:14 am
The nonclustered index will not be used, since the LIKE expression starts with %.
For better help, please post table DDL and execution plans.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 29, 2015 at 8:15 am
your where clause requires a full table scan that cannot use an index, becuase the search criteria looks for matches int he middle of the string.
WHERE si.TrackingNumber like '%' + @SearchText +'%'
if you can be sure the value for TrackingNumber STARTS with @SearchText , then you can use an index, otherwise there's nothing you cab do index-wise, maybe you can throw faster hardware at it,
WHERE si.TrackingNumber like @SearchText +'%'
Lowell
January 29, 2015 at 8:53 am
There may be a chance here...can you provide a few examples of si.ShippingKey and the range of o.OrderNumbers which you would expect to match? You may be able to construct a sargable BETWEEN to probe ShippingInfoImports.
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
January 29, 2015 at 9:53 am
Based solely on the existing query -- having not yet reviewed index missing stats and index usage stats for this table -- you could add a clustered index on:
si.ShippingKey
Nonclustered indexes are most useful only with very limited numbers of rows.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 29, 2015 at 11:32 am
How about something like:
DECLARE@status NVARCHAR(50) = 'SalesOrderOpen',
@SearchText NVARCHAR(250) = '21'
SELECT Distinct o.ID
FROM dbo.Orders o
INNER JOIN dbo.ShippingInfoImports si on si.ShippingKey LIKE Convert(varchar(8), o.OrderNumber) + '%'
LEFT JOIN dbo.status S ON o.statusid = s.ID
WHERE si.TrackingNumber like '%' + @SearchText +'%'
AND s.EnumKey LIKE
CASE @status = 'All' THEN '%' ELSE @status END;
Notice the removal of NOLOCK as that can give bad data.
January 29, 2015 at 6:02 pm
Try changing this:
si.TrackingNumber like '%' + @SearchText +'%'
to this:
si.TrackingNumber IS NOT NULL AND si.TrackingNumber like '%' + @SearchText +'%'
And if that helps, read this article to see why:
Improving Performance for Some LIKE “%string%” Searches[/url]
If you see a performance improvement gain, it will be dependent on how many NULL values are in that column (the more the better).
There are also some other things you could also try:
- change the order of your queries so that the si table is first (left-most table in the JOINs).
- change the odd JOINs that aren't on common table criteria to CROSS APPLY
And of course, remove the NOLOCK hints unless you're aware of the dangers of using it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 30, 2015 at 6:08 am
Try using PATINDEX instead of LIKE. I have had good luck using this method where indexes are available on the search column.
DECLARE @status NVARCHAR(50)
, @SearchText NVARCHAR(250)
SELECT @status ='SalesOrderOpen'
, @SearchText = '21'
IF @status <> 'All'
BEGIN
SELECT DISTINCT o.ID
FROM dbo.Orders o WITH (NOLOCK) INNER JOIN
dbo.ShippingInfoImports si WITH (NOLOCK) ON LEFT(si.ShippingKey,8) = CONVERT(VARCHAR(8), o.OrderNumber) INNER JOIN
dbo.[status] s WITH (NOLOCK) ON o.statusid = s.ID AND s.EnumKey = @status
WHERE PATINDEX('%'+@SearchText+'%',si.TrackingNumber) != 0;
END
ELSE
BEGIN
SELECT DISTINCT o.ID
FROM dbo.Orders o WITH (NOLOCK) INNER JOIN
dbo.ShippingInfoImports si WITH (NOLOCK) ON LEFT(si.ShippingKey,8) = CONVERT(VARCHAR(8), o.OrderNumber)
WHERE PATINDEX('%'+@SearchText+'%',si.TrackingNumber) != 0;
END
January 30, 2015 at 6:26 am
Christopher Kutsch (1/30/2015)
Try using PATINDEX instead of LIKE. I have had good luck using this method where indexes are available on the search column.
DECLARE @status NVARCHAR(50)
, @SearchText NVARCHAR(250)
SELECT @status ='SalesOrderOpen'
, @SearchText = '21'
IF @status <> 'All'
BEGIN
SELECT DISTINCT o.ID
FROM dbo.Orders o WITH (NOLOCK) INNER JOIN
dbo.ShippingInfoImports si WITH (NOLOCK) ON LEFT(si.ShippingKey,8) = CONVERT(VARCHAR(8), o.OrderNumber) INNER JOIN
dbo.[status] s WITH (NOLOCK) ON o.statusid = s.ID AND s.EnumKey = @status
WHERE PATINDEX('%'+@SearchText+'%',si.TrackingNumber) != 0;
END
ELSE
BEGIN
SELECT DISTINCT o.ID
FROM dbo.Orders o WITH (NOLOCK) INNER JOIN
dbo.ShippingInfoImports si WITH (NOLOCK) ON LEFT(si.ShippingKey,8) = CONVERT(VARCHAR(8), o.OrderNumber)
WHERE PATINDEX('%'+@SearchText+'%',si.TrackingNumber) != 0;
END
Whilst you could get a measurable performance improvement with a covering index on a wide table (as opposed to no covering index), you won't get index seeks with a leading wildcard using either LIKE or PATINDEX. If you can demonstrate a difference in performance between the two, I'd be very interested to see it.
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
January 30, 2015 at 6:55 am
Sure.
I created a table, TestData, that contains 1M rows.
IF OBJECT_ID( N'dbo.TestData',N'U' ) IS NOT NULL
DROP TABLE dbo.TestData
GO
CREATE TABLE dbo.TestData (
[RID] UNIQUEIDENTIFIER NOT NULL
, [Group1] VARCHAR ( 8 ) NOT NULL
, [Group2] VARCHAR ( 4 ) NOT NULL
, [Group3] VARCHAR ( 4 ) NOT NULL
, [Group4] VARCHAR ( 4 ) NOT NULL
, [Group5] VARCHAR ( 11 ) NOT NULL
, [DateCreated] DATETIME NOT NULL
, [DateModified] DATETIME NULL
, CONSTRAINT pk_TestData_RID PRIMARY KEY ( RID ASC )
)
GO
Alter Table dbo.TestData
Add Constraint df_TestData_RID DEFAULT ((NewSequentialID())) FOR RID
GO
INSERT dbo.TestData ( Group1, Group2, Group3, Group4, Group5, DateCreated )
SELECT TOP 1000000
(LEFT( CAST( NEWID() AS VARCHAR (36) ), 8 ))
, (SUBSTRING( CAST( NEWID() AS VARCHAR (36) ), 10, 4 ))
, (SUBSTRING( CAST( NEWID() AS VARCHAR (36) ), 15, 4 ))
, (SUBSTRING( CAST( NEWID() AS VARCHAR (36) ), 20, 4 ))
, (SUBSTRING( CAST( NEWID() AS VARCHAR (36) ), 25, 11 ))
, CASE WHEN ASCII( CAST( NEWID() AS VARCHAR (36) ) ) % 2 = 0 THEN
DATEADD( MONTH,ASCII( CAST( NEWID() AS VARCHAR (36) ) ) *-1,GETDATE() )
ELSE
DATEADD( DAY,ASCII( CAST( NEWID() AS VARCHAR (36) ) ) *-1,GETDATE() )
END AS [DateCreated]
FROM sys.objects a CROSS JOIN
sys.tables b CROSS JOIN
sys.partitions c CROSS JOIN
sys.columns d
GO
Create NonClustered Index idx_TestData_DateCreatedDateModified
ON dbo.TestData ( DateCreated, DateModified )
Include ( RID, Group1, Group2, Group3, Group4, Group5 )
GO
UPDATE dbo.TestData
SET DateModified = (DATEADD( millisecond,ASCII( CAST( RID AS VARCHAR (36) ) ),DateCreated ))
GO
Then, I perform my query against the data using the LIKE operator vs. the PATINDEX function.
SELECT *
FROM dbo.TestData
WHERE Group3 LIKE '%4E39%';
GO
SELECT *
FROM dbo.TestData
WHERE PATINDEX('%4E39%',Group3) != 0;
GO
In my testing, the LIKE operator requires parallelism and additional overhead to complete as opposed to the PATINDEX function.
January 30, 2015 at 7:25 am
Fantastic, thanks for going to the trouble of posting this up Chris.
Here's IO and timing stats for both queries as-is:
LIKE ========================================
(229 row(s) affected)
Table 'TestData'. Scan count 9, logical reads 10605, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 765 ms, elapsed time = 142 ms.
PATINDEX =====================================
(229 row(s) affected)
Table 'TestData'. Scan count 1, logical reads 10219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 873 ms, elapsed time = 878 ms.
Here are results with parallelism switched off in the LIKE query
SELECT *
FROM dbo.TestData
WHERE Group3 LIKE '%4E39%'
OPTION (MAXDOP 1);
LIKE ========================================
(229 row(s) affected)
Table 'TestData'. Scan count 1, logical reads 10219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 795 ms, elapsed time = 805 ms.
PATINDEX =====================================
(229 row(s) affected)
Table 'TestData'. Scan count 1, logical reads 10219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 889 ms, elapsed time = 880 ms.
Because it runs in parallel, the LIKE version is faster in this case.
If you change the queries slightly so they are both parallel and also gobble the output to remove network and display latency, the results are only trivially different:
DECLARE @Datetime DATETIME, @Eater VARCHAR(10)
SET @Datetime = GETDATE()
PRINT 'PATINDEX ====================================='
SELECT @Eater = Group3
FROM dbo.TestData
WHERE PATINDEX('%4E39%',Group3) != 0;
SELECT DATEDIFF(ms,@Datetime,getdate());
-- 146, 146, 163, 146, 146, 146, 156, 153, 140, 150
DECLARE @Datetime DATETIME, @Eater VARCHAR(10)
SET @Datetime = GETDATE()
PRINT 'LIKE ========================================'
SELECT @Eater = Group3
FROM dbo.TestData
WHERE Group3 LIKE '%4E39%'
SELECT DATEDIFF(ms,@Datetime,getdate());
-- 160, 113, 150, 126, 150, 130, 160, 130, 143, 150
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply