TSQL query Performance Issue

Question

Post reply

TSQL query Performance Issue

Hardy21

SSCrazy Eights

Points: 9708
More actions
June 15, 2010 at 2:00 am

#220058

Hello,
Currently I am facing performance related issue. I am using SQL Server 2008 R2.
Please find the sample database structure of my database.
I have two tables: TableA & TableB
TableA has primary key TableAId (Int) and TableAGuid (uniqueidentifier) with column: TableAName (nvarchar(60)).
TableB has primary key TableBId (uniqueidentifier) and TableAGuid (uniqueidentifier) with column: TableBName (nvarchar(60)).
TableA has clustered index on TableAName and non-clustered index on TableAGuid column.
TableB has clustered index on TableBId and non-clustered index on (TableAGuid, TableBName) column.
TableB contains upto 4 rows for TableAGuid.
Now, when I use following query, it retrieves data very fast:
Select TableA.TableAName from TableA left outer join TableB on TableA.TableAGuid = TableB.TableAGuid
Where TableA.TableAName like “H%”
But, when I use following query, it takes almost 8 times than first query:
Select TableA.TableAName from TableA left outer join TableB on TableA.TableAGuid = TableB.TableAGuid
Where (TableA.TableAName like “H%” OR TableB.TableBName like “H%” )
There are other joins as well but I have checked that when I add “OR” condition then only it takes more time.
I have also checked the execution plans. First query directly populates the data using ‘clustered index seek for tableA’ and ‘clustered index seek for tableB’ and use ‘Nested Loop’. But, when I use second query, it is using “Index scan for TableB” and “Hash join”.
As per the business logic, I have to use second query. Please guide me how can I improve the performance of the second query.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

How does

Select TableA.TableAName

from TableA

where TableA.TableAName like 'H%'

union -- ALL (possibly!!)

select TableA.TableAName

from TableA

join TableB

on TableA.TableAGuid = TableB.TableAGuid

Where TableB.TableBName like 'H%'

Perform , any better ?

Clear Sky SQL
My Blog[/url]

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Query is dynamically created and I have executed it using sp_ExecuteSQL, also there are other tables in the FROM clause (joins) so not able to change the query.

Thanks

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

An optimal solution would involve changing the query in some way.

SQLServer cant do magic. It is *probably* returning the optimal query plan for the query it has been asked.

Clear Sky SQL
My Blog[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

With the restriction you've placed, chances are that the only way you'll get a performance increase would be to either feed the server's hamsters rocket fuel or get a bigger server.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Try reversing the order of the columns in the nonclustered index on TableB. Make it (TableBName, TableAGuid)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Perhaps a Plan Guide to force the query that you think is optimal? Note that this can lead to disasterous performance for some other input parameters though!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

VinicioAizpurua SSC Veteran Points: 272 More actions · Answer 7

Did you move your query from 2000 to 2008? I am experiencing the same performance issue. I have a SP that run in about1/2 sec in 2000 and in 2008 takes 25sec.

Vinicio Aizpurua

Miami, FL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

VinicioAizpurua (6/18/2010)
Did you move your query from 2000 to 2008? I am experiencing the same performance issue. I have a SP that run in about1/2 sec in 2000 and in 2008 takes 25sec.

If you have a question, please post it in a new thread. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VinicioAizpurua SSC Veteran Points: 272 More actions · Answer 9

GilaMonter

I am trying to give an answer. Problem could be memory resources on the SQL 2008 R2. (I am assumming that tables has indexes, statistics updated, etc.) Check on the memory usage. There would be you answer on why it is taking so long.

Have a goog day. 😉