Select query execution time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query execution time

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-19 : 11:01:32

What is different between the two methods: the first one taking almost 7 secs and the second one is fast with in 1 sec. both are using like function.

The ocdata - ntext field, has the full text content in each row., i have right now 60,000 rows in this table.

I am using this logic inside my SP:

DECLARE @KeyWord as nvarchar(100)

Set @KeyWord = 'Portal'

CREATE TABLE #TEMP
(
id INT,
ocdata nvarchar(max),
tiffile varchar(100),
type varchar(25),
type_id int
)
insert into #TEMP(id,ocdata,tiffile,type,type_id)
select id,ocdata,tiffile,type,type_id from TABOcrData where ocdata LIKE '%'+ @KeyWord + '%' and type = 'PM'

Select * from #TEMP order by id

Drop table #TEMP

********************************************

CREATE TABLE #TEMP
(
id INT,
ocdata nvarchar(max),
tiffile varchar(100),
type varchar(25),
type_id int
)
insert into #TEMP(id,ocdata,tiffile,type,type_id)
select id,ocdata,tiffile,type,type_id from TABOcrData where ocdata LIKE '%Portal%' and type = 'PM'

Select * from #TEMP order by id

Drop table #TEMP

asgast
Posting Yak Master

149 Posts

Posted - 2009-02-19 : 11:20:42

1st one has variable in like string, variable needs to be processed for every row
2nd is constant

if you want to have a variable and have the same speed then use dynamic sql,

DECLARE @n nvarchar(500)
DECLARE @KeyWord as nvarchar(100)

Set @KeyWord = 'Portal'

CREATE TABLE #TEMP
(
id INT,
ocdata nvarchar(max),
tiffile varchar(100),
type varchar(25),
type_id int
)
SET @n = 'select id,ocdata,tiffile,type,type_id from TABOcrData where ocdata LIKE ''%'+@KeyWord+'%'' and type = ''PM'''
insert into #TEMP(id,ocdata,tiffile,type,type_id)
EXEC sp_executesql @n

Select * from #TEMP order by id

Drop table #TEMP

on my table with 750900 rows 1st type query runs for 6 sec 2nd for 40 last one runs for 8 sec

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 11:22:44

Check your execution plans for both queries. I faced a similar problem before.

Check this.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119740

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-19 : 11:45:17

Asgast: U r absolutely right.

Thank you very much for the help.

quote:
Originally posted by asgast

1st one has variable in like string, variable needs to be processed for every row
2nd is constant

if you want to have a variable and have the same speed then use dynamic sql,

DECLARE @n nvarchar(500)
DECLARE @KeyWord as nvarchar(100)

Set @KeyWord = 'Portal'

CREATE TABLE #TEMP
(
id INT,
ocdata nvarchar(max),
tiffile varchar(100),
type varchar(25),
type_id int
)
SET @n = 'select id,ocdata,tiffile,type,type_id from TABOcrData where ocdata LIKE ''%'+@KeyWord+'%'' and type = ''PM'''
insert into #TEMP(id,ocdata,tiffile,type,type_id)
EXEC sp_executesql @n

Select * from #TEMP order by id

Drop table #TEMP

on my table with 750900 rows 1st type query runs for 6 sec 2nd for 40 last one runs for 8 sec

asgast
Posting Yak Master

149 Posts

Posted - 2009-02-19 : 11:56:06

I suspect that vijayisonly is correct too, I just gave you the simplest solution, not the best one. Using too much dynamic sql will stop sql server from reusing execution plan and this will slow down the SP. Checking your execution plan and indexes is a good thing to do if you want our queries to run faster.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 12:00:31

Why are you using ntext in first hand?It will be discontinued. Also are you saying you have full-text index? Check execution plan as you must have scan in the first query.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-20 : 06:24:47

Hello sodeep,

it is varchar(8000) datatype, is it OK or still will there be a problem.

Also this particular table will grow fast, per day atleast 4000 records all documents pages text wil be inserted to it.

Thanks.

quote:
Originally posted by sodeep

Why are you using ntext in first hand?It will be discontinued. Also are you saying you have full-text index? Check execution plan as you must have scan in the first query.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 06:26:44

And Where is the difference between execution plans on both queries? Post what you see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources