Optimize Query - 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 2000 Forums

Transact-SQL (2000)

Optimize Query

Author

Topic

bobbabuoy
Starting Member

33 Posts

Posted - 2004-02-26 : 06:03:05

Can someone look at this sql statement and tell me if it can be sped up? Also I have to add to it by joining it with another table. How do I do that? Just by nesting another join?

Thanks!


	Set rs=Server.CreateObject("ADODB.Recordset")
	sql = "SELECT td.TeamID, td.TeamName, rt.PartID, rt.Effort, rt.UnitMeas, pd.MinMilesConv "
	sql = sql & "FROM TeamData td INNER JOIN PartData pd ON td.TeamID = pd.TeamID "
	sql = sql & "JOIN RunTrng rt ON pd.PartID = rt.PartID "
	sql = sql & "WHERE rt.TrngDate >= '" & Session("beg_date") & "' AND rt.TrngDate < '" & Session("end_date") 
	sql = sql & "' AND pd.Archive = 'N' AND pd.Gender = '" & sGender & "' AND pd.Grade >= " & iMinGrade
	sql = sql & " AND pd.Grade <= " & iMaxGrade & " ORDER BY td.TeamID"
	rs.Open sql, conn, 1, 2

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-26 : 06:14:42

Indices on the columns included in the WHERE statement will assist performance.
Other than that, explicitly specifying the 2nd JOIN will bring clarity to the code.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-26 : 17:59:29

I'm just curious. Is there a reason you're not writing a stored procedure and capturing the recordset from that? It would be faster and more efficient.

You can join as many tables to this as you want. You will want to choose whether you want outer or inner joins based on how the tables are related. Just add more Joins like Andrew said.

Another way to speed this up might be indexes. It's hard to say though without the DDL.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources