How may I optimze 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

 

[PHV]

Due to referential integrity you don't need to join the PROJECT table in your queries:
CREATE PROCEDURE StartDate (@key nvarchar(50)) AS
if exists (select SCHEDULE.STARTDATE as StartDate from SCHEDULE
where SCHEDULE.PROJECT_ID=@key
)
select isnull(SCHEDULE.STARTDATE,20050101) as StartDate from SCHEDULE
where SCHEDULE.PROJECT_ID=@key
else
select 20050101
GO


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ranjithvenkatesh]

Hi PHV,

Thanks for the quick reply.

I have changed the code to use one table.

However the stored procedure execution time remains the same.

Is there any other way I may improve the time?

Ranjith.

 

[PHV]

And this ?
CREATE PROCEDURE StartDate (@key nvarchar(50)) AS
select isnull((select STARTDATE from SCHEDULE
where SCHEDULE.PROJECT_ID=@key), 20050101)
GO

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ranjithvenkatesh]

Hi PHV,

I tried the above stored procedure. The execution time remains the same.

Is it possible to handle errors instead of checking for errors in stored procedures?
If yes, how?

Ranjith

 

[fredericofonseca]

How are you calling the SP? and how are you defining your code please?

As the last SP sugested by PHV did not improve the speed, this seems to be related to external influences, so the above code is required. Some SQL Server parameters may also need to be adjusted.


And how are you measuring the speed?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ranjithvenkatesh]

Hi Frederico,

Here are the details you asked for:

Code to call Stored Procedure:
public string runStoredProcedure(string UniqueKeyValue, string RequiredClassPropertyName)
{
SqlCommand getClassProperty = new SqlCommand(RequiredClassPropertyName, m_Connection);
getClassProperty.CommandType = CommandType.StoredProcedure;

SqlParameter oSP = new SqlParameter("@key", SqlDbType.NChar,50);
oSP.Value = UniqueKeyValue;
getClassProperty.Parameters.Add(oSP);

SqlDataReader Reader = getClassProperty.ExecuteReader();

Reader.Read();

object RequiredClassPropertyValue = new object();

if (Reader.HasRows)
RequiredClassPropertyValue = Reader.GetValue(0);

Reader.Close();

return RequiredClassPropertyValue.ToString();


}

Measuring Speed of Stored Procedure Execution:
I use the SQL Profiler to see the execution time.

Ranjith

 

[SQLSister]

Do you get the same time issue when you call the query from query analyzer?

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

 

[ranjithvenkatesh]

Hi SQLSister,

When I call the query from the Query Analyzer the duration is zero(which means it is lesser than a millisecond)

Ranjith

 

[ranjithvenkatesh]

I had seen the MS SQL forum only later and I had posted there too.

I will continue there to find a solution to this issue.

The thread id is thread183-978862

Thank you for the responses.

Ranjith.

 

[fredericofonseca]

Ranjith

I am not going to add much, as I don't know how you are using the code mentioned.

But depending on how you use it you may be better off by only doing the following

SqlParameter oSP = new SqlParameter("@key", SqlDbType.NChar,50);
oSP.Value = UniqueKeyValue;
getClassProperty.Parameters.Add(oSP);
once for the life of the object

E.g. on VB I would do like this
if newflg then
newflg = false
SqlParameter oSP = new SqlParameter("@key", SqlDbType.NChar,50);
oSP.Value = UniqueKeyValue;
getClassProperty.Parameters.Add(oSP);
end if

This because once you have declared the parameters of a SP, as long as you keep the object "active" and reuse it you don't need to redeclare them again (unless you don't declare them all and you use different ones on diff parts of your program).

This is C# as per your other thread and I don't know anything about it so the above may not even apply.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com