Performance tuning

Question

Post reply

Performance tuning

Nisha N.Kumar

Ten Centuries

Points: 1100
More actions
September 19, 2007 at 5:02 am

#198023

Hi,
Consider I have an employee table consisting of 50,000 records in it.The table has fields like EmpId,EmpName,Designation and Salary details.An index has been created on EmpId.If I execute the following query:
Select EmpId,EmpName,Salary
from Employee
where Designation like '%Manager'
Can anyone please tell me how much time this query will take to execute.
How can the performance be increased in these scenarios?
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Is this an exam question?

I suggest you create the table and insert that many rows into it and give it a test.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

In SQL Server you can do nothing to improve performance. Even if you create index on Designation column query optimizer will not pick it because you have leading % wildcard.

However if this would be Oracle you would be able to create a reversed index (I am not sure in exact terminology) where index is created like 'reganaM', in this case Oracle's query optimizer will re-create your query like 'reganaM%'.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Well nothing obvious, or "low effort".

That being said if you do this kind of stuff enough to justify the change in structure- you could look at using a computed column index, where the computed column would be based on the REVERSE() function of the field in question. Still not quite as fast as a "forward search" but does help

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 4

Why would you say that is not quite as fast as "forward search" ?

If you create an index on that computed column you can get SEEKs from "forward searching" that "reversed" string

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 5

Example:

create table mytable

( i int,

iname varchar(200),

reverse_iname as reverse(iname))

create nonclustered index ix_mytable_reverse_iname on mytable(reverse_iname)

insert into mytable (i, iname) values ( 1, 'FORWARD' )

insert into mytable (i, iname) values ( 2, 'REVERSE' )

insert into mytable (i, iname) values ( 3, 'UNKNOWN' )

insert into mytable (i, iname) values ( 4, 'TESTING' )

SET SHOWPLAN_TEXT ON

GO

SELECT iname from mytable

where reverse_iname like REVERSE('WARD') + '%'

|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[devdba_data].[dbo].[mytable]))

|--Filter(WHERElike([mytable].[reverse_iname], reverse('WARD')+'%', NULL)))

|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1009], [Expr1010], LikeRangeInfo(reverse('WARD')+'%', NULL)))

|--Compute Scalar(DEFINE[Expr1009]=Convert(LikeRangeStart(reverse('WARD')+'%', NULL)), [Expr1010]=Convert(LikeRangeEnd(reverse('WARD')+'%', NULL)), [Expr1007]=LikeRangeInfo(reverse('WARD')+'%', NULL)))

| |--Constant Scan

|--Index Seek(OBJECT[devdba_data].[dbo].[mytable].[ix_mytable_reverse_iname]), SEEK:([mytable].[reverse_iname] > [Expr1009] AND [mytable].[reverse_iname] < [Expr1010]) ORDERED FORWARD)

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

my numbers come in marginally slower with a computed column than a matching "real" column in "forward matching". Of course - I'm talking when BOTH columns are indexed.

meaning

Title like 'manager%' --title is actual field

vs

revtitle like 'renagam%' -- computed

Still a VAST improvement over '%manager'

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 7

The only difference could be that I did not speified the NULLABILITY and therefore it makes a tiny bit of a difference but that can also be solved with ISNULL() function

* Noel