How may I speed up this query?

[ranjithvenkatesh]

Database: SQL Server 2000

OS: Windows XP Professional

Tables:
PROJECT: PROJECT_ID - primary key

SCHEDULE: PROJECT_ID - foreign key
STARTDATE - string (yyyymmdd)

Aim: To get the Start Date string from the SCHEDULE table given the PROJECT_ID

Checks: NULL, EXISTS and default value to be returned if not available

Problem: The query takes an average of 700 milliseconds. How do I optimize the code below?

Stored Procedure:
CREATE PROCEDURE StartDate (@key nvarchar(50)) AS
if exists (select SCHEDULE.STARTDATE as StartDate from SCHEDULE, PROJECT
where PROJECT.PROJECT_ID=@key and
SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID
)

select isnull(SCHEDULE.STARTDATE,20050101) as StartDate from SCHEDULE, PROJECT
where PROJECT.PROJECT_ID=@key and
SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID

else

select 20050101

GO

 

[HRoarke]

If you're trying to return a default value, e.g. 20050101, when the ProjectID doesn't exist, I'd skip the existance test and use the ISNULL function to control what you're returning (as you do in the second select).

If you feel that you must check for existance, try the following:

if exists (select 1 
           from SCHEDULE s
           where s.PROJECT_ID=@key)
BEGIN
     ... regular retrieval statement ...
END
ELSE
BEGIN
     Select 20050101 as StartDate
END

Hope this is helpful.



"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[2ni]

try to replace this part :
from SCHEDULE, PROJECT
where PROJECT.PROJECT_ID=@key and
SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID

by

from SCHEDULE inner join PROJECT
on PROJECT.PROJECT_ID=@key and
SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID

 

[HRoarke]

If you're trying to return a default value, e.g. 20050101, when the ProjectID doesn't exist, I'd skip the existance test and use the ISNULL function to control what you're returning (as you do in the second select).

If you feel that you must check for existance, try the following:

if exists (select 1 
           from SCHEDULE s
           where s.PROJECT_ID=@key)
BEGIN
     ... regular retrieval statement ...
END
ELSE
BEGIN
     Select 20050101 as StartDate
END

Hope this is helpful.

"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[HRoarke]

Sorry for the multiple posts - I was receiving a DB timeout error from the site, which indicated that the Submit did not complete. Clearly, it completed.... my apologies.

 

[checkai]

2 options from me...

CREATE PROCEDURE StartDate (@key nvarchar(50)) AS
if exists (select SCHEDULE.STARTDATE as StartDate from SCHEDULE, PROJECT
where PROJECT.PROJECT_ID=@key and
SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID
)

select isnull(SCHEDULE.STARTDATE,20050101) as StartDate 
from SCHEDULE
	LEFT JOIN  PROJECT on SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID
where PROJECT.PROJECT_ID=@key

else

select 20050101

or

CREATE PROCEDURE StartDate (@key nvarchar(50)) AS

declare @date as varchar(10);
set @date = isnull((select isnull(SCHEDULE.STARTDATE,20050101) as StartDate 
from SCHEDULE
	LEFT JOIN  PROJECT on SCHEDULE.PROJECT_ID=PROJECT.PROJECT_ID
where PROJECT.PROJECT_ID=@key),'20050101')

select @date as returnValue

 

[ranjithvenkatesh]

thank you for the responses.

Let me try them and get back to the forum.

Ranjith

 

[ranjithvenkatesh]

I tried all of the above suggested responses.

However the execution time remains the same.

Is there some more information about the table or the database which I need to tell the forum?

Ranjith.

 

[SQLSister]

What indexes do you have on the table? How many records are in the two tables?

Wait, you are doing nothing withthe Project table except filtering on the id field which is also a field inteh schedule table. So why are you joining at all?

try:

select isnull(SCHEDULE.STARTDATE,20050101) as StartDate from SCHEDULE
where SCHEDULE.PROJECT_ID = @key

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[maswien]

I agree with SQL Sister, you need to know if there are any project_id exist in schedule but not in project. Then

select isnull(SCHEDULE.STARTDATE,20050101) as StartDate
from SCHEDULE
where SCHEDULE.PROJECT_ID = @key

will return different answer than your option 2, otherwise they should be the same.

 

[maswien]

If there are project_id exist in schedule but not in project, then those are orphan records, which should be fixed. We assume that your database doesn't have design flaw and the tables are not corrupt, so I think SQL Sister's solution is the best.

 

[SQLSister]

Even if there are orphan records, it would be irrelvant as we are specifying the particular Project_ID we want. The join would not catch any other records anyway. Just make sure there is an index on project_id (which there is if it is a primary key as I suspect it is.)

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ranjithvenkatesh]

I now use only one table to do the querying.
Is that an issue?

There are no indexes for the PROJECT table.

This database was imported from ACCESS.

We thought we would have a time advantage querying from a SQL Server database.

Requirement:
Query 3000 records from a database.

Ways(ACCESS or SQL Server Desktop Edition):
1. SQL queries called from a C# Console Application.
2. Stored procedures called from a C# Console Application.

Ranjith.

 

[vongrunt]

Imported how? With upsizing wizard?

 

[SQLSister]

You can get time advantages using SQL Server but you must do things differently. For one you need indexes to get any time advantages. Proper indexing is critical to performacne in SQL Server. Also in Access, people often create recordsets and then loop through them to do things. This is a bad practice in SQL Server and should be avoided if you want true speed.

You also need primary keys for data integrity reasons. Every record should have a way to uniquely identify it and that method should be enforced automatically by the database. The Primary key does that.

Another way to improve speed in queries like this is to not allow nulls in the field if you are always going to replace it with a different value when you query it. Set a default value and change the field to disallow nulls if that is the case. Without having to convert the value every time you query, you will save time. If this conversion is just for the purpses of this one query, that is not a good option, of course, but if it will alawys apply that you don't want to see null vales, then fix the data so it doesn't have them and fix the process, so you won;t get tehm again inthe future.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ranjithvenkatesh]

Imported using the Data Transformation Services Import/Export Wizard.

 

[vongrunt]

As far as I know, Access-to-SQL2k DTS doesn't copy indexes, primary keys and other stuff... Upsizing wizard does it much better.

Also: get rid of nvarchar data type unless you really need it (international apps, Unicode/XML storage etc).