Optimize SQL Queries

Question

Post reply

Optimize SQL Queries

shantanusenin

Old Hand

Points: 308
More actions
September 24, 2010 at 3:00 am

#387856

Hello
I have two queries whose results are inter connected. The result for Query 1 is input to the query 2. The execution time for Query2 is more than 1 minute which is quite high as it returns only 40 rows of data.
Both the queries execute when the user logs into the application, and the user has to wait for a long time.
Query1:
SELECT Accounts from ITM_USER WHERE Short_ID = 'ssen5'
Output of Query1 : CSC,BHP,IMM,RIO
Query2:
Select distinct SourceDomainName from ITM_Alerts where SourceDomainName like '%CSC%' OR
SourceDomainName like '%BHP%' OR SourceDomainName like '%IMM%' OR
SourceDomainName like '%RIO%'
Output of Query2:
BHP
BHP Syslog
BHP WLAN
BHP-BCN ITM
BHP-BEC ITM
BHP-BHC
BHP-BHC ITM
BHP-BHC, BHP-BMX
BHP-BHG
BHP-BHG ITM
BHP-BHU
BHP-BHU ITM
BHP-BIO
BHP-BIO ITM
BHP-BIO NetTrace
BHP-BMC ITM
BHP-BMC ITM, BMC ITM
BHP-BMI ITM
BHP-BMX
BHP-BMX ITM
BHP-BSN ITM
BHP-COA
BHP-SSM
CSC
CSC eHealth
CSC ITM
CSC Syslog
CSC, CSC-INF
CSC-INF
CSC-NMSSERVERS
IMM
IMM ITM
IMM ITM, IMMI ITM
IMMI ITM
RIO
RIO eHealth
RIO ITM
RIO NetTrace
Is there any way to optimize the same thru some stored procedure or any other optimized code.
Please help
TIA
Senz

Viewing 15 posts - 1 through 15 (of 18 total)

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

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

The returned number of rows is irrelevant , what is more pertinent is the source number of rows.

Select distinct SourceDomainName from ITM_Alerts where SourceDomainName like '%CSC%' OR

SourceDomainName like '%BHP%' OR SourceDomainName like '%IMM%' OR

SourceDomainName like '%RIO%'

This will force a scan.

There is no magic to speeding up this , the only possible thing you could do is add an index on the the SourceDomainName column,so that sqlserver is scanning a smaller set of data (so an index scan not a table scan).

Are you sure this need to be like '%XXX%' from the data you provided 'XXX%' would suffice, that would be faster to process.

Clear Sky SQL
My Blog[/url]

shantanusenin Old Hand Points: 308 More actions · Answer 2

Are you sure this need to be like '%XXX%' from the data you provided 'XXX%' would suffice, that would be faster to process.

Both the above are taking the same amount of time

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

Do you have an index on that column ?

Are you reading 100% of the data in table ?

Clear Sky SQL
My Blog[/url]

shantanusenin Old Hand Points: 308 More actions · Answer 4

There is no indexing on the table and yes i am reading 100%

Can you provide me the steps of indexing in SQL Server 2005

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Try this link

http://odetocode.com/Articles/70.aspx

Clear Sky SQL
My Blog[/url]

shantanusenin Old Hand Points: 308 More actions · Answer 6

please help me with the error

CREATE INDEX IX_SDN ON ITM_Alerts(SourceDomainName)

Msg 1919, Level 16, State 1, Line 1

Column 'SourceDomainName' in table 'ITM_Alerts' is of a type that is invalid for use as a key column in an index.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Homework ???? Do i get partial credit ?

Looks like someone else has exactly the same issue

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26433433.html

Clear Sky SQL
My Blog[/url]

shantanusenin Old Hand Points: 308 More actions · Answer 8

you caught me!!!!!!!!!!!!

but it didnt work..................................................... 😉

i am getting error

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Well , since i dont know what is expected of you (and the point your instructor is trying to make here) im ducking out of this.

You need to go back to him /her.

Clear Sky SQL
My Blog[/url]

shantanusenin Old Hand Points: 308 More actions · Answer 10

i simply asked why the below error came....... was it so tough ....

CREATE INDEX IX_SDN ON ITM_Alerts(SourceDomainName)

Msg 1919, Level 16, State 1, Line 1

Column 'SourceDomainName' in table 'ITM_Alerts' is of a type that is invalid for use as a key column in an index.

:-D:-P:w00t:

shantanusenin Old Hand Points: 308 More actions · Answer 11

i simply asked the reason of the below error

CREATE INDEX IX_SDN ON ITM_Alerts(SourceDomainName)

Msg 1919, Level 16, State 1, Line 1

Column 'SourceDomainName' in table 'ITM_Alerts' is of a type that is invalid for use as a key column in an index.

is it so tough :hehe:

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

shantanusenin (9/24/2010)
i simply asked the reason of the below error

The error message is exceedingly clear in this case

Msg 1919, Level 16, State 1, Line 1

Column 'SourceDomainName' in table 'ITM_Alerts' is of a type that is invalid for use as a key column in an index.

For the types that are valid as index keys, consult Books Online (the SQL help file)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 13

Not tough at all , i know exactly WHY that error has occurred.

BUT, i dont know why your instructor has forced that error to occurred.

I do not know what is expected of you now.

This could be an exercise in database redesign as far as i know.

Thats my last word,

Feel free to take up the SSC money back guarantee if not 100% satisfied 🙂

Clear Sky SQL
My Blog[/url]

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 14

OK, I'll bite.

run

sp_help tablename

Look at the datatype of the attribute in question then reference BOL to see why.

Hope this helps,
Rich

[p]
[/p]