Improve Query 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

General SQL Server Forums

New to SQL Server Programming

Improve Query Time

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-11 : 13:03:16

I've got a couple of queries that take a long time to execute on our SQL 2000 Server.

We are using Visual Studio 2005 Professional to make the calls, and each one takes about 60 seconds.

By itself, 60 seconds isn't much; however, we are running this report weekly for almost every employee out on the production floor. The entire report can take hours to run.

So, I'm looking for someone who can say, "Hey, your code can execute faster if you do this instead!"

Both queries work now, and they are as follows:

string strSqlDecay = "SELECT [OP_ID], [Serial_Number], [Test_Result], [Date_Time] FROM Test_Results " +
"WHERE ([OP_ID] Like '{0}') AND ([System_ID] Like '%{1}%') AND ([Test_Result] Not Like '%Abort%') " +
"AND ([Date_Time] BETWEEN '{2}' AND (COALESCE('{3}', [Date_Time])) ) " +
"ORDER BY {4}";
string strSqlChamber = "SELECT DC.[OP_ID], CB.[System_ID], CB.[Serial_Number], CB.[Test_Result], CB.[Date_Time] FROM Test_Results CB" +
" INNER JOIN (SELECT [OP_ID], [System_ID], [Serial_Number] FROM Test_Results WHERE ([System_ID] Like '%Decay%')) DC" +
" ON (CB.[Serial_Number]=DC.[Serial_Number]) " +
"WHERE (DC.[OP_ID] Like '{0}') AND (CB.[System_ID] Like '%Chamber%') " +
"AND (CB.[Date_Time] BETWEEN '{1}' AND (COALESCE('{2}', CB.[Date_Time])))";

Avoid Sears Home Improvement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 13:47:03

Show us the DDL for the tables plus the indexes.

This right here though is a red flag: CB.[System_ID] Like '%Chamber%'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-11 : 14:01:52

Hi Tara,

Thanks for the response.

I'm a software developer, not an SQL guy: What is a DDL?

The tables do not have indexes - anywhere! It was a very bad design, but it has been in use for over 5 years, and there are gigabites of data on it and several machines and PCs running it. It likely will not be changed for some time.

CB.[System_ID] Like '%Chamber%' is because there are several System_IDs like this:
A_Chamber_1 to A_Chamber_15
and
AA_Chamber_1 to AA_Chamber_10
and
VT_Chamber_1 to VT_Chamber_12

Since most of the "Chamber" machines come from vendors, their System_IDs are assigned by them and get changed as they update their software. Whenever they write records to our non-indexed database, they include their System_ID so that we can locate problem spots when (not if) they occur.

If you know of a more efficient way to write that, I am listening!

Avoid Sears Home Improvement

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 14:11:45

Your lack of indexes is the reason for your issue. There is no way to fix a performance issue unless you index the tables. Rewriting the query will not fix it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-11 : 14:13:22

That and turn your calls into stored procs :p

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources