Can this Stored Proc be more efficient?

Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:

> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:

> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:

> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO